Prevent a Microsoft Access Duplicate Query Form from Opening When Empty
Question details
The user wants to display duplicate-query results only when matching records exist.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A duplicate-record query is used to find repeated records in an Access database.
- Observed behavior
- The desired result is a form that opens when duplicates exist and displays a message instead when the query returns no records.
Make sure the duplicate-record query is saved and returns the expected records. If Access blocks VBA code, enable content only if you trust the database.
Use a Form Open Event to Check for Duplicate Records
Use a form based on the duplicate query to cancel its opening when the query returns no records.
This approach controls the form, not the query itself. Open the form instead of opening the query directly.
In Access, select the saved duplicate query in the Navigation Pane, then choose Create > Form or Form Wizard to create a form based on it. Save the form.
Right-click the form in the Navigation Pane and choose Design View. Open the Property Sheet, select Form, and on the Event tab set On Open to Event Procedure. Click the adjacent builder button to open the VBA editor.
In the Form_Open procedure, enter: Cancel = (Me.RecordsetClone.RecordCount = 0), followed by If Cancel Then MsgBox "No duplicates found.", vbExclamation. Save the code and the form.
Open the form from the Navigation Pane. It should display results when duplicates exist and show the message without opening when the query returns no records.

Review Exported Access Results in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility. Export Access query results to Excel to review them in WPS Spreadsheets; WPS does not run Access queries or forms.

Frequently Asked Questions
Why does the duplicate query still open when it has no results?
The VBA check runs in the form's On Open event, not in the query. Open the form from the Navigation Pane instead of opening the query directly.
Where do I add the Form_Open code in Access?
Open the form in Design View, select Form in the Property Sheet, and choose Event > On Open > Event Procedure. Click the builder button to edit the VBA procedure.
Can I change the message shown when no duplicates are found?
Yes. In the Form_Open procedure, replace the text inside MsgBox "No duplicates found." with your preferred message.
What happens if VBA opens the form and its opening is canceled?
A DoCmd.OpenForm call can raise error 2501 when the form's Open event is canceled. Add appropriate error handling to the procedure that calls DoCmd.OpenForm.




