[SOLVED] Creating search box for a subform

Creating search box for a subform

I am trying to create a search form in MS Access for my system users, I have an unbound main form named FrmUserListMain and in it a text box named "txtsearch" - where a user types what is to be searched for - and also a subform named frmuserlist. The subform has fields "fullname" and "email". When I type in the text box, I want the data in the subform to be filtered to show the "fullname" or "email" that matches the one in the text box but if there is none the subform should just be blank.

What I tried and it didn't work

Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
Me.FilterOn = False
Me!frmUserList.Form.Filter = "[FullName] like '*" & Me.txtSearch & "*' or [Email] like '*" & Me.txtSearch & "*'"
Me!frmUserList.Form.FilterOn = True
With Me.txtSearch
.SetFocus
.SelStart = Len(.Text)
End With
End Sub

Pictorial of the main form and the subform with their respective text boxes

Solution

Interesting! I like your approach. I suggest a "If" statement and using ".Text" as below. So, the code captures the current input value and removes any existing filter when the user clears the search box. Please remember to specify the correct subform name from the property settings, as Access may automatically change the form name as default "subform" when you insert.

Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
    Me.FilterOn = False
    ' Me!frmUserList.Form.Filter = "[FullName] like '*" & Me.txtSearch & "*' or [Email] like '*" & Me.txtSearch & "*'"
    ' Me!frmUserList.Form.FilterOn = True
    If Len(Me.txtsearch.Text) > 0 Then
        Me!frmUserList.Form.Filter = "[FullName] like '*" & Me.txtSearch.Text & "*' or [Email] like '*" & Me.txtSearch.Text & "*'"
        Me!frmUserList.Form.FilterOn = True
    End If
    With Me.txtSearch
        .SetFocus
        .SelStart = Len(.Text)
    End With
End Sub