Others

Prevent a Microsoft Access Duplicate Query Form from Opening When Empty

Rana GarciaRana Garcia Nov 22, 2026 868 views

Question details

The user wants to display duplicate-query results only when matching records exist.

Prevent an Access Duplicate Query Form from Opening When No Duplicates Are Found
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a results form

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.

2
Open the event procedure

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.

3
Add the check

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.

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

Use a Form Open Event to Check for Duplicate Records
Opening the form with VBA: If another VBA procedure uses DoCmd.OpenForm, canceling the form's Open event can raise error 2501. Handle that error in the calling procedure if appropriate.
Free Microsoft Office alternative

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.

Review duplicate-query results exported from Access as a spreadsheetOpen and edit Microsoft Excel XLSX files for easier migrationWork with a familiar spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

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.