Search-as-You-Type in a Microsoft Access Form

Implement dynamic, real-time live search filtering for listbox controls and continuous forms.

Modern search-as-you-type interfaces give users immediate feedback as they type. While Access combo boxes support basic type-ahead matching, list boxes require a lightweight VBA module to deliver true real-time filtering.

Enhance Listbox Navigation

Listboxes offer a clean visual representation of records without forcing users to open dropdown menus. Follow the step-by-step implementation guide below to add live search filtering to any Access form.

💾 Download Free Sample Access Database (.ZIP)
Search as you type listbox demonstration in Microsoft Access 🔍 Click to enlarge

Step-by-Step Implementation Guide

1
Download Sample Database: Download and extract the sample Access database.
2
Import Module: Import the standard VBA code module modSearch into your Access database.
3
Add Search Textbox: Create a new textbox above your listbox named txtSearch and set its Default Value property to:
"(type to search)"
4
Add Clear Button: Create a small button or label named btnClearFilter with caption "X" to clear the filter quickly.
5
Add Result Count Label: Add an optional label or textbox named txtCount to display total matching rows.
6
Declare Space Flag: At the top of your form's VBA code module (in the Declarations section below Option Explicit), add:
Private blnSpace As Boolean

Form Event Procedures

Step 7: Search Box KeyPress Event (handles spacebar input)

Assign this code to the txtSearch_KeyPress event to prevent premature filtering when entering spaces:

Private Sub txtSearch_KeyPress(KeyAscii As Integer) On Error GoTo err_handle If KeyAscii = 32 Then blnSpace = True Else blnSpace = False End If Exit Sub err_handle: Select Case Err.Number Case Else MsgBox "An unexpected error has occurred: " & vbCrLf & Err.Description & _ vbCrLf & "Error " & Err.Number & "(" & Erl & ")" End Select End Sub
Step 8: Clear Button Click Event

Resets the search box and triggers the change event to restore full records:

Private Sub btnClearFilter_Click() On Error Resume Next Me.txtSearch.Value = "" txtSearch_Change End Sub
Step 9: Placeholder Focus Handlers (GotFocus / LostFocus)

Manages the placeholder prompt text as the user navigates into and out of the search box:

Private Sub txtSearch_GotFocus() On Error Resume Next If Me.txtSearch.Value = "(type to search)" Then Me.txtSearch.Value = "" End If End Sub
Private Sub txtSearch_LostFocus() On Error Resume Next If Me.txtSearch.Value = "" Then Me.txtSearch.Value = "(type to search)" End If End Sub
Step 10: Change Event (The Core Search Driver)

Executes every time the user types a character, passing queries to fLiveSearch():

Private Sub txtSearch_Change() Dim strFullList As String Dim strFilteredList As String If blnSpace = False Then Me.Refresh ' Ensure the current character is available in the control value ' 1. Specify full, unfiltered listbox RowSource strFullList = "SELECT RecordID, First, Last FROM tblNames ORDER BY First;" ' 2. Specify SQL statement filtered by search criteria strFilteredList = "SELECT RecordID, First, Last FROM tblNames " & _ "WHERE [First] LIKE ""*" & Me.txtSearch.Value & "*"" " & _ "OR [Last] LIKE ""*" & Me.txtSearch.Value & "*"" ORDER BY [First];" ' 3. Execute live search fLiveSearch Me.txtSearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount End If End Sub
Form Filtering Alternative: You can apply this same technique to filter continuous forms instead of listboxes. In the fLiveSearch function, substitute RowSource with RecordSource, and replace ListCount with RecordsetClone.RecordCount.