Others

How to Create an Access Report from Form Dropdown Selections

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs to configure a button in a Microsoft Access form to generate a report filtered by the values selected in the form's dropdown combo boxes.

How to Create an Access Report from Form Dropdown Selections

Product: Microsoft Access
Device & OS: not provided
Scenario: Generating a custom data report dynamically filtered by user-selected variables from a graphical form interface.
Observed behavior: The report must compile and display records matching the parameters passed from the dropdown selections when a command button is clicked.

Before you start

Ensure your Access form is properly set up with named combo boxes (dropdowns) and that you have a base Select Query built for your intended report.

Solution 1Recommended

Filter Query by Form Controls and Open Report via VBA

Modify your report's underlying query to reference the form's dropdown selections as parameters, and use a VBA command to trigger the report generation.

By setting the report's RecordSource to a query that directly reads the values from your open form, Access can automatically filter the records before compiling the report. Connecting this to a button click makes the process seamless for end users.

Modify the Report's Source Query

Open the query used as your report's RecordSource in Design View. Find the field corresponding to your dropdown (e.g., Shipment Type) and in the Criteria row, enter the syntax: Forms!YourFormName!YourComboBoxName.

Set Up the Report's RecordSource

Save and close the query. Open your report in Design View, access the Property Sheet, and ensure the 'Record Source' property is set to the query you just modified.

Configure the Form Button

Open your form in Design View and select the button you want to use to generate the report. Open the Property Sheet for the button, go to the 'Event' tab, and click the builder button (...) next to 'On Click'.

Add the VBA Code

Choose 'Code Builder' to open the VBA editor. Inside the button's Click event procedure, type the following code: DoCmd.OpenReport "YourReportName", acViewPreview. Save the VBA module and test your form.

Filter Query by Form Controls and Open Report via VBA

Keep the Form Open: Your form must remain open when you run the report. The query needs to pull the live selected values from the dropdown boxes to filter the data properly.

Free Microsoft Office alternative

Looking for a Lightweight and Free Office Suite?

While WPS Office does not include a database management tool like Microsoft Access, it offers excellent free alternatives for Word, Excel, and PowerPoint. If you need to manage data and generate reports, WPS Spreadsheet provides powerful data analysis, filtering, and pivot tables to help you track information effortlessly without complex programming.

1. Download and Install: Visit the official WPS Office website to download the free suite and install it on your device.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and either import your exported data or create a new dataset from scratch.
3. Create Dropdowns and Reports: Use the Data Validation feature to create custom dropdown lists, and utilize Pivot Tables to dynamically summarize and report your data instantly.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Powerful Spreadsheet tool with advanced data validation (dropdowns), filtering, and Pivot Table capabilities for reporting.Lightweight installation and low system resource consumption.Familiar, intuitive interface requiring no steep learning curve or VBA knowledge.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Access report prompting me to 'Enter Parameter Value'?

This prompt appears when Access cannot find the form or control specified in your query criteria. Verify that your form is currently open and that the spelling of the form name and combo box name in the criteria (Forms!FormName!ComboBoxName) exactly matches the names in your database.

Can I filter a report using multiple dropdown boxes at the same time?

Yes, you can filter by multiple variables (like date and shipment type). Simply add the Forms!FormName!ComboBoxName syntax to the Criteria row of each relevant field in your query's Design View. Placing them on the same Criteria row functions as an 'AND' condition.

How do I show all records if a dropdown box is left blank?

To display all records when no selection is made, adjust your query criteria to account for null values. Use this syntax in the Criteria row: Like IIf(IsNull([Forms]![FormName]![ComboBoxName]), "*", [Forms]![FormName]![ComboBoxName]). This returns the filtered value if selected, or all values if the box is empty.

Office Quick Solutions