When a user needs to find an existing record or value by typing something in a box, it is well recognized that a "typeahead" or incremental search is a powerful and user-friendly solution.
Of course MS Access has featured combo boxes (drop-down lists) with “auto expand” capabilities since time began; here’s an example where I’ve typed “pen” into the control and it has automatically selected the first matching value:
(The list can be opened by clicking on the down arrow, or by hitting F4 - one of the many keyboard shortcuts that nobody knows - or in code with the dropdown method).
This is fine for most situations, but there are cases where the auto-expanding combo box falls down:
- Very long lists (like many hundreds or thousands of rows in the drop-down): firstly this can be a performance problem, putting significant load on network and database whenever a user uses the control, secondly very long lists are not user-friendly, and worst of all the auto expand can just stop working or give strange results; in a system I was working on with SQL Server data storage, one combo with >4000 rows would frequently fail to find values in the list when the user started typing.
- Lists where the user may not know the starting text (e.g. trying to find Southampton in a list that includes it as City of Southampton).
- Situations where we want to give the user an easy way of selecting multiple items. Not the main focus of this post, but it does provide much of a solution if you need it.
This post describes a way of providing a nice user experience in these situations.
In the first simple example implementation the user is looking for the record for one of our clients based on knowing the unusual first name of a member of staff there. In this case the dropdown opens when the user has typed “bri”, showing lots of rows of people called “Bridges” etc, then when the user adds a “t” the list looks like this:
On selecting a row, the form updates to show the full record for the selected client.
An implementation like this starts with an unbound combo box, making sure its Auto Expand property is set to No. You will ultimately need to clear the Row Source, but it makes design easier to start with a row source for the dropdown that closely resembles the final version. Typically this might be a table with a primary key value that will be hidden (e.g. an ID), and a visible descriptive field (e.g. a Name). Other properties can be set to suit requirements, so perhaps (following this simple example) Column Count = 2, Column Widths “0cm;10 cm” and List Width “10cm”. Keep Limit To List set to “Yes”. When you have a combo box that is behaving nicely with a Row Source, switch back to design view and remove the Row Source. Leave the Row Source Type set to “Table/Query”.
Now we need to add some code to make it work. First we add a handler for the On Key Down event, like this:
Private Sub Combo0_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 8 Or KeyCode = 17 Or KeyCode = 32 Or KeyCode = 46 Or KeyCode > 47 Then
Me.TimerInterval = 300
End If
End Sub
This will make the form do something 300 milliseconds later if the pressed key is one we like, i.e. one that changes the contents of the control in a way that should affect the search. 8 is Backspace, 32 is Spacebar, 46 is Del so we need them. 17 is CTRL and can arrive if the user pastes with CTRL+V or cuts with CTRL+X. Most keycodes over 47 are characters.
See the bottom of this post for an extension to make this handle mouse and ribbon-based paste operations.
Why 300 milliseconds? Make it whatever you like, but I found that is a reasonable amount of time to wait before executing the search if a user has clicked into the control and started typing. Any longer and the user feels like they are having to hang around, any faster and it searches before they’ve had time to enter the characters they are confident about. Note that if the user presses more than one key, for example by typing “Rob”, each will set the form’s timer interval, which re-starts the timer and so defers the resulting action until 300 milliseconds after the last key press. So the 300 really defines the length of pause after hitting any one key.
At this point the observant will be wondering why we are using the Key Down event and not the Key Up event. Well I started off by using Key Up event, but I found that it simply does not fire if the user selects some of the entered text and removes it with the Del key; however using the Key Down event does need extra care, because when it fires the contents of the control have not yet changed to reflect the key press.
Now we need some code on the form’s Timer event.
Private Sub Form_Timer()
TimerInterval = 0
SearchByName Combo0.Text
End Sub
In this case we first switch off the timer, then pass the text that is now in the control into a method that will execute the search. Because we are harvesting the search value now, and not in the Key Down event, we get the value after all key presses have completed. Note that we do have to use the .Text property, and not the .Value property (which is the default if we just pass me!Combo0 as a string).
Now we need to process the search. In this simple example we have a private method in the form’s code-behind:
Private Sub SearchByName(SearchString As String)
Dim qryDef As QueryDef
If Len(SearchString) >= 2 Then
Set qryDef = CurrentDb.QueryDefs("qryNameSearch")
qryDef.Parameters("NameSearch") = SearchString
Set Combo0.Recordset = qryDef.OpenRecordset
Set qryDef = Nothing
If Combo0.Recordset.RecordCount > 0 Then Combo0.Dropdown
Else
Set Combo0.Recordset = Nothing
End If
End Sub
This relies on a saved parameter query, which in the example above might be something like:
PARAMETERS NameSearch Text ( 255 );
SELECT Staff.ID, Staff.LastName
FROM Staff
WHERE Staff.LastName Like [NameSearch] & '*';
or if you want to find matches when the user enters characters from the middle of the name as well:
WHERE Staff.LastName Like '*' & [NameSearch] & '*';
And of course the query can be adapted to return more advanced expressions, as in the screenshot above, and executing the search however best suits the data. And the SQL could be inline if you don’t want a saved query. Or it could be a SQL Server stored procedure, or even a called to a web service that is converted to a recordset on return, etc etc.
If we are in a data entry form where the value always exists in the list, that’s it, job done. If the value may not exist (see the example below) we need to give the user a way of entering data. If the control is being used to navigate to a record, then we need to do something when the user selects a value using a handler on the control’s After Update event, such as:
Private Sub Combo0_AfterUpdate()
If Combo0 > "" Then
… do something …
End If
End Sub
Moving on from this simple example, it’s also possible to show the list of values in a separate control that is made visible when a search finds rows. In the example below (from home!) I’ve used a list box, setting it’s record source in similar fashion to the above combo box. I prefer the look and feel of this approach, and it would also allow the user to select more than one row which could be useful in some situations. The example below is a form used for taking entries to fell races. A large proportion of runners have entered before, so it saves a lot of time to fetch and confirm their previous details rather than starting from scratch. On the other hand, lots of people share the same names, particularly in South Wales(!), so we need to see quite a lot of information to confirm a match.
When the user enters two or more characters of the surname, a list appears. On selecting a row, the remaining details are automatically filled in (in the parts of the form hidden by the list in this image). If a match is not found, the user has a simple data entry form to enter a new competitor.
In this case our search method looks like this, to show or hide the list:
If Len(RunnerSearch.Text) >= 2 Then
Set qryDef = CurrentDb.QueryDefs("RegistrationFormNameSearchQry")
qryDef.Parameters("NameSearch") = RunnerSearch.Text
Set lstRunnerPicker.Recordset = qryDef.OpenRecordset
Set qryDef = Nothing
lstRunnerPicker.Height = 567 * 4.9 ' 567 twips per cm
lstRunnerPicker_Label.Height 567 * 4.9
lstRunnerPicker.Visible = True
Else
lstRunnerPicker.Visible = False
End If
I hope someone finds this helpful. I’m sure these approaches can be refined and improved in all kinds of ways, so feedback welcome.
UPDATE @ 4th December 2016
When checking out comments from Rick Wiker below, I noticed my code above doesn't detect right-click > Paste, or Paste using the ribbon tool. I find this is easily solved using the On Change event, eg:
Private Sub RunnerSearch_Change()
If Me.TimerInterval = 0 Then
Me.TimerInterval = 300
End If
End Sub
The condition is probably not needed - I just used it to be specific about when this was happening (i.e. when the KeyDown event has not already set the timer). This seems to work for a text box search control for both mouse and ribbon-based pasting. I've not yet tested this approach with a combo, and haven't spotted any negative side effects. Feel free to comment!