MS Access fast Combo Box with VBA

Question

I have a form which has a ComboBox on it that pulls records via ID and displays Name from a linked table. Standard look for values in the form combo box wizard generated. It works perfectly fine, but it takes 3-4 minutes every time to find a single record.

I've been trying to research this and found something that looks useful, but can't seem to get it right.

The code I have at the moment:

 Private Sub Combo81_Change()
 Dim strText As String
 Dim strSelect As String
 strText = Nz(Me.Combo81.Text, "")
 If Len(strText) > 2 Then
 strSelect = "SELECT Name FROM CTable WHERE Name LIKE '*" & strText & "*'; "
 Debug.Print strSelect
 Me.Combo81.RowSource = strSelect
 Me.Combo81.Dropdown
 End If
 End Sub

I found this code on two forums, this is supposed to do the following: "the key is to not have a Row Source defined for the Combo Box. The row source will be defined as the user starts typing letters. Once they get to 3 letters then the row source of the combo box will be defined and the combo box will be told to dropdown."

When I get to 3 letters, a dropdown appears, but it's blank, it doesn't display any results.

I would like when the user types, e.g. "Smith" only those people with the name Smith come up.

I'm relatively new to Access and the DB I'm using the FE/BE with linked tables to a shared network folder and FE on users Desktops.

Any advice? Or alternatively a different solution as to how take my combo box faster and still keep values unique?

Sometimes, but not always, including the terminator in your sql statement causes problems. It is fine to leave the semi-colon SQL terminator out when you build your query expression: "SELECT Name FROM CTable WHERE Name LIKE '" & strText & "'" — KacireeSoftware
Should i leave the search for record after update even with (="[ID] = " & Str(Nz([Screen].[ActiveControl],0))) on/ OR delete it? — Shannon Regan
Does this answer your question? Search as you type with MS Access Combobox — HackSlash

a.sadegh63 a.sadegh63 · Accepted Answer · 2021-02-04T17:44:57

you can use following codes to search value in a combo-box in ms access as user type, suppose we have a combo-box name org_id in our form, for search a value in org_id we need three event on org_id combo-box. AfterUpdate, LostFocus and KeyPress events. codes are:

Dim strFilter As String ' Module scope variable used for filter on our combo (org_id)

Private Sub org_id_AfterUpdate()
    strFilter = ""
    strSQL = "SELECT org_tbl.org_id, org_tbl.org_name, org_tbl.org_code FROM org_tbl" & _
             " ORDER BY org_tbl.org_code"
    org_id.RowSource = strSQL
    
End Sub

Private Sub org_id_LostFocus()
    strFilter = ""
    strSQL = "SELECT org_tbl.org_id, org_tbl.org_name, org_tbl.org_code FROM org_tbl" & _
             " ORDER BY org_tbl.org_code"
    org_id.RowSource = strSQL
End Sub

Private Sub org_id_KeyPress(KeyAscii As Integer)
    strSQL = "SELECT org_tbl.org_id, org_tbl.org_name, org_tbl.org_code FROM org_tbl ORDER BY org_tbl.org_code"
    
    If KeyAscii <> 8 Then ' pressed key is not backspace key
        strFilter = strFilter & Chr(KeyAscii)
    End If
    
    If IsNull(strFilter) = True Or strFilter <> "" Then
        If KeyAscii = 8 Then ' pressed key is backspace key
            strFilter = Left(strFilter, (Len(strFilter) - 1))
        End If
    End If
    
    strSQL = "SELECT org_tbl.org_id, org_tbl.org_name, org_tbl.org_code FROM org_tbl" & _
             " WHERE org_name Like '*" & strFilter & "*' ORDER BY org_tbl.org_code"
    org_id.RowSource = strSQL
    org_id.Dropdown
End Sub

I hope this (answer) helps you.

edit: you can download sample file from following link: Access combo box to search as you type sample file

MS Access fast Combo Box with VBA

1 Answers