How to Add a Select All Option to Cascading Combo Boxes in Access
Question details
The user wants to provide a 'Select All' choice in a multi-level cascading combo box form to display all records without filtering a specific level.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building a customized database form with progressive, dependent combo box filters (e.g., Country > Region > City).
- Observed behavior
- The user needs to include a 'Select All' row inside the combo box drop-down or find an alternative way to bypass the filter for a specific tier to show all related records.
Ensure your form controls are correctly named and that you are familiar with accessing the Property Sheet to edit the Row Source and Record Source properties using SQL.
Use a UNION Query for the Combo Box Row Source
This method injects a physical 'Select All' text option into your combo box drop-down list by using a SQL UNION statement.
A standard combo box populated by a table only shows the data within that table. By utilizing a UNION query, you can combine your actual table data with an artificial row containing the value 'Select All'.
Right-click your targeted form in the Navigation Pane and select 'Design View'. Click on the combo box control you wish to modify.
Press F4 to open the Property Sheet. Go to the 'Data' tab, click on 'Row Source', and enter a UNION query. For example: SELECT RegionID, Region, 1 AS SortColumn FROM Regions WHERE CountryID=[Form]![cboFindCountry] UNION SELECT 0, "Select All", 0 FROM Regions ORDER BY SortColumn, Region;
Open the form's main Record Source query. Amend the criteria for this specific combo box filter to evaluate '0' as true. For instance, use the condition: WHERE RegionID = [Form]![cboFindRegion] OR [Form]![cboFindRegion] = 0.
Use Null Values to Return All Records
Instead of adding a new row, allow users to leave the combo box blank to implicitly skip the filter and return all records.
Looking for a Free Alternative to Microsoft Office?
While WPS Office does not feature a database application like Microsoft Access, it is an outstanding, lightweight, and free alternative for Word, Excel, and PowerPoint. Enjoy a seamless experience for your everyday document, spreadsheet, and presentation tasks.
Frequently Asked Questions
Why doesn't my dependent combo box update when I change the first one?
You need to add a VBA Requery command in the 'After Update' event of your first (parent) combo box. This forces the dependent combo box to refresh its Row Source based on the newly selected value.
Can I add a 'Select All' option without writing SQL?
If your combo box uses a 'Value List' rather than a Table/Query as its Row Source Type, you can manually type 'Select All' into the list. However, for table-driven cascading combo boxes, a UNION query in SQL is required.
Why use '0' for the 'Select All' ID instead of Null?
When using a UNION query, data types must match across the combined datasets. Autonumber Primary Keys in Access are numeric, so using '0' (a number that Autonumber never naturally generates) safely acts as the 'Select All' placeholder identifier.
