Results 1 to 10 of 10
  1. #1
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67

    Using a form's filter property to filter records containing specific text strings.

    I have a form F_Lists which has as its recordsource the table Lists. F_Lists is tabular and it has a textbox control named Search. I want the user to be able to enter text into Search and then have the form filter to only show those records for which the field L_Name contains the text entered into Search. I have the following code



    Private Sub Search_AfterUpdate()
    Dim S$
    S$ = "(Me.L_Name) Like *" & Me.Search.Text & "*"
    MsgBox "S$ = " & S$
    Me.FilterOn = True
    End Sub

    The first 3 records shown by the form include the text boo in the field L_Name, and the others don't

    When I go into Search and then type boo and then press the Enter key, the messagebox shown the desired filter string, but nothing happens to the records being displayed. They all continue to be displayed, including the ones which don't contain "boo" in the field L_Name.

    What am I doing wrong?

    Thank you in advance

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You didn't set the filter property. Try:
    Code:
    Private Sub Search_AfterUpdate()
     Dim S 
       S = "(Me.L_Name) Like *" & Me.Search.Text & "*"
    '     MsgBox "S = " & S
    
       Me.Filter = S
       Me.FilterOn = True
    End Sub
    Or you could use
    Code:
    Private Sub Search_AfterUpdate()
        Me.Filter = "(Me.L_Name) Like *" & Me.Search.Text & "*"
        Me.FilterOn = True
    End Sub

  3. #3
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    You're right Steve. Thank you for your prompt reply. I originally had that statement in and I accidently deleted it. Here is the code now

    Private Sub Search_AfterUpdate()
    Dim S$
    S$ = "Me.L_Name Like ""*" & Me.Search.Text & "*"""
    Me.Filter = S$
    MsgBox "S$ = " & S$
    Me.FilterOn = True
    End Sub


    Now what happens is this. I go into the search box and type boo and then hit return. Then a dialogue window appears. The title of the dialog window is Me.L_Name

    The dialog asks the question "Enter parameter". If I just hit return then all of the records disappear. If instead I type boo and hit return, then all of the records remain, including the ones that don't contain boo in the field L_Name.

    I have no idea what's happening.

    I realize that one work-around would be to have the record source be a query rather than the table, and I could adjust the Query's SQL based upon what was typed in the search box and then do Me.Requery.

    But I would prefer to use the filter property if it is possible.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The dialog asks the question "Enter parameter".
    Without seeing your dB, it sounds like there is a query that is looking for a parameter. Check the record source of the form.

    I realize that one work-around would be to have the record source be a query rather than the table
    I always have a query as the record source of a form/report.

    Care to post the dB for analysis?

    Edit....
    Oops...
    In this line
    Code:
    S = "(Me.L_Name) Like *" & Me.Search.Text & "*"
    delete the "Me.".
    Use JUST the field name.

    Code:
    S = "(L_Name) Like *" & Me.Search.Text & "*"

  5. #5
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    Steve .....

    That works!!!!!!!

    I never would have guessed that in a million years!

    How the &#*##$@! did you know that?!?

    -regards

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Even though I missed it the first few times I read your post, "Me." is shorthand to reference a form/report.
    But a form has a record source consisting of fields. When you set a filter, you are filtering on FIELD values in the form. Therefore, you don't (can't ) use "Me.", you use just the field name.

    What the field is filtered on can be from the same form
    Code:
    S = "(L_Name) Like *" & Me.Search & "*"
    or from a different form
    Code:
    S = "(L_Name) Like *" & Forms!AnotherForm.Search & "*"

    ======================
    Notes:

    You don't have to/(shouldn't) type ".Text" . The default property is ".Value". You don't have to type that either (although it doesn't hurt). Since it is the default property, Access knows what to do. Also saves on typing. See help on the differences between ".Text" and ".Value".

    In Basic, there are special characters after a variable that are used to declare the variable type.
    In VBA, this syntax have been depreciated.
    The correct syntax to declare a string variable is:
    Code:
    Dim S as String

  7. #7
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    Thanks for your help Steve. I've decided to explore another approach. Now my tabular form F_Lists has a records source which is a query, ListQuery.
    At this point the data in the underlying table is meaningless - its just experimental. I have added the string "boo" to the field L_Name in a few of the records in table Lists and have added the string "noozger" to a few others. When I open the form, the query from which is gets its recordset has been set to look for "boo" in the L_Name field.
    I have placed in the header section of the form a button temp which has the following code:

    Private Sub Temp_Click()
    Dim S$, QDF As QueryDef
    Init
    Set QDF = db.QueryDefs!ListQuery
    S$ = "SELECT Lists.L_Num, Lists.L_Name, Lists.L_Chosen " & "FROM Lists "
    S$ = S$ & "Where (((Lists.L_Name) Like ""*noozger*""))" & " ORDER BY Lists.L_Name;"
    QDF.SQL = S$
    Forms!F_Lists.Requery
    End Sub

    My hope was that when I press the button temp, the records show in the detail part of the form will switch to the ones for which field L_Name contains "noozger". Instead, nothing happens. But when I close the form and open it again, the "noozger" records display. What I want if for the "noozger" records to display right after I press temp.

    Any suggestions?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are actually changing the SQL of a saved query (ListQuery) when you execute that code. The next time the form is opened, records with "noozger" will be displayed, not "Boo". If that is OK, then you are golden.

    I dislike changing the SQL of saved queries. You might as well just change the record source property of the form.
    But here are the code changes I would try:
    Code:
    Private Sub Temp_Click()
        Dim db As DAO.Database
        Dim QDF As QueryDef
        Dim S As String
    
        Set db = CurrentDb
    
        Init     '?????
    
        Set QDF = db.QueryDefs!ListQuery
        S = "SELECT Lists.L_Num, Lists.L_Name, Lists.L_Chosen " & "FROM Lists "
        S = S & "Where (((Lists.L_Name) Like ""*noozger*""))" & " ORDER BY Lists.L_Name;"
        QDF.SQL = S
    
        Me.Requery
        '    Forms!F_Lists.Requery
    
        Set db = Nothing
    End Sub


    You could also set the form record source (or the query "ListQuery") to
    Code:
    SELECT Lists.L_Num, Lists.L_Name, Lists.L_Chosen 
    FROM Lists 
    ORDER BY Lists.L_Name;
    Then set a filter in the click event of the button.
    Code:
    Private Sub Temp_Click()
        Me.Filter =  Lists.L_Name Like ""*noozger*""
        Me.FilterOn = True
    End Sub

  9. #9
    GeorgeJ is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    67
    Steve, Thank you for your help.

    From the results I have been getting, one thing seems to be true: You can change the SQL string of a select Query, but I haven't found a way to run the changed query from code inside a form. The Requery command doesn't do it. If you leave the form and reload it, you do get results corresponding to the changed query. I am wondering - is there any way to run the changed query without closing and reopening the form? If not, I guess I will go back to plan A, which is to use filters.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why aversion to setting Filter property?

    If you have code that modifies QueryDef and Requery of form (I would not do this and never tried) doesn't work, then possible option is to set the form RecordSource property with the SQL statement instead of reference to query object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. me.filter using 2 strings - problem with apostrophe.
    By wackywoo105 in forum Programming
    Replies: 3
    Last Post: 05-07-2014, 03:41 PM
  2. Error referencing filter property of a sub-form container
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 03-23-2014, 01:14 PM
  3. Replies: 1
    Last Post: 08-01-2012, 03:56 PM
  4. Replies: 28
    Last Post: 03-08-2012, 06:47 PM
  5. Filter specific records on sub form
    By foxtet in forum Forms
    Replies: 5
    Last Post: 06-05-2011, 12:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums