How to Use Two Access Combo Boxes to Filter Records Together
Question details
The user needs to configure two unbound Microsoft Access combo boxes (such as purchase orders and assembly numbers) so they work together to apply a combined filter to records on a form.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Filtering records dynamically on a database form based on multiple criteria selected from different unbound combo boxes.
- Observed behavior
- When both combo boxes are used, they either act independently, or the generated macros from the Combo Box Wizard override one another instead of combining the filter criteria.
Ensure your combo boxes are unbound, note their exact object names (e.g., cboPO and cboAssembly), and avoid using spaces or special characters in your field names.
Apply a Combined Filter Using VBA After Update Events
Use Visual Basic for Applications (VBA) to dynamically build and apply a combined filter string whenever either combo box is updated.
If you created the combo boxes using the wizard, the generated macros might conflict. Converting them to VBA event procedures allows you to handle both criteria simultaneously and elegantly manage empty values.
Open your Microsoft Access database, right-click the form containing your combo boxes, and select 'Design View'.
Select the first combo box (e.g., your Purchase Order dropdown). Open the Property Sheet, navigate to the 'Event' tab, click the ellipsis (...) next to 'After Update', and choose 'Code Builder'.
In the VBA editor, construct an If...Then statement to check for values. For example: If IsNull(Me.cboPO) Then Me.Filter = "AssemblyID = " & Me.cboAssembly Else Me.Filter = "POID = " & Me.cboPO & " AND AssemblyID = " & Me.cboAssembly End If.
Below your condition, add the command to execute the filter by typing: Me.FilterOn = True.
Repeat steps 2-4 for the second combo box's After Update event, ensuring the VBA code evaluates both fields so the filter updates accurately regardless of which box is clicked first.
Remove Conflicting Embedded Macros
Clear out any existing standalone macros that might interfere with your VBA event procedures.
Manage Data Easily with WPS Office
While Microsoft Access is a dedicated database tool, many common data filtering, tracking, and reporting tasks can be seamlessly handled using WPS Spreadsheet. WPS Office is a free, lightweight suite that is fully compatible with Microsoft Office formats, offering an intuitive way to manage complex data without writing VBA code.
Frequently Asked Questions
Why do my Access combo boxes override each other when filtering?
This usually happens when the combo boxes are created using the Combo Box Wizard, which generates independent macros. These macros apply separate filters instead of combining the criteria. Using a unified VBA script in the After Update event allows them to work together seamlessly.
Should I use bound or unbound combo boxes for filtering records?
You must use unbound combo boxes for filtering records on a form. Bound combo boxes modify the underlying data of the current record you are viewing, whereas unbound boxes simply capture your selection to use as search or filter criteria.
How do I handle empty or null values in my combo box filter?
Use the IsNull() function in your VBA code. You can write an If...Then...Else statement to check if a combo box is empty. If it is, omit that specific field from your concatenated filter string to prevent 'type mismatch' or filtering errors.
