Subform records are sometimes not relating to the main form

[David Kistner]

Sometimes, when our users enter data into the main form and the
related subforms, the subform record is not relating to the main form.
All of the tables are receiving the data, except for the foreign key
of the subform table that relates to the primary key of the main form
table. This entry is missing. I can manually go to the subform table
record, and insert the foreign key entry, and at that point everything
works fine. But what could be causing this intermittent failure to
occur? Most of the time, users fill out the form and the related
subforms and we have no problem (the foreign key is automatically
included/entered into the subform's table).

Thanks in advance for your help.

- David Kistner

 

[Allen Browne]

If the main form is at a new record, and the user enters a record in the
subform, there is no value for the foreign key to pick up from the main
record, and you end up with the orphaned record.

Please note that this happens even if you have a relationship with
referential integrity enforced.

To prevent this, set the foreign key's Required property to Yes. Then the
subform record won't save after the user has filled it in. To give them a
message before they fill in the subform, cancel its BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Fill in the main form first."
End If
End Sub