Search all records in subform from main form-CodePudding

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

But it seems to show all records that have the location in them or not instead of only records with the specific locationed entered in the textbox.

But once I've done my search, I can't seem revert the form the the original clear state so that I can go back to searching other things, it kinda messes everything up and saves the searched location under a different record.

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

CodePudding user response：

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, "'", "''") & "'"