Filter More Than 65,000 Records in a Microsoft Access Combo Box
Question details
The user needs a practical way to search approximately 70,000 diagnosis records from an Access form.

- 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.
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.
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.
Open the form in Design View. Add an unbound text box named txtSearch and a list box named lstResults using the Design tab.
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.
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
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.
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.

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.

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.




