Open an Access Form with Multiple Criteria Using VBA
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.

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

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.

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.




