Dynamically search multiple fields (1 Viewer)

Status
Not open for further replies.

John Big Booty

AWF VIP
Local time
Tomorrow, 01:36
Joined
Aug 29, 2005
Messages
8,262
This DB is a modification of a search tool that was posted in these forums several years ago.

The tool allows the user to type text into a text box (the text is matched against any part of a number of fields) with the results being shown dynamically in a list box as the user types. Essentially the List box initially shows all records, this list is gradually reduced as the text is typed and the matching results narrow. The user can then double click on any record to show the details in a pop up form.

How it works;

  1. Create Yourself a new unbound form; for the sake of the exercise we'll call it FRM_SearchMulti
  2. On that form create two unbound text boxes, call the first one SearchFor, and the second SrchText
  3. Set SrchText's Visible property to No
  4. Save your form.
  5. Now create a query let's call it QRY_SearchAll in it put the tables you want to search.
  6. Select all the field you want to search along with any other you want to appear in your search results.
  7. In the criteria for the first of the fields you wan to search put the following; Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
  8. Now for the next field you wish to search go down one row in the criteria (Or) and put the same code that we used in step 7.
  9. Repeat this for each field you want to search, going down one row in the criteria for each of the fields. So that you are searching Field1 OR Field2 OR Field3 etc.
  10. Save and close your query and go back to our form FRM_SearchMulti, now put a list box on the form use your Query QRY_SearchAll as the source for the List box when promoted by the wizard, and follow the bouncing ball from there, call this List Box SearchResults.
  11. Save the form.
  12. Now in the On Change event of text box SearchFor put the following code;
    Code:
    [COLOR="Green"]'Create a string (text) variable[/COLOR]
        Dim vSearchString As String
    
    [COLOR="Green"]'Populate the string variable with the text entered in the Text Box SearchFor[/COLOR]
        vSearchString = SearchFor.Text
    
    [COLOR="Green"]'Pass the value contained in the string variable to the hidden text box SrchText,
    'that is used as the sear4ch criteria for the Query QRY_SearchAll[/COLOR]
        SrchText.Value = vSearchString
    
    [COLOR="Green"]'Requery the List Box to show the latest results for the text entered in Text Box SearchFor[/COLOR]
        Me.SearchResults.Requery
    
    [COLOR="Green"]
    'Tests for a trailing space and exits the sub routine at this point
    'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor[/COLOR]
        If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
            Exit Sub
        End If
    
    [COLOR="Green"]'Set the focus on the first item in the list box[/COLOR]
        Me.SearchResults = Me.SearchResults.ItemData(1)
        Me.SearchResults.SetFocus
    [COLOR="Green"]
    'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box[/COLOR]
        DoCmd.Requery
    [COLOR="Green"]
    'Returns the cursor to the the end of the text in Text Box SearchFor[/COLOR]
        Me.SearchFor.SetFocus
    
        If Not IsNull(Len(Me.SearchFor)) Then
            Me.SearchFor.SelStart = Len(Me.SearchFor)
        End If
  13. Save the form and put it into form view, start typing into the text box SearchFor, as you type the items in the List box should start reducing as the match narrows.
 

Attachments

Last edited:
The following code has an improved procedure for dealing with trailing spaces in the search string;
Code:
[COLOR="DarkGreen"]'Create a string (text) variable[/COLOR]
    Dim vSearchString As String

[COLOR="DarkGreen"]'Populate the string variable with the text entered in the Text Box SearchFor[/COLOR]
    vSearchString = SearchFor.Text

[COLOR="DarkGreen"]'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll[/COLOR]
    SrchText.Value = vSearchString

[COLOR="DarkGreen"]'Requery the List Box to show the latest results for the text entered in Text Box SearchFor[/COLOR]
    Me.SearchResults.Requery

[COLOR="DarkGreen"]
'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor[/COLOR]
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        [COLOR="DarkGreen"]'Set the focus on the first item in the list box[/COLOR]
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        [COLOR="DarkGreen"]'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box[/COLOR]
            DoCmd.Requery
        [COLOR="DarkGreen"]'Returns the cursor to the the end of the text in Text Box SearchFor, 
        'and restores trailing space lost when focus is shifted to the list box[/COLOR]
            Me.SearchFor = vSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
            
        Exit Sub
    End If

[COLOR="DarkGreen"]'Set the focus on the first item in the list box[/COLOR]
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus

[COLOR="DarkGreen"]'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box[/COLOR]
    DoCmd.Requery

[COLOR="DarkGreen"]'Returns the cursor to the the end of the text in Text Box SearchFor[/COLOR]
    Me.SearchFor.SetFocus

    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If
Note: Not implemented in the sample DB
 
Status
Not open for further replies.

Users who are viewing this thread

Back
Top Bottom