Fix Combo Box Search Not Working on an Access Query Form
Question details
The user needs to resolve an issue where a combo box fails to search for and locate records on a Microsoft Access form that is based on a query.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to use a combo box drop-down to navigate to or search for specific records on a form linked to a query.
- Observed behavior
- The combo box search functionality does not return the expected records or fails to trigger a search, often due to incorrect wizard settings or missing VBA/macro events.
Verify that your form is successfully bound to the underlying query by checking the Record Source property, and ensure that the query itself returns the correct data when run independently in Datasheet View.
Use the Combo Box Wizard to Reconfigure the Search
The most reliable way to create a search combo box is to let the built-in wizard configure the necessary embedded macros and properties for you.
Often, manually created combo boxes lack the specific 'After Update' macro required to find a record. Recreating the control using the wizard automatically generates the correct configuration.
Right-click your form in the navigation pane and select 'Design View'.
On the Form Design tab in the Controls group, ensure that the 'Use Control Wizards' button is highlighted and active.
Click the Combo Box icon in the Controls gallery and click on the form to place it. The Combo Box Wizard will open automatically.
Choose the third option: 'Find a record on my form based on the value I selected in my combo box', and click Next.
Select the field you want to search by (e.g., Customer Name), adjust the column width so the data is visible, and click Finish to complete the setup.
Adjust Combo Box Properties Manually
If you prefer not to use the wizard, verify that your combo box properties are correctly mapped to the query's fields.
Implement Partial-Name Searches with Wildcards
For advanced searches where users only type part of a name, you need to modify the query criteria using wildcards.
Try WPS Office as a Free Alternative to Microsoft Office
While WPS Office does not include a direct database program like Access, it serves as a powerful, lightweight, and free alternative to Microsoft Word, Excel, and PowerPoint. If you are extracting query data to spreadsheets or creating reports, WPS Office handles it seamlessly without expensive subscription fees.
- 1. Download the Installer: Visit the official WPS website and download the free installer for your operating system.
- 2. Install WPS Office: Run the setup file and follow the on-screen prompts to complete the lightweight installation.
- 3. Open Your Exported Data: Launch WPS Spreadsheets to easily open and analyze data exported from your database queries.
Frequently Asked Questions
Why doesn't the 'Find a record' option appear in my Combo Box Wizard?
This option only appears if the form is 'bound' to data. You must ensure the form's Record Source property contains a valid table or query before you run the Combo Box Wizard.
Why is my combo box showing ID numbers instead of names?
This occurs when the Bound Column is set to the ID, but the Column Width for that ID column has not been set to 0. Open the Property Sheet, go to the Format tab, and set the Column Widths property to hide the ID column (e.g., type '0cm;3cm').
Can I use a combo box to filter a form instead of just jumping to one record?
Yes, but filtering requires a different approach than finding a record. You will need to use a standard combo box and write VBA code or an embedded macro on the 'After Update' event to apply a filter string to the form's Filter property.
Why does my combo box search fail when I use partial text?
By default, the combo box search requires an exact match. To search by partial text, you must modify the query's criteria to include wildcards, such as Like '*' & [SearchValue] & '*'.
