How to populate foreign key

[goodboy1975]

I have a form (Form A) based on Table A. The primary key of this table is the foreign key of Table B. Now what I was trying to do was when one row on Form A is populated and saved, I would like the primary key from Form A for that record to automatically populate the foreing Key column in Table B (i.e. write from a form to another table). Any help would be appreciated.
Thanks!

[Stewart Ross]

Hi goodboy1975

When there is a one-to-many relationship between tables - which there is when a primary key in one table is a foreign key in another - the simplest forms-based user dialogue you could provide is to use a subform within your main form to show the related information. Access will carry primary key values forward from the main form to the secondary keys in the related table for you automatically if you use a subform.

A subform is typically used to show records row-by-row in datasheet form. You need to design the subform first before you can include it into the main form. You can then open the main form in design mode and drag a subform onto it to create the master-detail structure required.

When you drag a subform onto the main form Access examines the relationships which are defined between the tables (or queries) concerned, identifies the most likely primary and secondary keys, and pre-populates the Master and Child field properties of the subform with the names of these fields if it can identify them. If they cannot be identified by Access you can set them yourself.

When you start populating a record in a subform which has its master-child properties correctly set, the values for the identified child field (or fields in a compound key) are automatically carried forward by Access from the values in the main form (representing the one-side of the relationship) to the subform (representing the many-side of the relationship).

It is normal design practice when using a subform not to show the foreign key field values to the user, as they simply repeat one or more values already present on the main form, although in testing you may wish to have them visible to make sure everything works as expected.

To sum up, the user interface feature provided for data entry where there is a master-detail relationship (1-M) is the use of a subform to record the detail records related to the master records shown in the main form. If proper relationships have already been set between the tables then Access can identify the most likely master-child field properties for you when you add the subform to the main form.

Welcome to Bytes!

-Stewart

[goodboy1975]

Stewart,
I tried the subform again and it works. I think I didn't do it right the first time.

Many thanks!!