How to Filter an Access Form with a Combo Box When It Opens
Question details
The user wants to filter an Access form dynamically based on a user's selection from a combo box drop-down list, avoiding the need to build a separate form for every single category or company.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Opening a target form to display specific records filtered by a value selected in an initial search form's drop-down menu.
- Observed behavior
- Instead of creating and maintaining dozens of individual forms or macros for each company, the user seeks a unified, dynamic filtering method.
Ensure you have your primary data table ready and identify the key field (like CompanyID) that will serve as the linking value between your combo box and the target form.
Use DoCmd.OpenForm with a WhereCondition (Recommended)
This is the most flexible approach. It uses a simple VBA command on a search form to open your target form filtered to the exact value selected in your combo box.
By utilizing the WhereCondition argument in the DoCmd.OpenForm method, you can dynamically pass the combo box value as a filter without permanently altering the target form's underlying query.
Create a new blank form and add a Combo Box control. Name it 'cboCompanySearch' and set its Row Source to the table containing your companies.
Place a Command Button on the form next to the combo box. You will use this button to trigger opening the filtered form.
Open the Property Sheet for the button, go to the Event tab, and click the ellipsis (...) next to 'On Click'. Choose Code Builder.
Type the following code: DoCmd.OpenForm "YourTargetFormName", , , "CompanyID = " & Me.cboCompanySearch
Reference the Combo Box Directly in the Form's Query
If you prefer avoiding VBA, you can modify the target form's Record Source query to automatically look at the value in your search form's combo box.
Looking for a Free, Lightweight Office Suite? Try WPS Office
While WPS Office does not include a direct relational database application like MS Access, its powerful Spreadsheet tool provides robust data validation, advanced filtering, and pivot tables. You can easily manage large datasets, create drop-down lists, and dynamically filter records without writing any complex VBA code.
- 1. Create Drop-down Lists: Open WPS Spreadsheet, select a cell, and go to Data > Validation to create a drop-down list from a range of cells.
- 2. Apply Advanced Filters: Select your dataset headers, click Data > Filter, and use the drop-down arrows to instantly filter records based on specific criteria.
Frequently Asked Questions
How do I filter an Access form using multiple combo boxes?
You can combine multiple criteria by using the AND operator in your WhereCondition. For example: DoCmd.OpenForm "TargetForm", , , "CompanyID = " & Me.cboCompany & " AND Status = '" & Me.cboStatus & "'".
Why is my Access form prompting for a parameter value when it opens?
This usually occurs if the target form's query references a combo box name that is misspelled, or if the search form containing the combo box was closed before the query could read its value. Always keep the search form open while running the filtered form.
Can I put the combo box directly in the header of the target form instead of using a popup?
Yes. You can place an unbound combo box in the form's header. In the combo box's After Update event, write a VBA script to set the form's filter directly: Me.Filter = "CompanyID = " & Me.cboCompany and then enable it using Me.FilterOn = True.
