Search As you Type for Microsoft Access Combobox

John TsioumprisIT Supervisor
CERTIFIED EXPERT
I am passionate software developer and also a competent Systems administrator.I enjoy learning new things and challenges...
Published:
Edited by: Andrew Leniart
This is a rather simple but effective way of searching while typing on a Microsoft Access Combobox.
Today I was faced with a somewhat small "challenge"

While I am totally commited in using Combobox for selecting values there are cases where you want to perform a little intelligent search as you type (to tell the truth usually my data are "well-formed so this hasn't been a big need (till now).

I took a look around and found some solutions but there were either too complicated or required some kind of extra feeding in order for them to work while we already have whatever we need right in front of us.

So here is the code (3 small functions)
The main search function:
Public Sub SearchComboboxAsYouType(combo As ComboBox)
    Dim comboRowSource As String
    Dim comboColumns As Variant
    Dim activeColumns As Variant
    Dim systemSeparator As String
    Dim activeFieldToFilter As String
    ' if we haven't setup filtered Rowsource then store the RowSource to Tag
    If Len(Nz(combo.Tag, vbNullString)) = 0 Then
        combo.Tag = combo.RowSource
    End If
    systemSeparator = fGetSeparator
    comboRowSource = Trim(combo.RowSource)
    'Get part from SELECT to FROM and split it to Array based on Comma "," Separator
    comboColumns = Split(ExtractStringFromKeyWords(comboRowSource, "SELECT", "FROM"), ",")
    activeColumns = Split(combo.ColumnWidths, systemSeparator)
    For i = LBound(activeColumns) To UBound(activeColumns)
        If CInt(activeColumns(i)) > 0 Then
            activeFieldToFilter = comboColumns(i)
            Exit For
        End If
    Next
    Dim strSQL As String
    If Len(combo.Text) > 0 Then
        If InStr(comboRowSource, "WHERE") Then
            comboRowSource = Split(comboRowSource, "WHERE")(0)
       
            strSQL = comboRowSource & " WHERE " & activeFieldToFilter & " LIKE '*" & combo.Text & "*'"
        Else
        If Right(comboRowSource, 1) = ";" Then comboRowSource = Left(comboRowSource, InStr(comboRowSource, ";") - 1)
            strSQL = comboRowSource & " WHERE " & activeFieldToFilter & " LIKE '*" & combo.Text & "*'"
        End If
    Else
        strSQL = combo.Tag
    End If
    combo.RowSource = strSQL
    combo.Dropdown
End Sub

A helper function to extract the Fields from the Combo's Rowsource
Function ExtractStringFromKeyWords(InputString As String, KeywordFrom As String, KeyWordTo As String) As String


Dim tmpExtracted As String
If Len(InputString) > 0 Then tmpExtracted = Split(InputString, KeywordFrom)(1)
If Len(tmpExtracted) > 0 Then tmpExtracted = Split(tmpExtracted, KeyWordTo)(0)
tmpExtracted = Trim(tmpExtracted)
ExtractStringFromKeyWords = tmpExtracted
End Function

The final helper function to extract the list separator from Regional Settings (this might be an issue but right now I can't test it on other setups...although it would be fairly simple to make the necessary adjustments.
Function fGetSeparator() As String
    Dim RegObj As Object, RegKey As String
    Set RegObj = CreateObject("WScript.Shell")
    RegKey = RegObj.RegRead("HKEY_CURRENT_USER\Control Panel\International\Slist")
    If RegKey = "" Then
        MsgBox "There Is no registry value For this key!", vbInformation, "Separator"
    Else
        fGetSeparator = RegKey
    End If
    Set RegObj = Nothing
End Function

Just put all the above in a single module (or more if you want to separate them based on your needs)
To use the feature of searching while typing on your combobox I use the OnChange event.
Private Sub yourCombo_Change()
 SearchComboboxAsYouType Me.ActiveControl
End Sub

After you had made your selection the original Rowsource is restored.
Private Sub yourCombo_AfterUpdate()
'Restore Combo's Rowsource
Me.yourCombo.RowSource = Me.yourCombo.Tag
End Sub
All these should be enough to "grant" you the ability to easily perform the searching while typing.

Quick code explanation.
The Function SearchComboAsYouType receives the control "combobox"
It examines the Tag property of the combobox and if is empty copies the original combo's Rowsource (now this is a case where you can make your own variation if for example. Tag is already is filled for another reason - I use it a lot for "guiding" for Not In The List inserts - or you want to use a form level variable or what so ever)
After that, it retrieves the RowSource of the Combobox and splits it from the "SELECT to From" to extract the Fields that are used in the Combobox and also examines the Widths of the Columns - its common to hide the key and display the Text representation of the "key"
It then iterates the fields and the first that is Visible (Len >0) is assigned as the keyField to search
If Rowsource is already filtered it strips the part of "WHERE ...." and appends the keyField with the "LIKE" (now another possible issue...what if the combobox already has a WHERE...no big deal as the rowsource is "static" so just perform an Instr to find the last word of the predefined Criteria and make the necessary adjustments to append the keyfield with the Like Operator)
If is not filtered it strips the ";" at the end and performs the append.
Lastly, Combo's Rowsource is assigned the new filtered Rowsource and it DropDowns to show only the filtered records
Hope it comes handy for someone..comments/corrections as always most welcomed
0
110 Views
John TsioumprisIT Supervisor
CERTIFIED EXPERT
I am passionate software developer and also a competent Systems administrator.I enjoy learning new things and challenges...

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community