one-to-one relationship problems

[Peter Hemmingsen]

Hi

I need to make an Access 2007 "Customer" database where a customer is either
a person or a married couple.

I've created a "Person" table with personal information such as name,
birthday, etc. The primary key in this table is an autogenerated number.
The "Customer" table have an autogenerated primary key value, some customer
information, and then ManID and WifeID fields reffering to the primary key
(autogenerated ID) in the "Person" table.

I've created a one-to-one relationship between ManID (and WifeID) from the
"Customer" table and the primary key (autogenerated ID) of the "Person"
table and checked the "Enforce Referential Integrity" and cascade
Update/delete.

Now when I (Using Access 2007) create a record in the customer table I can
press the + (left to the new created row in customer table) which will then
allow me to enter a record in the "Person" table. However I cant create a
row in the "Person" table. It seems like the auto generated ID for the
person record is not created at this stage and I get a "ID value can not be
null" error.

I know that I could change the design so that each record in "Person" have a
CustomerID field and if to persons was married they would simply have the
same CustomerID. But this could lead to errors where more than two person
would have the same customer ID. (The application does not support polygamy
)

Any suggestions to the design or how to correctly make the relationship
would be highly appreciated,- thanks

Peter

 

[tedmi]

I have a very similar data structure that I implemented as follows:
Address table with PK autonumber AddressID, mailing address, other household
info. This is the equivalent of your Customer table.

Person table with compound PK of (AddressID, PersonID), where AddressID is
FK to the Address table, PersonID has a validation rule >0 AND <3 (i.e. must
be 1 or 2, hence enforcing monogamy), First & Last names, other individual
info. There is one-to-many relationship from Address to Person, but the
validation rule limits it to a one-to-one or two.

 

[Peter Hemmingsen]

Hi TedMi,

Thanks for your reply. It looks like you are assigning values (1 and 2) to
PersonID manually - is that correct?

Peter

 

[tedmi]

The PK of the Person record is generated as follows: The AddressID comes
automatically from the parent form (record source based on Address Table) of
the Person sub-form. The PersonID defaults to 1 for a new person record, and
I manually change it to 2 for the second person at this address. I can't
forget to change it, because otherwise there's a unique key violation - two
1's at the same AddressID.

One problem I encountered: It is possible to have an address record with no
persons attached, which I want to prevent. I think I will write AutoOpen code
to check for any such records, present them, and ask for either deletion or
input of person data.

 

[Evi]

You could make PersonID a Required field in the Address table's Design View
Evi

 

[tedmi]

The PersonID field exists in the Person table, not the Address table. The
situation I want to avoid is an Address record without at least one dependent
Person record. Easy to do with transaction control, which Jet does not
provide.

 

[Evi]

The PersonID field exists in the Person table, not the Address table. The
situation I want to avoid is an Address record without at least one dependent
Person record. Easy to do with transaction control, which Jet does not
provide.

 

[Evi]

TedMi, please stop removing the entire message thread. I have to clean out
my files now and again and I'm having to hunt you up in Google to read what
the entire thread was about.

So you must have AddressID in the Person table then.

Change your main address form so that you can't add a new address to it.
(Allow
Entry in the Form's properties should be False).
Create a form, FrmPerson, based on TblPerson. It can have a combo based on a
query based on tblAddress so that the new person can be added to an existing
address, if wished.
On the form, have a button which will only become visible when a required
field (perhaps the surname field) is filled in (use the AfterUpdate event of
that field)

Create a form based on tblAddress.
Use its Properties (on the Other tab) to set Modal to yes.


The button on FrmPerson opens FrmAddress in Dialog mode with

DoCmd.OpenForm "FrmAddress", acNormal, , stLinkCriteria, , acDialog

In the Unload Event of FrmAddress, have the code
Forms!FrmPerson.Form.AddrID = Me.AddrID

This ensures that the new address in FrmAddress becomes the address for the
person you have just entered.
Evi

 

[tedmi]

I have solved the issue without changing any existing forms. On the Unload
event of the Address form, I check for the existence of a person record for
that address; if not, the unload gets canceled. (Person is entered on a
subform of the Address form).
--
TedMi

TedMi, please stop removing the entire message thread. I have to clean out
my files now and again and I'm having to hunt you up in Google to read what
the entire thread was about.

So you must have AddressID in the Person table then.

Change your main address form so that you can't add a new address to it.
(Allow
Entry in the Form's properties should be False).
Create a form, FrmPerson, based on TblPerson. It can have a combo based on a
query based on tblAddress so that the new person can be added to an existing
address, if wished.
On the form, have a button which will only become visible when a required
field (perhaps the surname field) is filled in (use the AfterUpdate event of
that field)

Create a form based on tblAddress.
Use its Properties (on the Other tab) to set Modal to yes.


The button on FrmPerson opens FrmAddress in Dialog mode with

DoCmd.OpenForm "FrmAddress", acNormal, , stLinkCriteria, , acDialog

In the Unload Event of FrmAddress, have the code
Forms!FrmPerson.Form.AddrID = Me.AddrID

This ensures that the new address in FrmAddress becomes the address for the
person you have just entered.
Evi

 

[Evi]

Cool idea, tedmi, cancelling the unload. Glad you got it working.
Evi

I have solved the issue without changing any existing forms. On the Unload
event of the Address form, I check for the existence of a person record for
that address; if not, the unload gets canceled. (Person is entered on a
subform of the Address form).