I have a fundamental misunderstanding about the difference between inserting a subform onto a main form and linking the subform with a command button; I hope someone can enlighten me. I notice that when entering data on a subform placed onto the main form, the foreign key value is automatically inserted when a new record is created; this is what I would expect to happen. If, however, I open that same subform via a command button and enter data into a new record, the foreign key value is not inserted; this would result in the creation of an orphan record. Why is this so?

I would appreciate any help.

Regards,

John

Subforms are not really meant to opened alone. Subforms are best suited to opened only within a parent form. That way, the foreign key can easily be provided when adding records. Without the parent, a subform has now of determining which foraign key is to be applied.

Whenever I use a subform on its own, I always set it to read-only mode. That way, empty foreign keys will not occur.

hope this helps

The foreign key autopopulation only works if you are using a subform control so Access "knows" how they are related. When you open a form as a pop-up, Access does not perceive it as a subform, even if it is dependent on the main form.

If you want to use pop up, you will need to feed the key value manually. Usually this is done by passing the current main form's key value as OpenArgs when opening the pop up, and editing its recordsource to be restricted to only records matching the main form's key. An alternative that doesn't require coding is to have a hidden textbox that's bound to the key in the pop up, and setting its Default Value property to =[Forms]![MainForm]![KeyValue] (adjusting for naming, of course. You can use Expression Builder to help with this).

HTH.

When a form is placed on another form using a subform control there
is a pair of properties of the subform control, link master field and
link child field, that defines what field(s) the two forms have in common.
That is the field(s) that is automatically entered into new records in
the recordsource of the form in the subform control.

When the child form is not in a subform control on the parent form
there is no similar built in device to link the two forms together. You
have to come up with a way to add the key fields(s) to the 'child' form's
record yourself.

Thanks for the explanations. I think I understand now.

Regards,

John

Glad we could help. Good luck with your project(s)!