Others

How to Open an Access Report for a Specific Record ID

Camila Milosovich Sep 29, 2026 869 views

Question details

The user wants to add a button to a Microsoft Access form that prompts for a specific primary-key ID and opens a report filtered only to that record, avoiding navigation away from the main form.

How to Open an Access Report for a Specific Record ID

Product: Microsoft Access
Device & OS: not provided
Scenario: Developing or using a Microsoft Access database form and needing a quick way to preview or print a specific report based on user input.
Observed behavior: The user needs a VBA or macro solution to collect an ID and pass it as a filter condition to open a target report selectively.

Before you start

Ensure you know the exact name of your Access report and the primary key field name (e.g., 'ID') in your underlying table. It is highly recommended to back up your database before modifying form designs or adding VBA code.

Solution 1Recommended

Use an InputBox with VBA to Filter the Report

This method uses a simple VBA script tied to a command button. It prompts the user for the record ID using an InputBox and opens the report using the WhereCondition argument.

Using an InputBox is the quickest way to collect a single parameter from a user without adding extra input fields to your form layout.

Add a Command Button

Open your Access form in Design View. Drag and drop a Command Button from the Form Design toolbar onto your form. If the Command Button Wizard appears, simply click Cancel.

Open the VBA Editor

Right-click the newly added button and select 'Build Event...'. Choose 'Code Builder' and click OK to open the VBA editor for the button's On Click event.

Insert the VBA Code

In the code window, insert the following logic: Dim recordID As Long recordID = CLng(InputBox("Enter the record ID:")) DoCmd.OpenReport "YourReportName", acViewPreview, , "ID = " & recordID Ensure you replace 'YourReportName' with the actual name of your report, and 'ID' with the primary key field name.

Save and Test

Save your VBA code and return to Access. Switch the form to Form View, click your new button, enter a valid ID in the prompt, and verify that the report opens correctly.

Use an InputBox with VBA to Filter the Report

Input Validation: If a user enters text instead of a number, or leaves the InputBox blank, it will cause an error. For production databases, consider adding 'IsNumeric()' checks before running DoCmd.OpenReport.

Solution 2

Use an Unbound Combo Box on the Main Form

By placing an unbound combo box on your form, you allow users to select from a validated list of available IDs rather than typing them manually, reducing the chance of errors.

Solution 3

Use an Unbound Dialog Form for Report Parameters

Create a separate, small popup form to collect parameters. The report queries this form directly, which is ideal for complex reports requiring multiple filter criteria.

Free Microsoft Office alternative

Try WPS Office for Your Documents, Spreadsheets, and Presentations

While Microsoft Access is a powerful tool for complex database management, WPS Office is an excellent, lightweight alternative for your daily document processing, spreadsheet data analysis, and presentations. Enjoy a highly compatible and free office suite today.

1. Visit the WPS Office Website: Go to the official WPS Office website to find the latest free version.
2. Download the Installer: Click the download button corresponding to your operating system (Windows, Mac, or Linux).
3. Install and Edit: Run the installer, open the application, and seamlessly open, edit, and save your existing Microsoft Office files.

Fully compatible with Microsoft Word, Excel, and PowerPoint formatsLightweight installation with blazing-fast performanceFree to use for everyday office tasks and document editingIntuitive tabbed interface to manage multiple files easily

microsoft office alternative - wps office

Frequently Asked Questions

How do I filter an Access report if my Record ID is a text field?

If your primary key or target field is formatted as Short Text instead of a Number, you must wrap the variable in single quotes within your VBA WhereCondition. For example: DoCmd.OpenReport "YourReportName", acViewPreview, , "ID = '" & recordID & "'".

Can I print the Access report directly instead of opening a preview?

Yes. In the DoCmd.OpenReport VBA method, change the window mode argument from acViewPreview to acViewNormal. This command automatically sends the filtered report directly to your default printer.

Why do I get a 'Type Mismatch' error when entering the ID?

A Type Mismatch error typically occurs when the VBA code expects a numeric value (e.g., using CLng), but the user inputs text or leaves the InputBox blank. To prevent this, validate the input using the IsNumeric() function before triggering the OpenReport command.

Can I apply multiple filter conditions when opening a report?

Yes. You can combine multiple criteria in the WhereCondition argument using SQL operators like AND / OR. For example: "CustomerID = " & custID & " AND Status = 'Active'".

Office Quick Solutions