Microsoft Access VBA: Block Subform Records When a Parent Field Is Empty
Question details
Prevent a new payment record from being entered in an Access subform until Members Present has a value on the parent form.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A user enters payment records in the frm_Payments subform of a parent form.
- Observed behavior
- The user wants Access to check the parent form's Members Present control before allowing a new subform record.
Confirm that the parent form has a control named Members Present and that frm_Payments is used as its subform. Enable database content if Access displays a security warning that prevents VBA from running.
Validate the parent control in the subform's Before Insert event
Use this VBA event to cancel a new payment record when Members Present is null or empty.
The Before Insert event runs when the user begins entering a new record. Me.Parent refers to the parent form, and Cancel = True cancels the insertion.
Use the parent form control's name in the code, which may differ from the underlying table field's name.
In the Access Navigation Pane, right-click frm_Payments and select Design View.
Open the Property Sheet, select Form from the selection list, and choose the Event tab. Set Before Insert to [Event Procedure], then click the builder button (...).
In the VBA editor, add the following procedure, or place its body inside the existing Form_BeforeInsert procedure: Private Sub Form_BeforeInsert(Cancel As Integer) With Me.Parent![Members Present] If Len(.Value & vbNullString) = 0 Then .BackColor = vbRed MsgBox "Add Members present before adding records", vbCritical + vbOKOnly, "Attention!" .BackColor = vbWhite Cancel = True End If End With End Sub
Save the form, open the parent form in Form View, and try to start a payment record with Members Present empty. Then enter a value and try again.
Use WPS Office for documents and spreadsheets alongside Access
WPS Office is a free, lightweight alternative for everyday document, spreadsheet, and presentation work. Its familiar interface and Microsoft Office format compatibility make it easy to move existing files. It does not replace Access or run Access forms and VBA.

Frequently Asked Questions
Where should I put the Before Insert code?
Open frm_Payments in Design View, select the form in the Property Sheet, and use Event > Before Insert > [Event Procedure]. The code belongs in the subform's form module, not the parent form's module.
What if the control has a different name from the field?
In the parent form's Design View, select the Members Present control and check its Name property on the Property Sheet. Replace Members Present in Me.Parent![Members Present] with that control name.
Does referential integrity check whether Members Present is filled in?
No. A relationship with referential integrity can require a matching parent record, but it does not check whether a separate parent form control contains a value. Use the Before Insert validation for that check.
Will the code reject a value containing only spaces?
No. The example checks for null or an empty string. To reject spaces as well, change the condition to If Len(Trim(.Value & vbNullString)) = 0 Then.




