Martin Green's Mailbag
|   Home   |   Excel   |   Access   |   Word   |   Outlook   |   VBA   |   eBooks   |   Handouts   |   Site Index   |   RSS   |

Back to the Mailbag

Download the Sample File

Support this site!

Please make a small donation to help support this site.

Thank you.

A Progressive Search Tool for Access

Published: 28 November 2018
Author: Martin Green
Screenshots: Access 2016, Windows 10
For Access Versions: 2007, 2010, 2013, 2016

What Does it Do?

"I need a search tool where I can enter a letter to get a list of items, then refine the list by entering another letter and so on, with the list changing by itself as the letters are entered...".

In my Access Forms Masterclass #4 I showed how to build a Pop-Up Search Tool in which the user can enter a letter or string of letters into a text box and choose the search criteria. On the click of a button a list box displays a list of records matching the text and criteria they specified. In that example several things have to be taken into account before the Row Source property of the list box is updated to reveal the result of the search, and this happens on the Click event of a command button.

Whilst my correspondent's request sounds quite complicated, it's much easier to achieve. The Row Source of the list box has to be updated each time the user adds another letter to their search string. The trigger for this is the Change event of the search text box. No need for a separate button to initiate the search.

Why Change and not After Update?

I use the Change event rather than After Update because the change event fires every time the content of the search text box changes (i.e. as each letter is typed). The After Update event doesn't fire until the user has finished typing and leaves the text box. I only use the Change event when I have a specific requirement like this one because it would involve Access doing a great deal of unnecessary work when I was only interested in the finished string of text.

How it Works

For an example I made have made use of a list of names. The tool is quite simple. It consists of a Text Box into which the user can type (I named mine txtSearch), a List Box to display the result of the search (I named mine lstStaff), and a Command Button to reset the list to its original state (I called mine cmdReset). Depending on what the search tool is used for, you might also want to add another command button, as shown here (Fig. 1) that performs a task such as displaying the chosen record on a form (mine is called cmdViewSelectedItem)

The screenshot shows how a search would progress (Fig. 1). Starting with the full list the user enters a letter. The list automatically updates to show only those items starting with that letter. When the user adds another letter the list updates again to show only those items starting with that pair of letters, and so on...

As the user adds a letter the search is refined.
Fig. 1 As the user adds a letter the search is refined.

To keep things simple I have only given the option to search for items starting with the chosen search string, and looking in a specific field (here the LastName field). If you need to add more options you can see how to do this in my Access Forms Masterclass #4 A Pop-Up Search Tool.

My list box displays two fields (LastName and FirstName) but it also contains the record's Primary Key field (here StaffID) which is necessary if you want to use the tool to synchronize with a record on a form.

Here's the Code that Makes it Work

Most of the work gets done on the Change event of the search text box (Listing 1).

Listing 1:

Private Sub txtSearch_Change()
On Error Resume Next
Dim strSQL As String
If Len(Me.txtSearch.Text) > 0 Then
        strSQL = "SELECT tblStaff.[StaffID], tblStaff.[LastName], tblStaff.[FirstName] " & _
                 "FROM tblStaff " & _
                 "WHERE tblStaff.[LastName] Like " & Chr(34) & Me.txtSearch.Text & Chr(42) & Chr(34) & " " & _
                 "ORDER BY tblStaff.[LastName], tblStaff.[FirstName];"
With Me.lstStaff
            .RowSource = strSQL
End With
End If
End Sub

After the basic error handler I have declared a variable strSQL to hold the SQL string that defines the Row Source of the list box. The If Statement checks that the text box isn't empty (that is taken care of by the Reset button). The WHERE clause of the SQL statement is constructed using the Text property of the text box (not its Value property as you might assume) and, since special characters are required I have used the Chr() function to create them: Chr(34) = a quote mark " and Chr(42) = an asterisk *. A With Statement then applies the revised SQL to the list box's Row Source property and commands it to Requery, thus rebuilding the list.

To clear the Search text box and reset the list the following code is executed when the user clicks the Reset button (Listing 2).

Listing 2:

Private Sub cmdReset_Click()
On Error Resume Next
Dim strSQL As String
    strSQL = "SELECT tblStaff.[StaffID], tblStaff.[LastName], tblStaff.[FirstName] " & _
        "FROM tblStaff " & _
        "ORDER BY tblStaff.[LastName], tblStaff.[FirstName];"
With Me.lstStaff
        .RowSource = strSQL
        .Value = ""
        .Selected(0) = False
End With
    Me.txtSearch.Value = ""
End Sub

As in Listing 1 a SQL Statement is applied to the Row Source of the list box, this time without a WHERE clause because it needs to show all the records, but in addition the Value property is set to nothing (an empty string denoted by a pair of quote marks with nothing between them) to de-select any existing selection, followed by the instruction to Select(0) to make sure that the list box scrolls to the top after it is reset. Finally, the Search text box is emptied by assigning a zero length string to its Value property.

In my example I am locating the search tool on a data form, so I have added the feature to display the record on that form that the user has chosen from the Search tool's list. Having refined the list the user simply selects an item from the list and clicks the View Selected Item button (Listing 3). To make use of this you must have included the Primary Key field with those displayed by the list box. If, as I have done, you want to hide that field you just set its Column Width to zero. Remember also to make the Primary Key column the Bound Column of the list box. When the user selects an item in a multi-column list the Bound Column is the one which then becomes the Value of the list box (the same applies to combo boxes).

Listing 3:

Private Sub cmdViewSelectedItem_Click()
On Error Resume Next
Dim rst As Object
Set rst = Me.RecordsetClone
    rst.FindFirst "[StaffID] = " & Me.lstStaff.Value
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End Sub

This process makes use of the form's RecordsetClone property, a copy of the forms own recordset that exists in memory and can be manipulated as required. The code uses the value of the Primary Key field of the item chosen by the user to find and bookmark that record in the RecordsetClone. It then synchronizes the clone with the form's own recordset with the result that the form jumps to that particular record (Fig. 2).

Choose an item from the list to display that record.
Fig. 2 Choose an item from the list to display that record.


When you want to carry out a progressive search, refining your search results by adding a letter at a time, it's simply a matter of using your search text box's Change event which fires each time the user types another letter. The only disadvantage might be that there is extra work for Access to carry out, but unless you are working over a slow network or internet connection this should not be a problem.

Download Example Database

You can download a sample database containing the completed exercise used in this tutorial. To download the file right-click the icon or text link below and choose Save target as... and follow the instructions. The database is supplied in a *.zip folder. After downloading you should extract the database file from the zip folder before attempting to use the database. To do this right-click the zip file icon then choose Extract All... and follow the instructions.

Download Masterclass 1 Database [75KB]

^ top


  ©2000-2018 Martin Green All rights reserved