How to Use Access Combo Boxes as Query Criteria
Question details
The user wants to use combo boxes on an Access form to select players for a query, passing player IDs as criteria instead of typing names manually to avoid errors.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Filtering query results using form controls while ensuring data normalization and avoiding manual data entry errors.
- Observed behavior
- The current query requires manual typing of player names, which causes spelling errors and fails to utilize relational foreign keys efficiently.
Ensure you have your primary tables (e.g., Players) created with proper Primary Keys and your destination query ready before modifying the form controls.
Configure Combo Boxes to Pass IDs as Query Criteria
Set up the combo box properties to hide the ID column while displaying the name, then reference the combo box directly in the query criteria.
To avoid manual typing errors and maintain database normalization, forms should display user-friendly names while passing standard ID numbers (foreign keys) to queries. This is achieved by adjusting the column widths and bound column properties of the combo box.
Open your form in Design View, select the combo box, and navigate to the Property Sheet. Set the 'Row Source' to select both the ID and the Name, for example: SELECT PlayerID, PlayerName FROM Players ORDER BY PlayerName;
In the Format tab of the Property Sheet, set Column Count to 2 and Column Widths to 0";1". This hides the first column (PlayerID) and displays the second column (PlayerName).
In the Data tab of the Property Sheet, set the Bound Column to 1. This ensures that when a user selects a name, the combo box actually stores and passes the hidden PlayerID.
Open your target Query in Design View. In the Criteria row under your Foreign Key field (e.g., PlayerID), enter the reference to your combo box: [Forms]![YourFormName]![YourComboBoxName].
Save both the form and the query. Open the form in Form View, select a player from the combo box, and run the query (often via a command button on the form) to verify it filters correctly.
Need a Free Microsoft Office Alternative? Try WPS Office
While Microsoft Access is a specialized database tool, many complex data management, filtering, and reporting tasks can be seamlessly handled using WPS Spreadsheet. WPS Office is a lightweight, full-featured suite that provides excellent compatibility with Microsoft Office formats without the heavy subscription costs.
Frequently Asked Questions
Why does my query prompt for a parameter when I run it?
This typically happens if the form name or combo box name is misspelled in the query criteria, or if the form is closed when the query executes. Ensure the form remains open and the reference [Forms]![FormName]![ControlName] matches your design exactly.
How do I make the combo box show the name instead of the ID?
In the combo box Property Sheet, set the Column Count to 2 and the Column Widths to 0";1". Setting the first column's width to 0 inches effectively hides the ID, displaying only the second column (the name) to the user.
Can I use multiple combo boxes to filter the same query?
Yes. You can reference multiple combo boxes in different columns of your query design grid. Just add the specific [Forms]![FormName]![ControlName] reference to the criteria row of each respective field you want to filter.
