Best Method to Search Records using Manual Values in MS Access

Sometimes you need to search records using manual values in a form. For example, a “Gender” column contains either Male or Female values to filter. So, there is no need to specify them through the table fields. We will learn a simple ordinary method and a more easy-to-access method.

Method 1: Combo Box Drop-down List

In the first method, create a combo box with the row source type as “Value List” and Values like “Male, and Female”. Put the VBA Code into the “After Update” event of the Combo Box:

Private Sub Search3_AfterUpdate()
Dim strGenderFind As String
    'Criteria
    strGenderFind = "[Gender]= '" & Me.Search3 & "'"
    Me.Form.Filter = strGenderFind
    Me.Form.FilterOn = True

End Sub

Method 2: Option Group

This is an easier approach to use “Option Group” rather than the “Combo Box” method. Values are visible and can be easily accessed to filter the records.

Create an “Option Group” from the Form Design Menu. Enter the Label Names like “All, Male, and Female”. From the Option Group Wizard, leave the first option selected which is “Yes, the default choice is: All” and click on Next.

The option “All” is used to disable or make the form’s filter as False to display all records.

Now, select the frame of the “Option Group” and click on the “On Click” event for the VBA code:

Private Sub Search4_Click()
If Me.Search4 = 1 Then
    Me.Form.FilterOn = False
        Else
            If Me.Search4 = 2 Then
                strGenderFind = "[Gender]= 'Male'"
                Me.Form.Filter = strGenderFind
                Me.Form.FilterOn = True
                Else
                    If Me.Search4 = 3 Then
                        strGenderFind = "[Gender]= 'Female'"
                        Me.Form.Filter = strGenderFind
                        Me.Form.FilterOn = True
                End If
        End If
End If
End Sub