Search all records in subform from main form

I have a button that can search locations in all records in a table in the subform.

But it seems to show all records that have the [Location] in them instead of only records with the specific location entered in the textbox.

But once I've done my search, I can't seem revert the form to the original clear state, so that I can go back to searching other things.

Private Sub StartSearch2_Click()
    Dim rs As Recordset
    Dim strSQL As String
    strSQL = "select * from [FormTable] where [Location]='" & Me.LocSearch & "'"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rs.BOF And Not rs.EOF Then
        Set Me.Recordset = rs
    Else
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
        Me.RecordSource = strOriginalSQL
    End If
    Me.LocSearch = Null
End Sub

Solution

Another approach is to not change the Record Source of your form and instead set the Filter property.

Set the Record Source to FormTable. You can do this in the form designer.

Then set the Filter with

Me.Filter = "Location='" & Me.LocSearch & "'"
Me.FilterOn = True

You can clear the filter with
```
Me.Filter = ""
Me.FilterOn = False
```

If you want to filter a subform, you can do this from the main form with

With Me!mysubform.Form
    .Filter = "Location='" & Me.LocSearch & "'"
    .FilterOn = True
End With

It is a good idea to escape any single quotes in the search string

Me.Filter = "Location='" & Replace(Me.LocSearch, "'", "''") & "'"