Search as you Type

One of the cooler user interface pieces I’ve seen in software that handles large data sets is a simple search box.  When using Apple iTunes, I particularly like that the search box dynamically updates the list of results for each key press, allowing you to craft your search on the fly.  With a little crafty coding you can do this in an Access form quite easily.

You’ll need at minimum a Combo Box control, and for larger lists a combination of a text box and a list box.  With those, you’ll use a particular event to update the data set.

On Key Up event

Private Sub Search_KeyUp(KeyCode As Integer, Shift As Integer)

The Key Up event passes 2 parameters to your code, the “KeyCode” and “Shift”.  The KeyCode is an integer representing what was pressed on the keyboard, and shift indicates if the shift key was depressed too.

All data controls have this event, and we’ll use that to determine what key was pressed, then use a property to update the list of data that shows up for the user.

First, we have to know what key was pressed.  We don’t want to update data when a useless key is pressed, like an F key etc. so we have to limit the range of keys.  If you don’t know what KeyCode is actually hit, simply add a debug line to the sub to spit it out.

Debug.Print KeyCode

That will display the codes in the immediate pane as you hit keys, allowing you to determine the code.  For most purposes you’ll use the alpha keys, and a backspace.  With some If statements you can kill your sub if you don’t get a valid KeyCode.

Private Sub Search_KeyUp(KeyCode As Integer, Shift As Integer)
‘dynamically update list, alpha characters or backspace only
If KeyCode = 8 Then GoTo Filt
If KeyCode < 65 Or KeyCode > 90 Then Exit Sub
Filt:

From there, after the Filt: label, you’ll add your code to update the RowSource of your output control, either a ComboBox, ListBox, or even a sub form.  First, since the control isn’t actually updated yet, you need to determine the text that is being typed.  That can be determined by the control’s Text property.

Dim EntryTxt, SelectSQL, OrderBy, where
EntryTxt = Left(Me.Search.Text, Len(Me.Search.Text) – Me.Search.SelLength)
If Len(EntryTxt) = 0 Then
EntryTxt = Null
Me.Search = “Search”
End If

Here we setup the variables we’ll use, and used some string functions to get the left side of the text property.  This is useful when you’re using a combo box, since the box will actually fill with a matching entry and make your search invalid, you only want what the user has typed.

Note that a length check = 0 means there’s nothing typed at all, at which point the control value is reset back to the default “Search”.

A quick check of the EntryTxt variable can determine if there’s anything to filter, or if you need to open your query back up.

If IsNull(entrytext) Then
‘show all
where = “”
Else
‘Apply filtered SQL
where = ” WHERE [SearchField] Like ” & Chr$(34) & “*” & EntryTxt & “*” & Chr$(34)
End If

Here we use our where variable to craft a SQL WHERE statement to add to our query, leaving it empty if there is nothing to find.  Also note the Chr$(34) and asterisk text being added, that puts our text in double quotes and adds the wildcard on either side, allowing us to find the search string anywhere in our field.

The last thing to do is to build the rest of the SQL statement, and apply it to our control.

‘assemble SQL and apply to control
SelectSQL = “SELECT [ListData] FROM [TableSource] ”
OrderBy = “ORDER BY [ListData];”
Me.TargetControl.RowSource = SelectSQL & where & OrderBy

Here the SQL statement is built with the standard SELECT and FROM portions, followed by the WHERE statement we built above, and finished with an ORDER BY to put our list in order.

By simply setting the controls RowSource to the SQL statement we built, Access automatically re-queries and updates the display.  For a sub form instead of a combo or list box, simply change that to RecordSource instead.

Additional Conveniences

For a Combo Box, this is going to work best if the user can actually see the results of the search by dropping down the list.  This can be done in your search code by adding

Me.TargetControl.DropDown

For a text box driven search update, it’s quite convenient to skip the whole selecting text business and allow a user to simply click the control and start typing.  The OnClick event can take care of that part.

Private Sub Search_Click()
‘Select current text
Me.Search.SelStart = 0
Me.Search.SelLength = Len(Me.Search)
End Sub

That simple code selects everything in the text box as soon as you click, then you simply start typing.

Limitations

Once this code is added, Access will essentially be running a query for each key press on a control.  This can quickly become annoying if your search query is chugging through a lot of data and is slow.  So the rule of thumb here is to only do this for fast sets of records, like a local Access table in the same file you’re front end is running from or a reasonably small data set on a fast network.

After Update Event

If your data simply isn’t fast, you’ll want to change the search to happen after the user completes an entry, using the AfterUpdate event.

Some of the above code is no longer necessary, since the Text property isn’t needed anymore, instead we’ll simply apply the filter using the control’s new value.

Private Sub Search_AfterUpdate()
Dim SelectSQL, OrderBy, where
If IsNull(Me.Search) Then
‘Nothing to search, reset to default
Me.Search = “Search”
Where = “”
Else
‘Apply filtered SQL
where = ” WHERE [SearchField] Like ” & Chr$(34) & “*” & Me.Search & “*” & Chr$(34)
End If
‘assemble SQL and apply to control
SelectSQL = “SELECT [ListData] FROM [TableSource] ”
OrderBy = “ORDER BY [ListData];”
Me.TargetControl.RowSource = SelectSQL & where & OrderBy
End Sub

With the above code, your search is executed once the user tabs, enters, or clicks their way out of you search control.