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?

HK1 · Accepted Answer · 2013-11-14 20:24:57Z

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.

edited Nov 14, 2013 at 20:24

HK1

12.3k15 gold badges69 silver badges99 bronze badges

answered Nov 13, 2013 at 20:51

craig.white

4272 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HK1 Over a year ago

You probably need asterisks or stars yet too: Filter = "LastName Like '*" & filterval & "*'"

craig.white Over a year ago

HK1's answer is better- that will allow for any matches, not just exact.

CodeMed Over a year ago

@craig.white +1 Thank you. Now how do I get it so that the continuous form comes up empty when the form is loaded? There will be tens of thousands of records in the underlying data table, and I only want the user to see the subsets of records that fit criteria entered into the txtFilter text box. Currently, the entire contents of the table are showing up in results when the form loads. But the search filter works when the user enters something in txtFilter

CodeMed Over a year ago

@craig.white Thank you. I am going to mark this as the answer. However, I am just using HK1's edit of your suggestion because I want to keep the code simple for easier maintainability over time.

craig.white Over a year ago

No worries, glad we could help.

HansUp · Accepted Answer · 2013-11-13 21:57:12Z

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

Collectives™ on Stack Overflow

filter continuous form using textbox

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related