Others

How to Create an Access VBA Search Box for Multiple Fields

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to create a single combo box in Microsoft Access that uses VBA to search either a numeric reference field or a text location name, while retaining leading zeros for numeric values.

Product: Microsoft Access
Device & OS: not provided
Scenario: Developing a dynamic search interface where a single combo box input filters database records based on either a numeric reference ID or a text-based location name.
Observed behavior: The search control requires VBA criteria to dynamically determine the input data type, and the underlying table must be configured correctly so that numeric references with leading zeros are not truncated.

Before you start

Ensure your Microsoft Access database is open in Design View and that you have backed up your form and table structures before adding custom VBA code.

Solution 1Recommended

Implement a Multi-Field VBA Search Combo Box

Configure a combo box to accept both list selections and manual inputs, using an After Update VBA event to apply the correct search filter based on the input type.

By setting the 'Limit To List' property to No, users can either type a reference number or select a location from the dropdown. The VBA code utilizes the IsNumeric function to dynamically construct the appropriate WHERE clause for your search.

Add and configure the Combo Box

Open your form in Design View, insert a Combo Box from the Controls menu, and name it 'cboSearch'. In the Property Sheet, set its Row Source to query your text fields (e.g., SELECT LocationName FROM Locations ORDER BY LocationName;).

Allow custom manual entries

Navigate to the Data tab of the Combo Box Property Sheet and change 'Limit To List' to 'No'. This permits users to type numeric reference IDs that are not present in the location dropdown list.

Add VBA to the After Update event

Go to the Event tab, select '[Event Procedure]' for the 'On After Update' property, and click the ellipsis (...). Enter VBA code that checks 'If IsNumeric(Me.cboSearch)'. If true, set your criteria string for the numeric field; if false, set it for the text field.

Execute the form filter

Conclude your VBA script by applying the criteria string using the DoCmd.OpenForm method (e.g., DoCmd.OpenForm "YourFormName", WhereCondition:=strCriteria) to display the filtered results.

Handling Quotation Marks: When constructing criteria for text inputs, ensure you properly escape quotation marks using the Replace function (e.g., Replace(Me.cboSearch, """", """"")) to prevent syntax errors during the search.

Solution 2

Preserve Leading Zeros in Numeric References

Change the table data type for numeric references to Short Text to prevent Access from automatically stripping leading zeros.

Free Microsoft Office alternative

Looking for a Reliable and Free Office Suite? Try WPS Office

While Microsoft Access requires a premium subscription to build VBA-driven forms, you can handle extensive data management, filtering, and spreadsheet macros using WPS Spreadsheet. WPS Office offers a powerful, lightweight, and completely free alternative to Microsoft Office.

1. Download the WPS Office installer: Visit the official WPS Office website to download the free, lightweight installation package for your operating system.
2. Install and manage your data: Run the setup file and open WPS Spreadsheet to seamlessly import, manage, and filter your existing datasets.

100% compatible with Microsoft Office formats (.xlsx, .docx, .pptx).Built-in support for advanced data filtering and spreadsheet management.Lightweight installation with a highly familiar, tabbed user interface.Completely free to use, featuring comprehensive PDF editing tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a Data Type Mismatch error in my VBA search?

This error occurs when VBA attempts to compare a text string against a number field without proper delimiters, or vice versa. Ensure you use single or double quotes around text values in your criteria string, and omit quotes when querying standard number fields.

What does setting 'Limit To List = No' do in an Access Combo Box?

Setting 'Limit To List' to 'No' allows users to type custom values into the combo box that are not present in the predefined dropdown list. This is essential for a dual-purpose search box that accepts both list selections and manual ID entries.

Can I enforce numeric entry on a Short Text field in Access?

Yes. Even if a field is set to Short Text to preserve leading zeros, you can use an Input Mask (e.g., '0000' for exactly four digits) or add a Field Validation Rule to restrict user inputs to numeric characters only.

Office Quick Solutions