Solved Combobox search as you type issue

[frankt68]

In Access, I have a form fTest with the unbound combo box cmbOddelek that has search-as-you-type enabled, VBA code below:

Private Sub cmbOddelek_KeyPress(KeyAscii As Integer)
    Dim strSearch As String
    Dim strSQL As String

    ' Combine the current text in the combobox with the typed character (ChrW(KeyAscii))
    strSearch = Me.cmbOddelek.Text & ChrW(KeyAscii)

    ' Uncomment the following line to debug and print the current search string
    ' Debug.Print "strSearch " & strSearch

    If Len(strSearch) > 0 Then
        ' Build the SQL query to search for matches in the "Oddelek:" column
        strSQL = "SELECT [ID_šifraSTRM], [Oddelek:] FROM Oddelki WHERE [Oddelek:] LIKE '*" & strSearch & "*' ORDER BY [Oddelek:];"

        ' Uncomment the following line to debug and print the SQL query
        ' Debug.Print "strSQL > 0 " & strSQL
    Else
        ' If no search string is provided, show all records
        strSQL = "SELECT [ID_šifraSTRM], [Oddelek:] FROM Oddelki ORDER BY [Oddelek:];"

        ' Uncomment the following line to debug and print the SQL query
        ' Debug.Print "strSQL = 0 " & strSQL
    End If

    ' Set the RowSource property of the combobox to the generated SQL query
    Me.cmbOddelek.RowSource = strSQL

    ' Display the dropdown list with filtered results
    Me.cmbOddelek.Dropdown

End Sub

The code generally works, but the issue is that it doesn't work for every typed letter. When I type the first letter, it nicely displays the dropdown list with filtered results. When I type the second letter, the list disappears (becomes empty). When I type the third letter, the list is visible again, and so on. Essentially, the list is visible only when an odd number of characters are entered into the combo box. I'm interested in what the problem might be. Attached is a sample file and an animated gif.

 

[theDBguy]

For something like this, I prefer to use the Change event of the Combobox. You might give it a try to see if you'll get better results.

 

[moke123]

I'm not quite sure what your trying to accomplish .

Below is code that will filter a form as you type. Perhaps you can adapt it to do what your trying to do.

Private blnSpace As Boolean

Private Sub Text3_Change()

    Dim strFilter As String

    If blnSpace = False Then
    
        strFilter = "FirstName Like ""*" & Me.Text3.Text & "*"" or LastName like ""*" & Me.Text3.Text & "*"""
        Me.Filter = strFilter
        Me.Requery
        Me.Refresh
        Me.Text3.SetFocus
        Me.Text3.SelStart = Len(Me.Text3.Text)
        
    End If
    
    Me.FilterOn = True
        
End Sub
 
Private Sub Text3_KeyPress(KeyAscii As Integer)

    If KeyAscii = 32 Then
        blnSpace = True
    Else
        blnSpace = False
    End If
    
End Sub

 

[frankt68]

For something like this, I prefer to use the Change event of the Combobox. You might give it a try to see if you'll get better results.

I've tried and doesn't work, the dropdown list turns blank as soon as I type the first letter

 

[Josef P.]

Another approach: I let the user enter several characters and start the data filtering with Return. This costs less data accesses.

Example: Open test form from attached file and insert *int*. Commit with return key. The filtering process can be repeated nested.
Note: Esc key deletes the filter.

 

[theDBguy]

I've tried and doesn't work, the dropdown list turns blank as soon as I type the first letter

I just gave it a try, and it seems to work for me. Open up Form1.

 

[moke123]

I just gave it a try, and it seems to work for me. Open up Form1

I think it still has a glitch.

If I type in Cov it only returns one record until you hit backspace and then 3 records appear.

 

[moke123]

Maybe try MajP's Fayt class

Find (Filter) as You Type Controls (Combobox, Listbox, Form)

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class. You need to import the class module. Then instantiate a FAYT...

www.access-programmers.co.uk

 

[Gasman]

I use the class from @MajP as mentioned above.

 

[MarkK]

Here's code that works using the change event and a parameterized QueryDef...

