Others

How to Build an Access Data Entry Form with Multiple Filters

Maira Mehtab Sep 28, 2026 868 views

Question details

The user wants to create a Microsoft Access audit form that dynamically filters observations based on a selected audit number and restricts data entry to exactly one response per observation.

Product: Microsoft Access
Device & OS: not provided
Scenario: Building a structured data entry form for an audit database that requires cascading filters and a one-response limitation.
Observed behavior: The user needs to implement a solution where selecting an audit automatically updates the available observations for the user to respond to.

Before you start

Ensure your Access database has properly structured relational tables, including separate tables for Audits, Observations, and Responses, with appropriate primary and foreign keys established.

Solution 1Recommended

Use Correlated Unbound Combo Boxes

Set up cascading (correlated) unbound combo boxes to dynamically filter the observations list based on the selected audit number.

By utilizing an unbound selection form, you can temporarily hold the user's audit selection and use it to filter the record source of the second combo box. This ensures that users only see observations relevant to the specific audit they are working on.

Create two unbound combo boxes

Open your form in Design View and add two unbound combo boxes. Assign the first combo box to select the audit and the second to select the observation.

Set up the After Update event

Select the first combo box (Audit), open the Property Sheet, and navigate to the Event tab. In the 'After Update' event, add code or a macro to clear the value of the second combo box and requery it (e.g., using Me.ObservationCombo.Requery).

Filter the observation combo box

Set the Row Source of the second combo box (Observation) to an SQL query that filters observations based on the value currently selected in the first combo box.

Enforce a single response limit

To ensure only one response is entered per observation, open the Responses table in Design View. Select the ObservationID field and set its 'Indexed' property to 'Yes (No Duplicates)'.

SQL Syntax Check: When writing your SQL statements for the Row Source or WHERE conditions, ensure you use proper syntax (e.g., using FROM instead of FORM) to prevent query execution errors.

Free Microsoft Office alternative

Need a simpler way to manage structured data? Try WPS Office

While Microsoft Access is powerful for complex relational databases, WPS Spreadsheets offers a highly intuitive, lightweight, and completely free alternative for managing, filtering, and analyzing structured data through advanced data validation and forms.

1. Download and Install: Download WPS Office for free from the official website and install it on your device.
2. Create Cascading Drop-downs: Open WPS Spreadsheets, navigate to the Data tab, and use Data Validation alongside the INDIRECT function to recreate dependent combo box functionality without writing complex VBA code.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Easily create cascading drop-down lists using Data Validation and the INDIRECT function.Lightweight installation with fast performance on any device.A completely free alternative to expensive Microsoft Office subscriptions.

microsoft office alternative - wps office

Frequently Asked Questions

How do I create a dependent drop-down list in Access?

To create a dependent (cascading) drop-down list, set the Row Source of your second combo box to a query that references the value of the first combo box. Then, use the After Update event of the first combo box to requery the second one.

How can I limit a user to exactly one response per observation?

You can enforce this at the database level by opening the Responses table in Design View, selecting the ObservationID field, and changing its Indexed property to 'Yes (No Duplicates)'. This guarantees a unique index.

Why is my second combo box not updating when I change the first one?

This typically occurs if the first combo box's After Update event is missing the Requery command, or if the Row Source query of the second combo box does not properly reference the name of the first combo box.

Office Quick Solutions