Search as you type in combo box

[daniel2478]

Hello Everyone,


I am trying to make my combo box have a search capability and be able to narrow down the drop down list to something shorter


current function of the combo box will show the entire list
but when there are 100's of records then this method does not work too well

I would like to have the option to start typing and the drop down list narrowed down to the matching or similar string


below is sample code that i modified to match my table and field names but does not work as intended, once something is typed in then a window pops up requesting to enter parameter value

thanks in advance!

Private Sub cboSample_Change()
 
    Dim strText, strFind
 
    ' Get the text that the user has typed into the combo box editable field.
    strText = Me.cboSample.Text
 
    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
                strFind = Left(strFind, Len(strFind) - 1)
            End If
            strFind = strFind & "*" & Mid(strText, i, 1) & "*"
        Next
        strFind = strFind & "'"
 
        strSQL = "SELECT tblSample.ID, tblSample.fieldName, SortOrder FROM tblSample Where " & _
        strFind & " ORDER BY SortOrder;"
 
        ' Filter the combo list records using the new SQL statement.
        Me.cboSample.RowSource = strSQL
 
    Else
        ' Show the entire list.
        strSQL = "SELECT tblSample.ID, tblSample.fieldName, tblSample.SortOrder FROM tblSample ORDER BY tblSample.SortOrder; "
        Me.cboSample.RowSource = strSQL
    End If
 
    ' Make sure the combobox is open so the user
    ' can see the items available on list.
    Me.cboSample.Dropdown
 
End Sub
 

[jforbes]

I think this is a very similar Question that you may want to check out: Combobox Stop Autofilling Text portion from selected item

Most likely your error is from a malformed SQL, you could use the following to help you debug it: How to Debug SQL String

[ADezii]

I usually incorporate this type of functionality in the Change() Event of a Text Box above the Combo Box and not in the Combo Box itself. The basic Code for a 2-Column Combo Box consisting of [Last Name], [First Name] and for all matches of char(s) entered* (F*, Fr*, Fre*, etc,), would be:

Private Sub txtTest_Change()
  Me![cboTest].RowSource = "SELECT [Last Name], [First Name] FROM Customers WHERE " & _
                           "[Last Name] Like '" & Me![txtTest].Text & "*' ORDER BY " & _
                           "[Last Name]"
End Sub

[daniel2478]

thanks for the responses, i'll give both a try