Results 1 to 11 of 11
  1. #1
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62

    Multi-Field Text Search

    Good morning, all,

    First, I want to say thank you to everyone that's been helping me over the past couple weeks! I'm learning a lot!

    My question today is a multi-value search.



    I have a split form (fields and individual record at the top, all data in datasheet view on the bottom). I have multiple fields I would like to search on, so that a user may find a particular person based on a number of criteria.

    Here is the VBA that a colleague helped me with that works PERFECTLY right now, and I'd like to add just a *bit* more functionality to it by allowing it to search on more than just the one field.

    Code:
    Private Sub txtSearch_Change()
    Dim strFilter As String On Error Resume Next If Me.txtSearch.Text <> "" Then
    strFilter = "[Last_Name] Like '*" & Me.txtSearch.Text & "*'" Me.Filter = strFilter Me.FilterOn = True
    Else
    Me.Filter = "" Me.FilterOn = False
    End If With Me.txtSearch
    .SetFocus .SelStart = Len(Me.txtSearch.Text)
    End With
    End Sub
    Hopefully there's no typos above, as I'm copying from another screen, but that's my code. Every time I type a letter, the search requeries and supplies only the information that meets that search criteria. What I would like it to do is filter even if it was a First_Name field or the SSN field, etc. Can anyone supply what little bit of code I'd need to make it work?

    I tried doing an Or statement like this, but no dice:
    Code:
    ...
    strFilter = "[Last_Name] Like '*" & Me.txtSearch.Text & "*'" _
    OR "[First_Name] Like '*" & Me.txtSearch.Text & "*'"
    ...
    Can anyone help?

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont put the code in txtSearch_CHANGE()
    it goes in
    _AFTERUPDATE() event

  3. #3
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by ranman256 View Post
    dont put the code in txtSearch_CHANGE()
    it goes in
    _AFTERUPDATE() event
    If I do that, it's no longer a dynamic search that works as the user types information in. It will only edit the search results if I navigate away from that field. Am I missing something that allows a requery after each letter typed for an AfterUpdate() event?

  4. #4
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    I think, to be clear, I want to do this search without having an actual search button. It's just the text field that updates as the user types in search criteria.

  5. #5
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    This post was deleted and put in comment block below.
    Last edited by Ramtrap; 10-18-2017 at 09:29 AM. Reason: Inserted code tags below

  6. #6
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    All, I was provided some help, but now I'm having an issue whereby if there's a value not in my form, it's crashing the DB. For example, if I search my last name (Dutcher), I come up. If I search my first name (William), I'm in the list of people. If I put in my SSN, I show up. But if I put in, for example, "Dutcher " (with a space at the end), the form shuts down. Any ideas how to fix this? Can anyone spot what the issue is?

    Code:
    'This code works great, but if I put in a space character, it crashes the DB
    Private Sub txtSearch_Change()
        Dim strFilter As String
        Dim sSearch As String
    
        If Me.txtSearch.Text <> "" Then
            sSearch = "'*" & Replace(Me.txtSearch.Text"'", "''") & "*'"
            strFilter2 = "[Last_Name] Like " & sSearch & " OR  [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
        With Me.txtSearch
            .SetFocus
            .SelStart = Len(Me.txtSearch.Text)
        End With
    End Sub

    Code:
    'This code is what I have that will reset the textbox to blank and requery, giving me all the people in the DB
    Private Sub txtSearch_Click()
        Me.txtSearch.Text = ""
        Me.Requery
        With Me.txtSearch
            .SetFocus
            .SelStart = Len(Me.txtSearch.Text)
        End With
    End Sub

    *****I was suggested the following additions to above, but crashes even harder*****

    Code:
    'This code works great, but if I put in a space character, it crashes the DB
    Private Sub txtSearch_Change()
    Me.txtSearch.Text = Trim(Me.txtSearch.Text)
        Dim strFilter As String
        Dim sSearch As String
    
        If Me.txtSearch.Text <> "" Then
            sSearch = "'*" & Replace(Me.txtSearch.Text"'", "''") & "*'"
            strFilter2 = "[Last_Name] Like " & sSearch & " OR  [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
            Me.Filter = strFilter
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
        With Me.txtSearch
            .SetFocus
    .SelLength = 0
            .SelStart = Len(Me.txtSearch.Text)
        End With
    End Sub

    Code:
    'This code is what I have that will reset the textbox to blank and requery, giving me all the people in the DB
    Private Sub txtSearch_Click()
        Me.txtSearch.Text = ""
        Me.Requery
        With Me.txtSearch
            .SetFocus
            .SelStart = Len(Me.txtSearch.Text)
        End With
    End Sub
    Any help is very appreciated!!!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't see how code can work at all. Missing a comma in the Replace function:

    sSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"

    Here is Allen Browne's Find As You Type http://allenbrowne.com/AppFindAsUTypeCode.html

    Notice it does not use the Text property. I have never used Text for anything, only Value.

    Value is the default property for data controls in VBA. I think Text is default for VB.net and maybe VB6.
    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.

  8. #8
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    That was a typo, unfortunately (I had to handjam the VBA from one computer to another).
    The actual copy does have that comma in the appropriate spot.

    I will definitely take a look at the allenbrowne article, thank you! And I've seen others mention that the .Text property should almost never be used. I'm a new-to-Access person and was copying off a colleague, and I haven't had the chance to ask why .Text instead of .Value. From what I've ready, it should all still work if I just take off the dot-notation for that, altogether, since the default is the .Value, anyway--right? (i.e. instead of replacing A with B, I can do C, and should be fine):

    A: Me.txtSearch.Text
    B: Me.txtSearch.Value
    C: Me.txtSearch

    Other than that, the only thing I can figure is that it's not returning the focus properly. I keep getting a 2185 error, and it's encountered like this:
    Let's use Mike Smith, SSN 123456789 as an example:
    In the textbox, I search Mike, he pops up
    I search Smith, he pops up
    I search 5678, he pops up
    All that's great. But, if I type Smiths, or 7890, or a value that is NOT identified, it returns 0 results (as predicted), however, when I go to clear out the textbox, or if I click back in there, I get:

    Run-time error '2185':
    You can't reference a property or method for a control unless the control has the focus.

    Totally makes me think it's the .Text issue, but maybe I'm doing something wrong when trying to re-attribute everything correctly, either with a .Value (B, above) or as seen in example C, above.

    Thank you again for the article; will read through it (several times!) and update tomorrow. Thank you!

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, first step is to remove all the .Text references. Then step debug. Set a breakpoint in each procedure.
    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.

  10. #10
    Ramtrap is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2016
    Posts
    62
    Quote Originally Posted by June7 View Post
    Yes, first step is to remove all the .Text references. Then step debug. Set a breakpoint in each procedure.
    It was strange, but when I did that, it kept trying to put in all my letters backwards. Example: If I searched "Smith", it would enter it as "htims". I'm sure it had something to do with the on click event, but I had a friend look at it and I think she cracked it! It looks like this (I've added a comment to what the new line(s) of code is/are):

    First, the textbox click event whereby when you click the box, it clears the text and resets the search (no need for a reset button)
    Code:
    Private Sub txtSearch_Click()
    Me.txtSearch.SetFocus 'new line of code
    Me.txtSearch.Text = ""
    Me.Requery
    With Me.txtSearch
    .SetFocus
    .SelStart
    End With
    End Sub
    This is the actual search, that will search multiple fields
    Code:
    Private Sub txtSearch_Change()
    Dim strFilter As String
    Dim sSearch As String
    On Error Resume Next
    
    If Me.txtSearch.Text <> "" Then
    sSearch = "'*" & Replace(Me.txtSearch.Text, "'", "''") & "*'"
    strFilter = "[Last_Name] Like " & sSearch & " OR [First_Name] Like " & sSearch & " OR [SSN] Like " & sSearch
    Me.Filter = strFilter
    Me.FilterOn = True
    Else
    Me.Filter = ""
    Me.FilterOn = False
    End If
    
    If Me.Recordset.RecordCount = 0 Then 'new line of code
    Me.Filter = "" 'new line of code
    Me.FilterOn = False 'new line of code
    Me.txtSearch.SetFocus 'new line of code
    Me.txtSearch.Text = "" 'new line of code
    Exit Sub 'new line of code
    End If 'new line of code
    
    With Me.txtSearch
    .SetFocus
    .SelStart = Len(Me.txtSearch.Text)
    End With
    End Sub
    This seems to be working great. And while I'm considering this issue complete, I do have a question, if you could help me identify, why were the letters going in backwards when I replaced the .Text with a .Value?

    Thank you all very much for your help!

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Took a closer look at Allen's code and I do find use of the .Text property!! As well as .Value. So, my apologies, this concept does rely on Text property.

    Unfortunately I can't get your code to work. Compile error on the .SelStart in the Click event so I comment it. The Change procedure does not filter form on each key input, although the Filter toggle is activated, and when I complete input and tab or enter get error message "not a valid value" and I know it is valid.

    I don't have the interest to continuing exploring these procedures so will just say glad you got it working.
    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. Help With Multi Field Search Form
    By icaines9517 in forum Forms
    Replies: 6
    Last Post: 08-02-2016, 10:01 AM
  2. Multi-field search form
    By rebeldolphin69 in forum Forms
    Replies: 2
    Last Post: 06-15-2016, 05:28 AM
  3. Multi field search forms
    By Rogue in forum Forms
    Replies: 10
    Last Post: 05-14-2013, 09:40 AM
  4. Multi-field Search
    By bubba61 in forum Queries
    Replies: 20
    Last Post: 04-16-2013, 10:21 AM
  5. Multi-value Text Field Search
    By billfold in forum Queries
    Replies: 3
    Last Post: 04-30-2012, 03:43 PM

Tags for this Thread

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