formsfilteringms-access-2003subformadp

Filter subform based on what is typed in a textbox on the subform


I'm trying to filter the records shown on a subform based on what is typed in a textbox on the subform. The subform is called Users, and it is on the Group form. On the Users subform there is a textbox called txtFilter. If I type "W" in txtFilter, I want to show only records in which the User's lastName or userName begins with a "W". As I continue typing W...A...L I want only Users whose lastName or UserName begin with "Wal" to show up.

I have some vague idea that I have to use either the recordset property or the subform's serverFilter to do this, but I'm really at a loss as to what to do. Please help me!


Solution

  • Private Sub txtFilter_Change()
    
        If Nz(Me.txtFilter.Text, "") = "" Then
            Me.FilterOn = False
            Me.txtFilter.SetFocus
            Exit Sub
        End If
        Me.Filter = "lastName like '" + Me.txtFilter.Text + "%' or userName like '" & _
            Me.txtFilter.Text + "%'"
        Me.FilterOn = True
        Me.txtFilter.SetFocus
        Me.txtFilter.SelStart = Len(Nz(Me.txtFilter.Text, "")) + 1
    
    
    End Sub