By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,779 Members | 1,291 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,779 IT Pros & Developers. It's quick & easy.

How to populate foreign key

P: 2
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.
May 14 '10 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
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!

May 16 '10 #2

P: 2
I tried the subform again and it works. I think I didn't do it right the first time.

Many thanks!!
May 17 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.