Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from your application?
Naturally, the unnamed search engine applies very advanced technology, using massive pre-indexed tables with complex relationships, and search statistics. This isn't the topic of this article. The search used here is deliberately not optimized and is only fitting for very small tables (let's say below 10'000 records).
Instead, the present article focuses on the user interface. A combo box is used to show the results of the current search, and is updated “on the fly”, while the focus remains in the control. This allows to narrow down the search until the desired information is located. Note that this is different from the built-in auto-complete feature, which works only if the user always types the first letters of the information displayed in the combo.
The specific “while you type” aspect might not be suitable for every application. However, if that option is turned off, what remains is a more classical search box, but using the combo's drop-down section as search result window. This technique is very well received by users, even if they have to press Tab in order to see the result of the search. So, even if that aspect isn't what you are looking for, you might still be interested in the search mechanism and in the attached demo database.
It must be said that the complete solution is rather technical. Several advanced techniques are used, which are not always explained in full. This article was written for Access developers who need to create a search box with a professional look and feel, and not for novice users creating their first criteria form.
The Sample Data
I needed sample data for which an alphabetical sort doesn't really help in finding a specific record. This is true for any search in lengthy descriptions, but I wanted something simple.
I settled on hotel names, because a hotel is almost never know by its full name. For example, everybody in Geneva can help you find the “Hôtel des Bergues” or the “Hôtel du Rhône”, but only professionals will know that they are currently called “Four Seasons Hotel des Bergues Geneva” and “Mandarin Oriental Hotel du Rhone” (in typical Geneva Frenglish!). Even in the simple popular name, the significant word is the last. An efficient search engine should allow the user to type “bergues” or “rhone” and locate the current full name. The second interesting challenge with hotels is that you often need to add the city name, to distinguish between hotels having the same name, but in different locations. The search syntax should therefore include the city name, or the state code.
I went through various lists of hotels available on the web and gathered a few hundred names, complete with zip code, city, and state. I added a few youth hostels to illustrate hotels having the same name, but in different cities. Note that there are a few duplicates, and that some of the data might be outdated, but that isn't important. The data is realistic, and that was my only goal.
The data resembles that of a real-life application where several attempts had been made to make the alphabetical list “work”. Transposed to the current sample, the word “Hotel” or “Hôtel” isn't helping, as nearly one half of the hotel names start like that. It was being removed through a function, which helped a little, but of course it hadn't been removed everywhere, which created confusion in the interface. This didn't address the central problem, that the most significant word is never the first word in the name.
The application was updated, and the combo box described below was used everywhere when (the equivalent of) a hotel needed to be selected. This was perceived as a major improvement by all users, who quickly learned little tricks to locate some frequently recurring names with the minimal amount of keystrokes.
Searching “On The Fly”
The first attempt, and I'm sure other developers have started with this idea, is to trap the `change´ event of the combo box, in order to perform a new search with each keystroke. This would be a good idea if there was a way to interrupt a search when a new keystroke occurs. As it stands, this is not really possible in Access. The search runs in the same process as the interface, and a fast typist will soon become very angry at the constant pointless interruptions.
The search needs to occur only once the combo becomes idle. This could be achieved by a trigger function, as described in another article. The idea would be to use the `change´ event merely to write the current search string to another text box, in turn used as argument in a trigger function of a third. This function gets executed as soon as Access is idle, which doesn't happen when a fast typist is actively entering a search string. This solution would be quite clean (it would effectively detect the idle state), but not very elegant. On every form where a hotel needs to be selected, three interacting controls would be used (two of them hidden), which becomes hard to maintain.
Another solution is to introduce a timer. At each keystroke, the timer is reset, and the search only occurs if it's allowed to run out. This works relatively well, and a time out under half a second seems to be a good choice (for me and for the users who tested different speeds). However, the form's timer might already be used for some other trick, so a perfectly modular solution would require an independent timer. This can be achieved through a specific timer form (see below).
Most implementations of “on the fly” filtering are done in two distinct controls: a text box and a list box or a text box and a subform. If a single combo is to be used for both the entering of the search string and the display of the results, a few additional problems arise. This would not be possible at all if the combo box locked it's row source while editing. Luckily, this is not the case, and the record source can be altered dynamically. The only side effect is that the drop-down portion closes, but it can be reopened through code.
Since the edit portion is treated as a search string, the auto-complete feature needs to be turned off. This looks easy enough, but the feature is automatically activated whenever the drop-down portion is visible, regardless of the property setting. A workaround is needed to completely shut down this behaviour, not so much because it would break the search process (there are solutions for that) but because it creates an incoherent interface. If the keyword “swiss” is entered, the combo box would automatically select the first “Swisshotel” in the list; not so for the keyword “suisse”, as no hotel name starts with these letters. If the combo box never performs “auto-select”, the behaviour is predictable; if it performs it only sometimes, the user will have to check each time which it is.
This leads to the `not in list´ event. In almost all cases, the editing will end with a search string that is not as such “in the list”. However, if a single hotel has been identified, it makes sense to automatically select that record. As a matter of fact, the first hotel in the list could be selected whenever the user tabs out of the control.
A Timer Form
Perhaps this would warrant a distinct article. Each form has a timer event, triggered when the timer interval drops to zero. If a timer is needed, it is therefore possible to create a tiny form for the sole purpose of counting down some interval, and to trap its timer event. This is of course a bit of an overkill — opening an entire form just for a timer — but since Visual Basic isn't itself multi-threaded, this is the only option. The form really only needs a module (for easy instantiation) and an active timer event.
Another form's class module using the timer form could look like this:
Option ExplicitDim WithEvents Timer As FormPrivate Sub cmdTimer_Click() Set Timer = New Form_zsfrmTimer Timer.TimerInterval = 1000End SubPrivate Sub Timer_Timer() MsgBox "Time out!" Set Timer = NothingEnd Sub
The command button creates a new (hidden) instance of the timer form, called zsfrmTimer, and sets its timer interval to one thousand milliseconds. A second later, provided the button wasn't clicked again, the timer event is triggered.
When the first keystroke occurs in the combo box, a timer form is instantiated. At each keystroke, the timer interval is reset. If the timer runs out, the event is used to filter the combo box.
The mechanism is quite simple, and can be used whenever a timer is needed. On a given form, the form's own timer is of course the best choice, but the aim here was to leave the main form's timer untouched, so that the same implementation of the search combo could be used on all forms of the entire application without special adjustments.
Should several features use the timer form, they would not enter in conflict, as each module creates its own instance(s), with independent timers.
Search String Syntax
For Internet search engines, a special syntax has been developed, with quotes, plus and minus signs, and parentheses. This syntax is now relatively well known, and a search box with a commit button could implement something similar. However, when searching “on the fly”, the search syntax should be extremely simple. In the present example, it's not entirely trivial, but quite.
The text is split into words, separated by spaces, and each word is treated as a distinct search criteria. All keywords need to exist in the hotel name, but not necessarily in the order give. This is useful if the user first types “parc” and then, seeing the results, adds another keyword as in “parc grand” to locate one of the “Grand Hotel du Parc”. The search string “grand -parc” would be interpreted literally, and would not mean “all grand hotels excluding the parc hotels”.
The comma is used as separator to switch to another search field, or rather search fields. Everything after the first comma is used as a single keyword matched against the city. This makes sense in this particular scenario — the user will always enter the first letters of a city — but in other cases the search could continue with keywords.
For use in a combo box, I strongly advise against anything more complex. One reason is that the syntax should support copy-and-paste from another source. If the string “Hôtel de la Paix, Gingins” is a possible value of the combo, that same string should be usable in a search as well — and return that exact hotel. This is also why the city name is appended with a comma: it makes the search syntax quite obvious in addition to providing copy-paste support.
One final special case is handled. Switzerland uses two-letter codes for its states. When the second string is exactly two characters long and corresponds to a state abbreviation, the state field is used instead of the city field. So “ge” is not used as “ge*” and matched against all cities, but instead as “GE”, finding all the hotels in the state of Geneva (which is different from the city of Geneva).
I made the syntax as rich as possible, to show the limits of the method, but it is still relatively simple.
Implementing the Search Combo
After this lengthy introduction, let's look at some actual code. In order to implement the exact same search in many different forms, a class module is created, which will be instantiated when needed. The call from each form is simply this:
Option ExplicitDim ComboHotel As New claComboHotelPrivate Sub Form_Load() ComboHotel.Init cboHotelEnd Sub
This means that, in order to add such a combo to a new form, it's sufficient to copy it from another form and to add two lines to the form's module. This makes it very easy to create a consistent interface throughout the application.
Before it can be copied from form to form, it needs to be created. In this case, it will have five columns:
a hidden ID column (the number of the hotel),
the first non-hidden column combining the name with the city,
the name of the establishment alone,
the name of the city,
the two-letter state abbreviation.
However, it doesn't need to have a row source: that is provided by the class module.
Const cstrSelect _ = " SELECT ID," _ & " Chr(9)+Establishment & ', '+City AS Display," _ & " Establishment," _ & " City," _ & " State" _ & " FROM Hotels"Const cstrOrderBy _ = " ORDER BY City, Establishment, ID"Private Sub ResetRowSource(Optional Criteria) If IsMissing(Criteria) Then Criteria = mvarCriteria mcboAny.RowSource = cstrSelect & " WHERE " + Criteria & cstrOrderBy mfDirty = TrueEnd Sub
The only surprise here is the Chr(9) in front of the first visible column. This is the internal code for the Tab character, which cannot be entered in any field in Access. Although this character is not visible, it completely shuts down the auto-complete feature. When the user types “royal”, it will not match the “Royal Manotel, Genève”, because of the missing leading tab character.
Notice also that the standard sort order isn't alphabetic. In this instance, it's more useful to sort on the city than on the hotel name alone. After entering the keyword “jeune” (for youth hostels), the city becomes the key column.
Initialization of the Combo
The call to the Init method from the form allows the class module to trap all needed events.
Public Sub Init( _ Combo As ComboBox, _ Optional Criteria = Null) Debug.Assert Combo.OnChange = "[Event Procedure]" Debug.Assert Combo.OnEnter = "[Event Procedure]" Debug.Assert Combo.OnExit = "[Event Procedure]" Debug.Assert Combo.OnNotInList = "[Event Procedure]" Set mcboAny = Combo mvarCriteria = Criteria ResetRowSourceEnd Sub
The Debug lines are useful at design time; they will stop execution if the combo box doesn't trigger the four events needed. As can be seen in the form module above, the events do not need to be handled by the form (unless this is needed for some other purpose), but they must be active.
An optional criteria can be used at initialization, for example if only some hotels should be selectable on that particular form (only active hotels, only hotels for which a contract exists, etc). This criteria is a top-level filter for that particular instance.
The basic events are entering, exiting, and changing the content.
Private Sub mcboAny_Change() If mfrmClock Is Nothing Then Set mfrmClock = New Form_zsfrmTimer mfrmClock.TimerInterval = 300End SubPrivate Sub mcboAny_Enter() If mcboAny.ListCount Then ElseEnd SubPrivate Sub mcboAny_Exit(Cancel As Integer) Set mfrmClock = Nothing If mfDirty Then ResetRowSource: mfDirty = False mvarLast = NullEnd Sub
As explained above, the `change´ event doesn't requery the combo box. Instead, it resets a timer to 0.3 seconds. When that time elapses without user input, the requery is triggered. Note that the timer form is created only once and recycled for the entire editing session. The `exit´ event is a clean-up routine, releasing the timer form and resetting the combo box to it's unfiltered state.
The `enter´ event contains a simple yet efficient hack: it accesses the combo's list count. As a side effect, this forces Access to actually populate the list. This makes the scroll bar much more friendly and immediately usable to scroll through all records (instead of only the first batch of retrieved records). This makes sense in this demo, but not necessarily if the list is large.
Since auto-complete has been shut down, the `not in list´ event should handle the exit from the combo in a graceful manner. If the user ends the session with a mouse or arrow key selection of a hotel, then a hotel has been selected. If not, the event performs the selection.
Private Sub mcboAny_NotInList(NewData As String, Response As Integer) If mfrmClock.TimerInterval Then mfrmClock_Timer With mcboAny If .ListCount = 0 Then .RowSource = "SELECT Null, Null, '*** no match ***'" .Dropdown .Undo Response = acDataErrContinue mfDirty = True Else .RowSource = "SELECT " & .ItemData(0) & ", '" & NewData & "'" Response = acDataErrAdded mfDirty = True End If End With mvarLast = "*"End Sub
The core of the function simply selects the first item from the list. If no items were found, the combo is used as a message box, and shows “no match”. The code can easily be modified to handle the case where more than one item is in the list. Instead of just taking the first one, the function could force the user to make a selection among them, with or without displaying an error message. This is elaborated in the demo database.
Line 3 is a sanity check for really fast typists. If this event is triggered before the combo box was even refreshed, the list would not correspond to the current search criteria. In that case, and that case only, the normal time-out of 0.3 seconds is skipped and the combo is updated without delay. If a user has discovered that the letters “tik” uniquely select a given hotel, he or she will not wait for the list refresh and tab out immediately...
The Search Itself
This is the most complex, yet probably the least useful code sample, as it depends totally on the present data and on the arbitrary search syntax created for it. However, a few essential programming techniques are worth explaining.
Private Sub mfrmClock_Timer() Static sfBusy As Boolean Dim strCols() As String Dim strWords() As String Dim varW As Variant Dim varWhere As VariantOn Error GoTo Done: Do While sfBusy: DoEvents: Loop sfBusy = True mfrmClock.TimerInterval = 0 If mcboAny.ListIndex >= 0 Then GoTo Done mfDirty = True varWhere = "(" + mvarCriteria + ")" strCols = Split(mcboAny.Text, ",") If UBound(strCols) >= 0 Then strWords = Split(strCols(0), " ") For Each varW In strWords If Len(varW) Then _ varWhere = varWhere + " And " _ & "Establishment Like '*" + Swiss(varW) + "*'" Next varW End If If UBound(strCols) >= 1 Then varW = Trim(strCols(1)) If IsStateCode(varW) Then varWhere = varWhere + " And " _ & "State='" & varW & "'" ElseIf Len(varW) Then varWhere = varWhere + " And " _ & "City Like '" + Swiss(varW) + "*'" End If End If If mfrmClock.TimerInterval Then GoTo Done If Nz(varWhere) <> Nz(mvarLast) Then ResetRowSource varWhere If mcboAny.ListCount Then Else DoEvents mcboAny.Dropdown mvarLast = varWhere End IfDone: sfBusy = FalseEnd Sub
The core of the function splits the search criteria at the comma, and further splits the first part at every space. Lines 20-28 build the criteria against the hotel name, and lines 30-39 against either the state field or against the city field. The boolean function IsStateCode() performs a simple lookup of any two-character search string in the appropriate table.
Line 11 is called a semaphore. This technique is essential for any code that could potentially be reentrant, meaning in this case that it could get called again before it has finished running. For VBA, this is perhaps a very slight risk, but it's bad practice to disregard it entirely. Similarly, after creating the criteria for the requery, line XX checks whether a new timer has been set (meaning an additional character was entered). If that is the case, the current filter is already obsolete and should be discarded. Finally, line 52 clears the semaphore and the next timer event can be processed.
The Ô in Hôtel
A final refinement is needed, namely the treatment of accented characters. Since the sample data is a list of hotel names, the fact that some, but not all, are written with an “ô” cannot be ignored. Creating accent insensitive searches in Access isn't a simple topic, but since the application is limited to Swiss hotels, there are only five languages to consider, with only a handful of diacriticals.
The function simply replaces a few characters, including “o”, by a pattern for the Like operator listing all possible variants, e.g. “[oöô]”. This way, the user can type “hotel” for “Hôtel” and “zurich” for “Zürich” without harm.
As stated in the introduction, “on the fly” searching is only comfortable if the search itself is nearly instantaneous, or at least under the 0.5 seconds threshold. Searching through a few hundred records doesn't require any optimization, but for serious data a specific data structure might be needed to achieve a sufficiently low response time.
Still, even with a decent efficiency, it is rarely possible to requery forcibly between every keystroke. Formally, the search should occur in a separate process, using the time between keystrokes to narrow down and refine the search. This is not possible within Access, so the solution suggested here is to use a short timer.
1) Each keystroke resets a form timer, and doesn't requery automatically. A fast typist can enter the entire search string without any interruption.
2) When the timer runs out, the search string is analysed. A string like “grand parc,vs” is split at the comma and each part is treated separately. The first portion is assumed to contain keywords, and yields the following criteria:
Establishment Like '*p[aàäâ]r[cç]*' And Establishment Like '*gr[aàäâ]nd*'
3) The second part, being two characters long, is matched against the list of state codes. If that fails, the letters are assumed to be the first letters (and not any letters) of the city name. Since “VS” is the code for Valais, the criteria is:
4) The row source is rewritten, which forces a requery. As a side effect, the drop-down portion collapses, so the code automatically re-opens it. The requery cannot be interrupted and it can be lengthy. For that reason, all possible precautions are taken to avoid any unnecessary requery operations, for example when the user types a space or a comma, which doesn't (yet) change the current criteria.
5) Since auto-complete is deactivated, the `not in list´ event is trapped in order to simulate — if not visually at least functionally — the automatic selection of the first item in the list.
This list is more or less a repetition of the second section of the article, but from another point of view, namely how the various requirements have been met.
The very first code sample of the “Implementation” section showed that only two lines of code need to be added for every new combo box with the same purpose. The entire code is encapsulated into a single class module, achieving perfect modularity.
It should be relatively easy to transform the module for another application. The first step would probably be to determine the exact search syntax needed. It need not be as elaborate as the present example, which searches in three different fields based on rather tenuous clues in the search string. The second is to determine the columns and row source of the combo box, and to copy the corresponding SQL query to the class module; the few places where field names are used must also be updated, naturally. Finally, the search syntax must be implemented in order to produce a valid criteria for any search string.
This article is about the user interface of a dynamic search combo. However, I feel I cannot leave the topic of search optimization totally out.
The criteria for a state can be easily optimized by the engine, and an index on that field can greatly improve the response time. It is a direct comparison operation:
The assumption for a city search is that the first characters were entered.
City Like 'z[uüû]r*'
Again, the index can be used, even when the search pattern starts with a set. However, the keywords for the hotel names can be found anywhere. This requires a leading wildcard, as in:
Establishment Like '*b[aàäâ][iî]ns*'
This forces the database engine to physically read every name from disk (or over the network) and to match it against the pattern. This operation cannot be optimized, and no index can help to perform the operation any faster.
However, users will almost always type the first letters of a word. So if another linked table exists with the distinct words used in every hotel name, then that table could be used in the criteria as a subquery.
ID In (Select ID From Words Where Word Like 'b[aàäâ][iî]ns*')
The leading wildcard is gone, and the index can come back into play. The engine will lookup the ID numbers of hotels containing the word “bains” (or starting like that), and never even open the other records. What's more, database engines are optimized to handle multiple joins as criteria and the resulting execution speed is astonishing, even on large tables when combining several of these criteria.
This isn't enough information to create a search based on full-word indexing, but I felt it was important to at least mention it. Without this sort of technology, search engines would run for days before they could show you the first ten pages matching a search string!
The Demo File
The attached database contains the class module described in the previous sections and four forms. The code is heavily commented and enhanced in that three variables control the behaviour of the search.
OnTheFly toggles between “on the fly” or “search on commit”. I have used “search on commit” combo boxes for some time, and it is appropriate even for relatively slow searches (but under two seconds).
TimeOut is simply the delay between the last keystroke and the search. I settled on 0.3 seconds, but feel free to experiment.
AutoSelect handles what happens if the user tabs out of the combo and several items match the search string.
I had first considered writing two distinct articles, the first being titles “using a combo box as search box” (basically with OnTheFly turned off), but the code base is so similar that making it an option is largely preferable. If your application already has a user option dialogue, it is easy to add the option to enable or disable “on the fly” searching.
If you want to remove the option completely, delete the first two variables described above and the object variable mfrmClock. Then basically delete the lines that no longer compile; also, the timer form is no longer needed.
Finally, this is only the demonstration of a technique, using a specific data set and an ad hoc search syntax. The code is not expected to work with any other data source and significant development is needed to modify this sample for another application, notably in rewriting the routine PerformSearch almost from scratch. Hotels.mdb
Performing searches “on the fly” isn't something you need in every database. It is relatively complicated to set up, and it should definitely serve a distinct purpose for the user. In most cases, it's better to wait for the user to press the Enter (or Tab) key before performing the search, although a combo box can be used to display those results.
The application where I implemented this feature is used by a very small company, and I have access to every computer, meaning here the slowest ones, to test it. I probably wouldn't have done this if I had less control over the benchmarking. I don't like sluggish applications or unresponsive controls, and I don't expect users to like them either. Unless you have direct contact will your users, make the feature optional, as explained in the demo database.
However, I have used with success combo boxes as search boxes — the “on commit” version — in several situations, including running stored procedures from a back-end database engine. I have hinted to that possibility at the end of the article The Smart Combo, and had planned a follow-up article.
A week ago, DatabaseMX has asked me about techniques relative to “on the fly” searching. I decided to answer in the form of an article and, while writing it and developing the demo database, I realized that my planned article was no longer necessary. My previous search combo boxes are identical to those presented here, only without the additional “search while you type” feature...
I hope you found this useful, as starting point for your own search boxes, as demonstration of dynamic searches with a custom search syntax, or as inspiration for other “smart combo” techniques!
Markus G Fischer
This article is part of the EE-bookunrestricted Access.