We help IT Professionals succeed at work.
Get Started

Search within a ComboBox

Last Modified: 2016-03-28
I have some code on a datasheet form on a combobox field. This code is below. It does not run as seamless and does not reset. So when I am in form if I try to make changes to another box the combobox is still filtered. How do I get it to reset when moving to a new record. If the user starts typing it updates.

Private Sub Ultimate_ID_Change()

' Function Description:
' Filter a combo box list as the user types.
' 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.

Dim strText, strFind
' Get the text that the user has typed into the combo box editable field.
strText = Me.[Ultimate ID].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 = "tbl_ultimate.[Ultimate Parent 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) & "*"
    strFind = strFind & "'"
    ' Create the full SQL SELECt string for the combo box's
    ' .RowSource property.
    strSQL = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate Where " & _
    strFind & " ORDER by tbl_ultimate.[Ultimate Parent Name] ;"
    '' 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 "
    ''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.[Ultimate ID].RowSource = strSQL
    ' Show the entire list.
    strSQL = "SELECT tbl_ultimate.[Ultimate ID], tbl_ultimate.[Ultimate Parent Name] FROM tbl_ultimate ORDER BY tbl_ultimate.[Ultimate Parent Name]; "
    Me.[Ultimate ID].RowSource = strSQL
End If

' Make sure the combobox is open so the user
' can see the items available on list.
Me.[Ultimate ID].Dropdown

End Sub

Open in new window

Watch Question
Top Expert 2016
This problem has been solved!
Unlock 1 Answer and 20 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE