Require a record in a subform

[Guest]

I have a form with a subform; Each parent record (main form) should have at
least one child record, but can have many. Referential integrity is
established in the Relationships table. I want to check/require that a child
record exists in the subform before the main form can be closed. If one does
not exist, I want the form to remain open and to generate a msgbox to tell
the user they must add a child record before closing the form.

Don't know if this will matter or not, but users asked for a confirmation
dialog upon closing the form, so I currently have the following code in
beforeUpdate:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Data has changed."
strMsg = strMsg & " Save Changes?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
'do nothing
Else
DoCmd.RunCommand acCmdUndo
End If
End Sub

Any help would be appreciated. I Thought this would be very easy but have
not figured it out nor seen a posting about this

 

[Pat Hartman]

In a relational database the parent record must be saved before any child
records can be saved so there is no database rule that can enforce your
business rule. You also have to keep in mind that S--- happens and at some
point in time you will end up with parents without children (maybe there is
some support group for this affliction) and so you will have to deal with
the problem. That said, you could use the unload event of the main form to
test for this situation and give the user the opportunity to add the child
record or delete the parent record. The UnLoad event can be cancelled (it
has a cancel argument) if necessary to prevent the form from closing but you
can't be too draconian with your rules here because there is always
ctl-alt-dlet available if there is no other way out for them. You should
probably add a query that runs at startup that identifies any "orphan"
parents and lets the user delete them or fix them.