How to a Create Dynamic Search Form in MS Access

If you want to add an awesome dynamic search box to one of your Microsoft Access forms, this is the post for you. This search box will start filtering the data in your form as you soon as you begin typing. The more you type, the more it will filter the data. The search box can be setup to filter on one or more of the fields in your form.

This search box is fairly simple to implement, but it does require some VBA code. Let’s see how to build it in the steps below. You can download this example database here.

Step 1: Add Text Box and Button Controls to Your Form

At the top of our form in Design View, we need to add a Text Box control to our form. This will be our search box. In the Property Sheet, name it “txt_search_box.”

We also need to added a Button control next to the search box that we’ll use as a clear button. In the Property Sheet, name this button “btn_clear.”

Step 2: Add VBA to the Text Box Control

On the newly created Text Box control that we’ve added to our form, we’ll want to add VBA code to the On Key Up event. Click on the small three dot button next to the On Key Up event in the Property Sheet to open the code editor.

Once the code editor opens, enter the following VBA code.

Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
'   Search Box
'
'******************************************************************

    '   Declare variables
    Dim filter_data As String
    
    '   Apply filter if text is entered in search box
    If Len(txt_search_box.Text) > 0 Then
    
        '   Set the text entered in search box equal to the variable defined above
        filter_data = txt_search_box.Text
    
        '   Build the filter string
        '   Need to add to filter string for each field you wish to search
        Me.Form.Filter = " [id] LIKE '*" & filter_data & "*'" _
        & " OR [first_name] LIKE '*" & filter_data & "*'" _
        & " OR [last_name] LIKE '*" & filter_data & "*'" _
        & " OR [street_address] LIKE '*" & filter_data & "*'" _
        & " OR [city] LIKE '*" & filter_data & "*'" _
        & " OR [state] LIKE '*" & filter_data & "*'"

        '   Apply filter
        Me.Form.FilterOn = True
    
        '    Prevent text in search box from being removed after refresh
        txt_search_box.SelStart = Len(txt_search_box.Text)
    
    Else
    
        '    Clear filter string
        Me.Form.Filter = ""
        
        '   Remove filter
        Me.Form.FilterOn = False
        
        '   Set focus
        txt_search_box.SetFocus
        
    End If

End Sub

Step 3: Add VBA to the Button Control

The button we’ve added to our form will be used to clear the search box. Select the Button control to highlight it. In the Property Sheet, select the small three dot button next to the On Click event to open the code editor.

Once the code editor opens, enter the following VBA code.

Private Sub btn_clear_Click()
'******************************************************************
'
'   Clear Button
'
'******************************************************************

    '   Clear search box
    txt_search_box.Value = ""
    
    '   Clear filter
    Me.Form.Filter = ""
    
    '   Turn filter off
    Me.Form.FilterOn = False
    
    '   Set focus
    txt_search_box.SetFocus

End Sub

That’s all there is too it! Download the sample database here. Enjoy!