search (while you type) in a combobox

7/25/2019 Search (While You Type) in a ComboBox

1/13

Search (While You Type) in a ComboBox

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.

1 of 16


2/13


3/13


4/13

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 onlyneeds a module (for easy instantiation) and an active timer event.

Another form's class module using the timer form could look like this:Option Explicit

Dim WithEvents Timer As Form

Private Sub cmdTimer_Click()

Set Timer = New Form_zsfrmTimer

Timer.TimerInterval = 1000

End Sub

Private 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

4 of 16


5/13


6/13

is created, which will be instantiated when needed. The call from each form is simply this:Option Explicit

Dim ComboHotel As New claComboHotel

Private Sub Form_Load()

ComboHotel.Init cboHotel

End 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.

The ComboBox

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 = True

End 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

6 of 16


7/13

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, Genve, 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 theInitmethod 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

ResetRowSource

End 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.

Combo Events

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 = 300

7 of 16


8/13

End Sub

Private Sub mcboAny_Enter()

If mcboAny.ListCount Then Else

End Sub

Private Sub mcboAny_Exit(Cancel As Integer)

Set mfrmClock = Nothing

If mfDirty Then ResetRowSource: mfDirty = False

mvarLast = Null

End 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 aclean-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

8 of 16


9/13

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 Variant

On 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

9 of 16


10/13

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


& "State='" & varW & "'"ElseIf Len(varW) Then


& "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 If

Done:

sfBusy = False

End 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.

10 of 16


11/13

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 Htel

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 Accessisn'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.Function Swiss(ByVal pstrText As String) As String

pstrText = Replace(pstrText, "a", "[a]") pstrText = Replace(pstrText, "e", "[e]")

pstrText = Replace(pstrText, "i", "[i]")

pstrText = Replace(pstrText, "o", "[o]")

pstrText = Replace(pstrText, "u", "[u]")

pstrText = Replace(pstrText, "c", "[c]")

Swiss = pstrText

End Function

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 Htel and zurich for Zrich without harm.

Summary

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 secondsthreshold. 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 everykeystroke. 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.

11 of 16


12/13


13/13

search (while you type) in a combobox

Documents