John Big Booty
AWF VIP
- Local time
- Today, 13:32
- 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;
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;
- Create Yourself a new unbound form; for the sake of the exercise we'll call it FRM_SearchMulti
- On that form create two unbound text boxes, call the first one SearchFor, and the second SrchText
- Set SrchText's Visible property to No
- Save your form.
- Now create a query let's call it QRY_SearchAll in it put the tables you want to search.
- Select all the field you want to search along with any other you want to appear in your search results.
- In the criteria for the first of the fields you wan to search put the following; Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
- 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.
- 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.
- 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.
- Save the form.
- 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 - 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.
Last edited:
