Filtering any field in subform based on data entered into textbox on main form (1 Viewer)

[jlb4350]

I've searched this forum (and many others) and although I can find many posts related to filtering subforms, I can really find anything that works for my specific situation. It may be a really simple fix, but here it is:

I have a main form (frmPeople) that has an unbound textbox and an unbound subform (sfrmPeopleSearch) that displays all the fields and records in a table. What i'd like to do is filter the subform based on the data entered into the textbox. I'd like for the filter to include all fields, not just a single field (let's assume there are two fields - Name and Address).

Here is the code i found on the web but I can't get it to work. I don't get any error, but simply nothing happens when I enter data into the textbox:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String
strWhere = ""


If Not IsNull(Me.txtSearch) Then
    strWhere = strWhere & " ([EmployeeName] like '*" & Me.txtSearch & "*' OR "
    strWhere = strWhere & " [EmployeeAddress] like '*" & Me.txtSearch & "*') AND "
End If

'remove final AND
If strWhere <> "" Then
    strWhere = Left(strWhere, Len(strWhere) - 5)


    Forms!frmPeople.sfrmPeopleSearch.Form.Filter = strWhere
    Forms!frmPeople.sfrmPeopleSearch.Form.FilterOn = True
Else
    strWhere = "1=1" 'this is always true and forces the filter to clear
    Forms!frmPeople.sfrmPeopleSearch.Form.Filter = strWhere
    Forms!frmPeople.sfrmPeopleSearch.Form.FilterOn = True
End If
End Sub

Any ideas? Thanks in advance for your help!

 

[pbaldy]

Not particularly clean, but looks like it should work. I'd drop the leading space but I'm not sure it causes a problem. Use this to see how the filter variable is ending up:

http://www.baldyweb.com/ImmediateWindow.htm

 

[MajP]

This one only handles all string and memo fields. It could be modified for dates and numerics.

Public Function getAllFieldSearch(rs As DAO.Recordset, ByVal strFind As String, PKfield As String, Optional numericPK As Boolean = False) As String
  'Parmaters
  'RS: Recordset to filter
  'strFind: The string to Find
  'PKField: The name of the primary key field
  'numericPK: True if the primary key is numeric
  'This really makes sense only for strings. I would not use
  'this to search for a date or number. So check only the text fields
  Dim strOut As String
  Dim fld As DAO.Field
  Do While Not rs.EOF
     For Each fld In rs.Fields
      'check on text fields
      If fld.Type = dbMemo Or fld.Type = dbText Then
         If fld.Value Like "*" & strFind & "*" Then
           If strOut = "" Then
              If numericPK Then
                strOut = PKfield & " = " & rs.Fields(PKfield)
                Exit For
              Else
                strOut = PKfield & " = '" & rs.Fields(PKfield) & "'"
                Exit For
             End If
           Else
             If numericPK Then
               strOut = strOut & " OR " & PKfield & " = " & rs.Fields(PKfield)
               Exit For
             Else
               strOut = strOut & " OR " & PKfield & " = '" & rs.Fields(PKfield) & "'"
               Exit For
             End If
           End If
         End If
      End If
    Next fld
    rs.MoveNext
  Loop
  getAllFieldSearch = strOut
End Function

Since many records could match I use it to return the PKs of the matched fields and use it to set the filter. Like

Public Sub performSearch()
  mSearchString = getAllFieldSearch(Me.subFrmCtlEmployees.Form.RecordsetClone, Nz(Me.txtSearch, ""), "EmployeeID", True)
  MsgBox mSearchString
  With Me.subFrmCtlEmployees.Form
    .Filter = ""
    .FilterOn = False
    .Filter = mSearchString
    .FilterOn = True
    If .RecordsetClone.RecordCount = 0 Then
      MsgBox "No matches found."
      .FilterOn = False
    End If
  End With
End Sub

 

[jlb4350]

Hmmm, i got it working. I deleted the whole form and rebuilt it using the (corrected) code above and it started filtering. So, i guest my next question is, is there a possibility to filter by date range? Let's say I had another field that is of a date type called HireDate. Is there a way to add two more text boxes to this form where I can select a beginning date and end date and the subform will filter all records between the two?

Thank you again for all your help!

***EDIT***
Just saw the previous reply, i'll try that and see what happens!

 

[MajP]

I was interpreting this differently

I'd like for the filter to include all fields, not just a single field

The code I posted would look in each field of the subform to see if the value matched
(fieldOne = "abc" or FieldTwo = "abc" or .... FieldN = "abc")
However, the code you are showing only checks two specific fields. What you are asking and what you are showing are very different. But sure you can add two date fields and search between those. The question is do you want that to stand by itself or be part of the bigger search such as
EmployeeName = "Smith" OR DateHire Between #1/1/2019# and #2/1/2019#

 

[jlb4350]

I was interpreting this differently

The code I posted would look in each field of the subform to see if the value matched
(fieldOne = "abc" or FieldTwo = "abc" or .... FieldN = "abc")
However, the code you are showing only checks two specific fields. What you are asking and what you are showing are very different. But sure you can add two date fields and search between those. The question is do you want that to stand by itself or be part of the bigger search such as
EmployeeName = "Smith" OR DateHire Between #1/1/2019# and #2/1/2019#

You're correct in that I want to search all fields in the subform. The two fields are only for example purposes but the real database, which unfortunately has a ton of sensitive data, has about 12 fields in the subform, including two different date fields. I was able to get it working just by adding more lines in the if statement, but I'd like to try your code later when i have more time to see if I can include the date fields in the search too. Unfortunately, the code you posted is completely foreign to me (I'm still a coding noob), so I'll have to take some time with it. I really do appreciate your help though. I will update here once I've tried it.