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
From novice to tech pro — start learning today.