One-to-Many Relationship problem

[Roy Carlson]

I have a database with 2 tables. One is a table with a persons bio data
(name, children, primary address, etc), and the other table is used to
collect additional addresses and phone numbers for that person. I created a
one-to-many relationship between the two tables (bio table is one and alt
addresses table is many). I am using the AutoNumber field on the first table
and linking it to a field called "Link ID" on the second table (this is not
an auto number field).

In the table view of the first table, I am able to click the + for a row of
any record and see the fields for the second table.

I have two forms, one that asks for the bio data, and one that asks for the
alt address/phone data. I have a button on the bio form that opens the
"subform" for that record and asks for alt address/phone. However, something
isn't working. When I add the data, it doesn't link it to any record. The
LinkID field in the table is always 0, instead of the autonumber from the
first table. If I change the LinkID to the correct AutoNumber (so that it
corresponds to a record in the first table), it displays properly.

However, I want access to automatically put in the correct number - I
thought that is what a relationship is for. What am I doing wrong??

 

[Jeff Boyce]

Roy

?"... opens the 'subform'...?"

It sounds like the "subform" is not actually embedded within the main form,
but is just opened up in addition to the main form.

Take a look at main form/subform construction. This gives you a way to
still capture the data via a subform, but Access manages connecting the
subform data to its "parent" in the main table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hartman \(MVP\)]

True subforms are embedded on a main form. Setting the master/child link is
what tells Access how to populate the subform's foreign key. This is not
automatic. If the master/child link is unset or set improperly, the link
between the parent and child will not be made and you will end up creating
orphan records. When you open a stand-alone form, Access has no way of
knowing which record you need as the parent. In order to properly implement
RI on this relationship:
1. Remove the default for the foreign key. Access automatically assigns 0
as the default for numeric fields. Access has no way of knowing at the time
you are defining a table that you intend to use a particular field as a
foreign key. You don't want foreign keys to have any default (except in
unusual cases) so it is up to you to remove it.
2, Then define the FK field as required. That will prevent Access from
adding the record if the foreign key is not populated with a valid value.

To solve the foreign key problem when using popup forms,
1. pass the value you wish to use as the OpenArgs value in the OpenForm
method.
2. In the popup form's BeforeInsert event, place the OpenArg value into the
foreign key field. I choose this event because it doesn't fire until
someone actually types something into the form. This keeps you from
dirtying the form programatically which can cause confusion for the user.
An alternate event is the BeforeUpdate event of the form. It is the last
event fired before the record is actually saved.

Me.YourFKField = Me.OpenArgs

 

[Pat Hartman \(MVP\)]

I forgot to mention that you have not properly normalized your tables. A
person may have more than one child so children (or dependents) must be held
in a separate table. You also may have the issue of addresses associated
with the children as well as surnames. You didn't say what your application
is so I don't know how important these things are to you. You also have to
keep in mind that contact numbers are not necessarily associated with
addresses. Cell phones and pagers come to mind. So contact numbers are
generally kept in a separate table. If you want to associate phone numbers
with addresses, the relationship becomes many-to-many between addresses and
phone numbers (they are also many-to-many between people and phones although
that is also getting rarer with the proliferation of cell phones) since an
address may have many phones and a single number may ring at multiple
addresses (rare). You may not wish to allow for the second case of a phone
ringing at multiple addresses so in that case it would be acceptable to have
the AddressID as a FK in the phone table, restricting the phone to a single
address.