When a new parent record is inserted, the table will auto-generate the primary key for that record. How can this be captured to be used as the foreign key when a child-record for this parent is inserted?

This could be like in the classic order/order-item scenario.

Some flavors of SQL have a feature do do this; I'm new to Access and haven't seen any reference to such a capability. :o

=Alan R.

The EASIEST way is to have a subform on the main form and if you have the links set properly it will populate the foreign key automatically.

Bob -- thanks for the quick reply. I'm not quite used to all the 'auto-magic' of Access!

Is this how it would work? (kind of):

I have a order "header" form (Order num, date, etc) with a line-items sub-form, and set the parent-child links, (which would map the parent primary key to the child foreign key), as soon as the order record is saved (tabbing into the sub-form?), Access would generate (and 'know') the primary key of the order, and as each line in the sub form was saved (tabbing to the next line), it would have it's foreign key set? :confused:

What is a little disconcerting at this stage, is knowing when a WRITE or SAVE occurs.


The EASIEST way is to have a subform on the main form and if you have the links set properly it will populate the foreign key automatically.

Yes, You would have your line items subform on the main orders form and the Master/Child links would be set to OrderNum. With that, the subform will insert the order number when you start to add any line item numbers.

IMPORTANT - Do not use Access Reserved words for your field names or object names. So, do not use DATE as a field name as it is an Access Reserved Word. Another thing is to avoid the use of spaces and special characters (other than underscores) in field or object names. It will make your life way easier.

Also, as far as when a save occurs -

On a single form - a save automatically occurs when you navigate to another record or close the form (if the required fields are filled out).

On a main form/subform, the main form is saved, if you leave it to go to the subform, navigate to another record, or close the form after filling in any required fields.

The subform is saved when you move back to the parent form, navigate to a new subform or main form record, or close the form provided all required fields are filled out.

For all - if a form is not "dirty" (has had changes made to it, or in the case of a new record, any fields filled in) it will not save because there is nothing to save.

I hope that helps.

Thanks again Bob --- I think I'm starting to 'get' it!

I've been working/studying with Access for about 2 weeks or so now (20 years with Omnis and Omnis Studio), and little by little the pieces are coming together.

I'll def save a link to this forum and I'm sure I'll be back. :)

=Alan R.

Yes, You would have your line items subform on the main orders form and the Master/Child links would be set to OrderNum. With that, the subform will insert the order number when you start to add any line item numbers.

[big snip]

I hope that helps.

A "visual" indicator of whether your record is dirty or saved is that the record pointer has a pencil on it when it is dirty, which goes away when the record is saved.