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) & "*"
Next
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 "
''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.[Ultimate ID].RowSource = strSQL
Else
' 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
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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE