Others

Microsoft Access VBA: Block Subform Records When a Parent Field Is Empty

Natalie TaylorNatalie Taylor Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the subform in Design View

In the Access Navigation Pane, right-click frm_Payments and select Design View.

2
Open the event procedure

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 (...).

3
Add the validation code

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

4
Save and test

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.

Background color: The example resets the control background to white after the message closes. Change vbWhite if the control normally uses a different color.
Free Microsoft Office alternative

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.

Organize payment data in spreadsheets alongside your Access databaseOpen and edit common Microsoft Office formats, including DOCX, XLSX, and PPTXWork in a familiar interface when moving from Microsoft OfficeUse a free, lightweight office suite for everyday files
microsoft office alternative - wps office

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.