MS Access fast Combo Box with VBA

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?

Answer

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