VBA & Macro Problems

How to Validate Multiple Required Access Form Fields with a Single VBA Rule

Khadija Khan Oct 14, 2026 869 views

Question details

The user needs to validate three specific MS Access form fields simultaneously when a combo box is set to 'Completed'.

How to Validate Multiple Required Access Form Fields with a Single VBA Rule

Product: Microsoft Access
Device & OS: not provided
Scenario: Setting up conditional data entry validation where certain fields become mandatory only when a specific status is selected in a combo box.
Observed behavior: The goal is to prevent the record from being saved and alert the user with a single VBA procedure if any of the required fields are empty during a 'Completed' transaction.

Before you start

Ensure you have your Access form open in Design View and take note of the exact control names for your combo box and the three required fields before writing the VBA code.

Solution 1Recommended

Use the BeforeUpdate Event for Conditional Validation

The most effective method to validate multiple fields based on another field's value is by using the form's BeforeUpdate event to intercept and cancel the save operation.

Relying on the standard ValidationRule property can be limiting when dealing with complex conditional logic across multiple fields. The BeforeUpdate event triggers immediately before a record is saved to the database. By utilizing this event, you can evaluate the combo box value and check all required fields simultaneously in one centralized VBA block.

Open Form Properties

Open your form in Design View. Make sure the form itself is selected (a black square should appear in the top-left intersection of the rulers), then open the Property Sheet.

Locate the BeforeUpdate Event

In the Property Sheet, navigate to the 'Event' tab. Find the 'Before Update' property, select '[Event Procedure]' from the dropdown, and click the ellipsis (...) button to open the VBA editor.

Insert the VBA Code

Enter the following conditional logic between the 'Private Sub Form_BeforeUpdate(Cancel As Integer)' and 'End Sub' lines: If Me.YourComboBox = "Completed" Then If IsNull(Me.Field1) Or IsNull(Me.Field2) Or IsNull(Me.Field3) Then MsgBox "Fields 1 to 3 must be completed for a completed transaction", vbExclamation, "Invalid Operation" Cancel = True End If End If

Save and Test

Save your VBA code and return to Access. Switch the form to Form View, set the combo box to 'Completed', leave the required fields blank, and attempt to save the record to verify the prompt appears.

Use the BeforeUpdate Event for Conditional Validation

Match Your Control Names: Make sure to replace 'YourComboBox', 'Field1', 'Field2', and 'Field3' in the VBA snippet with the exact names of the controls on your specific form.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Alternative?

While Microsoft Access handles complex relational databases, you can manage most everyday data processing, documentation, and presentation tasks using WPS Office. It provides a highly compatible, free alternative to Microsoft Office that is fast, lightweight, and requires no learning curve.

1. Download WPS Office: Visit the official WPS Office website and download the free installer for your operating system.
2. Install and Launch: Follow the simple on-screen installation prompts, then open WPS Spreadsheet for your data management needs.
3. Import Your Data: Open your existing .xlsx or .csv files directly in WPS Spreadsheet with perfect format retention.

Fully compatible with Microsoft Excel, Word, and PowerPoint formats.Lightweight installation that runs smoothly on older or budget devices.Familiar user interface allowing for seamless migration from MS Office.Powerful spreadsheet data validation features for managing required fields without VBA.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use the ValidationRule property for this scenario?

The ValidationRule property at the table or field level is best suited for simple, single-field constraints (like checking if a number is greater than zero). Conditional validation that depends on the value of another field—such as a combo box status—requires the flexibility of VBA logic.

How do I highlight the exact field that is missing data?

In your VBA BeforeUpdate event, you can evaluate each field individually rather than combining them with 'Or'. If a specific field is null, you can use the SetFocus method (e.g., Me.Field1.SetFocus) immediately after displaying the message box to direct the user's cursor to the missing field.

Can I achieve this without writing VBA code?

Yes, you can use Access Data Macros instead of VBA. You can create a 'Before Change' macro at the table level using an 'If' block to check if the status is 'Completed' and the other fields are empty, then use the 'RaiseError' action to prevent the save.

Excel & Data Problems