Auto populate a foreign field in a subform

[ron b]

haven't worked with Access forms in a long time, and I am in the process of
creating a database for a friend. I have a main form, with two subforms.
Both subforms are continuous forms. The primary key of the main form
automatically populates the foreign key of the first subform, and that is
great, however the primary key in the first subform does not automatically
populate the foreign key of the second subform. For example:

tblone, main form:
tblone field (primary key) data 0001

tbltwo, first subform:
tbloneID field (foreign key) data 0001
tbltwo field (primary key) data AAB

tblthree, second subform:
tbltwoID field (foreign key) data is null, until manually typed in.


Is there a way to make the foreign key of the second subform automatically
populate with the data from the primary key of the first subform?

 

[Larry Daugherty]

In the relationships window add table three and establish the relationship
between table two PK and table FK exactly as you did for the first two
tables.

HTH

 

[ron b]

All three tables are linked on a one to many relationship, main to first
subform and first subform to second subform. The FK field in the second
subform still doesn't populate automatically. When I originally set up the
form it did, but I had to change the primary key in the first subform. After
the change, the new FK field doesn't populate. I believe it must be a
setting on the form, but I can't figure out what it is.

 

[Larry Daugherty]

When you look at the properties for the lowest subform control the link
Master field and Link Child field should be pointing to a field with the
same name or the PK of the upper table and the FK in the lower table.

HTH

 

[ron b]

Hi Larry,
The link for the lowest subform is between the PK of the main form and the
FK of the lowest subform. It appears you have to choose from a list of fields
in each, the main form and the second subform. There is no option to choose
between the PK in the first subform and the FK of second subform. Now the PK
in the main form populates the FK in the second subform, which is okay, but
it does not solve the original problem. I also noticed that now when I add a
new record to the main form, I cannot add a new record to the first subform.
I don't know if the issues are related. Any more ideas? I hope so. Thanks.
Ron

 

[Rick Brandt]

Hi Larry,
The link for the lowest subform is between the PK of the main form
and the FK of the lowest subform. It appears you have to choose from
a list of fields in each, the main form and the second subform.
There is no option to choose between the PK in the first subform and
the FK of second subform. [snip]

This is true if you use the wizard to make the entries however you can manually
enter anything you want into those properties to get the behavior you want.

 

[ron b]

Thank you Rick, that did the trick.

Hi Larry,
The link for the lowest subform is between the PK of the main form
and the FK of the lowest subform. It appears you have to choose from
a list of fields in each, the main form and the second subform.
There is no option to choose between the PK in the first subform and
the FK of second subform. [snip]

This is true if you use the wizard to make the entries however you can manually
enter anything you want into those properties to get the behavior you want.