You have a table with a primary key, and related table with a foreign key. You have created a relationship between these two tables, with referential integrity. Now you create a main form with a subform for these two tables. Some of the records you enter in the subform seem to disappear never to be seen again. Where have they gone?
Referential integrity does not block Nulls in the foreign key. Normally Access fills in the foreign key in a subform to match the primary key in the main form. But if the main form is at a new record, Access merrily accepts the entry in the subform with a Null foreign key. These records never show up again in the subform, since they do not relate to any record in the main form.
You must take specific action if you want to prevent this occurring.
Do either (or both) of the following:
Set the Required property of the Foreign key to True in Table Design.
Use the subform's BeforeInsert event procedure to cancel (or question) the insertion:
Dim frm as Form
Set frm = Me.Parent
If IsNull(frm.[ID]) Then
Cancel = True
MsgBox "Create a record in the main form first.", vbExclamation, "Required data."
frm.[ID].SetFocus
End If