Others

Open an Access Form with Multiple Criteria Using VBA

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Open a related Access form when records match an account and other optional criteria, or open an alternative form when no records match.

Open an Access Form with Multiple Criteria Using VBA
Product
Microsoft Access
Device & OS
not provided
Scenario
A control on an Access form should check for related records before deciding which form to open.
Observed behavior
The desired behavior is to show matching records in DocumentCenter_DocumentsRequired or offer DocumentCenter_SelectDocuments when no matches exist.
Before you start

Identify the table or query to search, the AccountID field type, and the names of both destination forms. Ensure the required form's record source contains AccountID and any other fields used in the filter.

Solution 1Recommended

Use DCount and OpenForm in a button event

Use this method to test for matching records and open the appropriate form, with an optional confirmation when no match exists.

1
Open the event procedure

Open the source form in Design View, select the button that should open the related form, and use Property Sheet > Event > On Click > [Event Procedure]. Click the ellipsis button to open the VBA editor.

2
Add the record check

Paste the following code between the existing Private Sub and End Sub lines. It assumes AccountID is numeric. Replace NameOfTable with the actual table or query name. Dim criteria As String If IsNull(Me!AccountID) Then MsgBox "Select an account first." Exit Sub End If criteria = "[AccountID]=" & Me!AccountID.Value If DCount("*", "NameOfTable", criteria) > 0 Then DoCmd.OpenForm FormName:="DocumentCenter_DocumentsRequired", WhereCondition:=criteria Else If MsgBox("No matching records were found. Select documents instead?", vbYesNo + vbQuestion) = vbYes Then DoCmd.OpenForm FormName:="DocumentCenter_SelectDocuments" End If End If

3
Add other criteria if needed

To require another condition, extend the criteria assignment before DCount. For example, use criteria = "[AccountID]=" & Me!AccountID.Value & " AND [Status]='Open'" if Status is a text field. Replace Status and Open with the actual field and value; the same criteria are used to filter the opened form.

4
Save and test both paths

Save the VBA code and form, switch to Form View, and click the button for an account with matching records. Then test an account without matches and confirm that the alternative form opens when you choose Yes.

Use DCount and OpenForm in a button event
If AccountID is text: Replace the numeric criteria assignment with criteria = "[AccountID]='" & Replace(CStr(Me!AccountID.Value), "'", "''") & "'". This encloses the value in quotes and escapes apostrophes.
Free Microsoft Office alternative

Review exported Access data in WPS Office

WPS Office is a free, lightweight alternative for everyday document and spreadsheet work. Its familiar interface and Microsoft Office format support make it easy to move exported data into a spreadsheet, although it does not replace Access forms or run this Access VBA solution.

Review and filter exported Access data in WPS SpreadsheetOpen and edit Microsoft Excel XLSX files, as well as Word and PowerPoint formatsUse a familiar interface for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

What should I use instead of NameOfTable?

In the DCount call, replace NameOfTable with the name of the table or saved query containing the records you want to check. It must contain the fields referenced by your criteria.

How do I check more than one condition?

Join conditions in the criteria string with AND or OR, such as "[AccountID]=" & Me!AccountID.Value & " AND [Status]='Open'". Make sure both the DCount source and the destination form's record source contain those fields.

How can I open the alternative form without asking for confirmation?

In the Else branch, remove the MsgBox If block and place DoCmd.OpenForm FormName:="DocumentCenter_SelectDocuments" directly after Else.

Why does the example check for a blank AccountID?

A Null AccountID cannot produce a useful account filter. The IsNull check displays a message and exits the button's event procedure before DCount runs.