Others

Filter More Than 65,000 Records in a Microsoft Access Combo Box

Adam DavisAdam Davis Nov 20, 2026 868 views

Question details

The user needs a practical way to search approximately 70,000 diagnosis records from an Access form.

Filter More Than 65,000 Records in a Microsoft Access Combo Box
Product
Microsoft Access
Device & OS
not provided
Scenario
A form needs to let users find and select a diagnosis from a large record set.
Observed behavior
Loading approximately 70,000 diagnosis records into a combo box is slow or impractical.
Before you start

Identify the diagnosis table, its unique ID and description fields, and the form where users will search. The example uses a local Access table named Diagnoses with DiagnosisID and IDesc fields; replace these names with your own.

Solution 1Recommended

Filter a list box as the user types in Access

Use a search text box and a limited list of matching diagnoses instead of loading the entire record set.

The Change event reads the text box's current Text property, including characters that have not yet been committed as its Value. The example shows up to 100 descriptions that start with the entered text and shows no results when the search is empty.

1
Add search and result controls

Open the form in Design View. Add an unbound text box named txtSearch and a list box named lstResults using the Design tab.

2
Configure the list box

Select lstResults, open its Property Sheet, and set Row Source Type to Table/Query, Column Count to 2, Bound Column to 1, and Column Widths to 0;3 to hide the ID and display the description. Adjust the displayed column width as needed.

3
Create the Change event

Select txtSearch and, on the Property Sheet's Event tab, set On Change to [Event Procedure]. Click the ellipsis button and enter this code in the form's VBA module, replacing the table and field names as needed: Private Sub txtSearch_Change() Dim searchText As String searchText = Replace(Me.txtSearch.Text, "'", "''") If Len(searchText) = 0 Then Me.lstResults.RowSource = "SELECT TOP 100 DiagnosisID, IDesc FROM Diagnoses WHERE 1=0" Else Me.lstResults.RowSource = "SELECT TOP 100 DiagnosisID, IDesc FROM Diagnoses WHERE IDesc Like '" & searchText & "*' ORDER BY IDesc" End If Me.lstResults.Requery End Sub

4
Test the search

Save the form and open it in Form View. Type and delete characters in txtSearch to check that lstResults narrows and expands. If more than 100 rows match, type additional characters to refine the results.

5
Use the selected diagnosis

Add logic to the lstResults After Update event, or use a button on the form, to copy the selected list box value—the bound DiagnosisID—to the appropriate destination control or field.

Filter a list box as the user types in Access
Wildcard and result limit: The example uses the Access ANSI-89 wildcard *. If the database uses ANSI-92 query mode, use % instead. Typed wildcard characters are treated as patterns. The 100-row limit keeps the result list manageable; change TOP 100 if appropriate.
Free Microsoft Office alternative

Work with exported diagnosis lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative for everyday document and spreadsheet work. Its familiar interface and Microsoft Office format support make it easy to move exported lists into a spreadsheet workflow. It does not replace Access forms or fix Access control limits.

Filter exported diagnosis lists in WPS Spreadsheets.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar interface for a smoother transition from Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Will moving the diagnosis table to SQL Server remove the Access control limit?

No. The combo box is still an Access control even when its data is stored in SQL Server. Filtering the Row Source before displaying results remains useful.

Why use the Text property in the Change event?

The Change event fires while the user is editing. Reading Me.txtSearch.Text retrieves the current characters; the control's Value may not yet include the latest edit.

Can I use a combo box instead of a list box for the filtered results?

Yes. Set a combo box's Row Source to a similarly filtered query and requery it as the search text changes. You may need additional code to manage when its drop-down opens and how users select a result.

Why aren't all matching diagnoses displayed?

The example uses SELECT TOP 100 to limit the result list. Enter more characters in txtSearch to narrow the matches, or adjust the TOP value in the Row Source code.

What if the search returns no matches even though records exist?

Check the table and field names in the VBA code and confirm the wildcard mode. Use * for ANSI-89 queries or % for ANSI-92 queries, then test with the beginning of a known description.