filter continuous form using textbox

Question

I need to let users filter a continuous form using values the user enters into a textbox. And the continuous form is also nested within a couple levels of navigation subforms. This sounds easy enough, but all the examples I find on the web use macros instead of vba.

I set up the structure and wrote an AfterUpdate procedure for a textbox txtFilter as follows:

Private Sub txtFilter_AfterUpdate()
    Dim filterval As String
    filterval = txtFilter.Value
    With Forms!Main!NavigationSubform.Form!NavigationSubform.Form
        .Filter = "LastName Like " & filterval
        .FilterOn = True
    End With
End Sub

I have played with different syntax, but none of it seems to work properly. Here is a link to download the relevant parts of the database from a file sharing site: http://jmp.sh/v/HGctZ4Ru74vDAjzN43Wq

Can anyone show me how to alter this so that users can use the textbox to filter the continuous form?

Answer 1

Option Compare Database
Option Explicit '<- always include this!!!!!

Private Sub txtFilter_AfterUpdate()
    Dim strFilter As String

    ' only set Filter when text box contains something
    ' to search for ==> don't filter Null, empty string,
    ' or spaces
    If Len(Trim(Me.txtFilter.Value & vbNullString)) > 0 Then
        strFilter = "LastName Like '*" & _
            Replace(Me.txtFilter.Value, "'", "''") & _
            "*'"
        ' that Replace() prevents the procedure from breaking
        ' when the user enters a name with an apostrophe
        ' into the text box (O'Malley)
        Debug.Print strFilter ' see what we built, Ctrl+g
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        ' what should happen here?
        ' maybe just switch off the filter ...
        Me.FilterOn = False
    End If
End Sub

Answer 2

I got it to work using this: .Filter = "LastName Like """ & filterval & """"

Need those annoying String Identifiers even for strings sometimes.

Okay, To get the form to open with no records and then pull up just the records you (or the user) specifies is easiest with a bit of re-work. (I'd recommend you working with a copy and not your original) 1:On your Continuous Form, remove the Recordsource; we're going to use Late Binding (Kinda) 2:Then delete the code under the txtFilter box, then delete the box itself. 3:Add a comboBox with something like this as the recordsource: SELECT DISTINCT myTable.LastName FROM myTable ORDER BY myTable.LastName; (This will get you a unique list of last names so knowing how to spell the name will not be necessary, plus it assures at least one match) 4:In the After Update event of that combobox, add code like this:

Dim strSource As String
strSource = "SELECT mt.IntakeNumber, mt.ClientNumber, " & _
    "mt.LastName, mt.FirstName, mt.ConsultationDate " & _
    " FROM myTable mt " & _
    "WHERE (mt.LastName)= '" & Me.cboFilter.Value & "'"

Me.RecordSource = strSource
Me.Requery

Obviously you'll need to change the table and field names as necessary, but hopefully you get the idea.