Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How Do I Set Up Find As You Type In A Combo Box?

Combo Boxes

How Do I Set Up Find As You Type In A Combo Box?

by  tomh1  Posted    (Edited  )
Firefox and the various Desktop Search applications have introduced users to the concept of "find as you type." Combo Boxes in MS Access offer an automatic AutoComplete property to automatically select the first entry that matches the characters that the user types.

AutoComplete assumes that the user is typing the first character and typing characters that should be successive. For instance, if the user types "g" in a list that contains "Andrew," "Carl," "George" and "Scott," AutoComplete selects "George," but typing "o" would not return any results.

Some programs have extended the find-as-you-type concept to include automatic "abbreviations." Examples include [link http://colibri.leetspeak.org/]Colibri[/link] and [link http://www.candylabs.com/approcket/]AppRocket[/link] for Windows and [link http://www.obdev.at/products/launchbar/index.html]LaunchBar[/link] and [link http://www.petermaurer.de/nasi.php?thema=butler&id=butlerhelpers&sprache=english]Butler[/link] for Mac OS X. In such cases, find-as-you-type will find any entry with the typed characters. In the example above, typing "o" would return "George" and "Scott," and typing "oe" would return just "George."

Implementing this in Access proves to be fairly easy.
[ol]
[li]Create a combo box on a form.[/li]
[li]Create an event procedure for the combo box's OnChange event.[/li]
[li]Copy and paste the code, below, into the OnChange procedure[/li]
[li]Replace "Combo0" with the name of your combo box.[/li]
[li]Change the strSQL strings to match your table and fields.[/li]
[li]You're done.[/li]
[/ol]

Code: 
Private Sub Combo0_Change()
' Function Description:
' Filter a combo box list as the user types, similarly to how application
' launchers like Colibri, AppRocket and LaunchBar opperate.
    ' e.g. if the list contains the names of U.S. Presidents, and
    ' the user types "gw," then the resulting SQL WHERE clause will
    ' look like "Name Like '*g*w*'" and the resulting list
    ' will include George Washington, George H. W. Bush and
    ' George W. Bush, among others.
    ' The order is preserved, so that typing "wg" creates an SQL WHERE
    ' clause like "Name Like '*w*g*'" and the resulting list would
    ' include George Washington but not the Bushes.
    
' This is accomplished by grabbing the text typed by the user in the
' combo box's edit field, creating an SQL SELECT statement from it,
' and finally applying that SQL statement to the combo box's
' .RowSource property.

' Form design settings:
'  Set AutoExpand to No
'  Column Count 3
'  Keyed on column 1 (record primary key)
'  Showing column 2 (user-readable data) column 2 width > 0
'  First and Second column width=0

Dim strText, strFind

' Get the text that the user has typed into the combo box editable field.
strText = Me.Combo0.Text

' If the user has typed something in, then filter the combobox
' list to limit the visible records to those that contain the
' typed letters.
' Otherwise (if the field is blank), the user has deleted whatever
' text they typed, so show the entire (unfiltered) list
If Len(Trim(strText)) > 0 Then
    ' Show the list with only those items containing the typed
    ' letters.
    
    ' Create an SQL query string for the WHERE clause of the SQL
    ' SELECT statement.
    strFind = "Name Like '"
    For i = 1 To Len(Trim(strText))
        If (Right(strFind, 1) = "*") Then
            ' When adding another character, remove the
            ' previous "*," otherwise you end up with
            ' "*g**w*" instead of "*g*w*."
            ' This has no apparent impact on the user, but
            ' ensures that the SQL looks as intended.
            strFind = Left(strFind, Len(strFind) - 1)
        End If
        strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next
    strFind = strFind & "'"
    
    ' Create the full SQL SELECt string for the combo box's
    ' .RowSource property.
    strSQL = "SELECT tName.nameKey, tName.Name, SortOrder FROM tName Where " & _
    strFind & " ORDER BY SortOrder;"
    
    '' NOTE: to remove the order requirement, such that typing "wg"
    '' and "gw" return the same results, the SQL WHERE clause needs
    '' to look like "Name Like '*w* AND *g*'."
    '' The code above should be changed as follows:
    ''For i = 1 To Len(Trim(strText))
    ''   strFind = strFind & "Name Like '*" & Mid(strText, i, 1) & "*' And "
    ''Next
    ''
    ''strSQL = "SELECT tName.nameKey, tName.Name, SortOrder from tblApps Where " & _
    ''Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
    
    ' Filter the combo list records using the new SQL statement.
    Me.Combo0.RowSource = strSQL
    
Else
    ' Show the entire list.
    strSQL = "SELECT tName.nameKey, tName.Name, tName.SortOrder FROM tName ORDER BY tName.SortOrder; "
    Me.Combo0.RowSource = strSQL
End If

' Make sure the combobox is open so the user
' can see the items available on list.
Me.Combo0.Dropdown

End Sub

One difference between the application launchers mentioned above and this code is that the application launchers remember your last selection, and intelligently reorder the list to bring your most common selections to the top. This code does not do that, though it should be possible through the combo box's OnLostFocus event and (probably) an extra field or two in the corresponding table.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top