Private Const SQL_SELECT As String = _
    "SELECT ID, Oddelek " & _
    "FROM Oddelki " & _
    "WHERE Oddelek LIKE p0 " & _
    "ORDER BY Oddelek;"
   
Private qdf_ As DAO.QueryDef

Property Get qdfSearch() As DAO.QueryDef
    If qdf_ Is Nothing Then Set qdf_ = CurrentDb.CreateQueryDef("", SQL_SELECT)
    Set qdfSearch = qdf_
End Property

Private Sub cmbOddelek_Change()
    With qdfSearch
        .Parameters(0) = "*" & Me.cmbOddelek.Text & "*"
        Set Me.cmbOddelek.Recordset = .OpenRecordset
    End With
    Me.cmbOddelek.Dropdown
End Sub

Private Sub Form_Load()
    Me.cmbOddelek.AutoExpand = False
End Sub

 

[theDBguy]

I think it still has a glitch.

If I type in Cov it only returns one record until you hit backspace and then 3 records appear.

Thanks. Please try it now...

 

[MajP]

Works fine for me with one line of code.

Private FAYT As New FindAsYouTypeCombo
Private Sub Form_Load()
  FAYT.InitalizeFilterCombo Me.cmbOddelek, "[oddelek:]", anywhereinstring, True
End Sub

 

[frankt68]

I'm not quite sure what your trying to accomplish .

I want to reduce the number of displayed records in the dropdown list of the combo box containing the search term (= text entered in combo box) in any of the columns displayed in the combo box.

Below is code that will filter a form as you type. Perhaps you can adapt it to do what your trying to do.

Thank you for your contribution, but unfortunately, I couldn't figure out how to adapt it to make it work.

 

[frankt68]

Another approach: I let the user enter several characters and start the data filtering with Return. This costs less data accesses.

Example: Open test form from attached file and insert *int*. Commit with return key. The filtering process can be repeated nested.
Note: Esc key deletes the filter.

Thank you for your contribution. It works, but it's not exactly what I wanted because it doesn't display a filtered dropdown list; all the records are still in the list. When you enter the search text, it positions to the first matching record.

 

[frankt68]

Maybe try MajP's Fayt class

Find (Filter) as You Type Controls (Combobox, Listbox, Form)

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class. You need to import the class module. Then instantiate a FAYT...

www.access-programmers.co.uk

I did and it works fine.

 

[frankt68]

Here's code that works using the change event and a parameterized QueryDef...

Private Const SQL_SELECT As String = _
    "SELECT ID, Oddelek " & _
    "FROM Oddelki " & _
    "WHERE Oddelek LIKE p0 " & _
    "ORDER BY Oddelek;"
  
Private qdf_ As DAO.QueryDef

Property Get qdfSearch() As DAO.QueryDef
    If qdf_ Is Nothing Then Set qdf_ = CurrentDb.CreateQueryDef("", SQL_SELECT)
    Set qdfSearch = qdf_
End Property

Private Sub cmbOddelek_Change()
    With qdfSearch
        .Parameters(0) = "*" & Me.cmbOddelek.Text & "*"
        Set Me.cmbOddelek.Recordset = .OpenRecordset
    End With
    Me.cmbOddelek.Dropdown
End Sub

Private Sub Form_Load()
    Me.cmbOddelek.AutoExpand = False
End Sub

Thank you for your suggestion. I've tested the code, but when I run it, it returns a runtime error 3061: Too few parameters, marking this line of code: Set Me.cmbOddelek.Recordset = .OpenRecordset

 

[frankt68]

Thank you all for the good solution suggestions. I believe that FAYT is the most suitable solution. However, I still don't understand why the code I provided at the beginning is not working as expected.

 

[Josef P.]

...it doesn't display a filtered dropdown list; all the records are still in the list. When you enter the search text, it positions to the first matching record.

I use NotInList event to start filtering.

 

[frankt68]

I use NotInList event to start filtering.
View attachment 109862

Thank you for the explanation. I guess I must have missed your instructions the first time.

 

[theDBguy]

@frankt68,

Just curious, did you try the demo I posted?