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, Feb 02 '21 at 20:42
Should i leave the search for record after update even with (="[ID] = " & Str(Nz([Screen].[ActiveControl],0))) on/ OR delete it? — Shannon Regan, Feb 02 '21 at 21:09
Does this answer your question? [Search as you type with MS Access Combobox](https://stackoverflow.com/questions/33892474/search-as-you-type-with-ms-access-combobox) — HackSlash, Feb 02 '21 at 21:42
I tested your code and it works for me. Didn't matter if AutoExpand was Yes or No. The semi-colon was not an issue. Name is a reserved word. Try enclosing in brackets `[Name]`. Advise not to use reserved words as names nor spaces/punctuation/special characters. — June7, Feb 04 '21 at 04:37
I tried adding brackets and it worked, almost...!!! It won't go to the record...? I need it to go to the record the user selects after typing. I just create this combo box without the wizard and added the code. what am i missing? — Shannon Regan, Feb 05 '21 at 17:17
I finally figure it out, I added DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0)) to the after update portion of the code. Thank you so much for your help!!! — Shannon Regan, Feb 11 '21 at 15:43

a.sadegh63 · Accepted Answer · 2021-02-06T16:33:49.060

0

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

edited Feb 06 '21 at 16:33

answered Feb 04 '21 at 17:44

a.sadegh63

56
1
8

Where do I put this code? just go to Design, view code, and put it all in there and modify the combo and field names. Or add it individually to the each event in the form property window? – Shannon Regan Feb 05 '21 at 15:51
I've added a picture above of what the combo box's properties currently looks like – Shannon Regan Feb 05 '21 at 15:57
just open your form in design view. 1. click on View Code button on Design tab. 2. copy and paste above codes in VBE window. 3. find and replace org_id with your combo name. note all event name must edit with your combo name, for example org_id_LostFocus must changes to your_combo_LostFocus. – a.sadegh63 Feb 06 '21 at 16:12
I had the same situation with this and other solution above. I finally get it to work but it won't filter or go to that record in the form. What am I missing? once the correct name is selected I need the form to go to that record.... – Shannon Regan Feb 11 '21 at 14:51
I finally figure it out, I added DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0)) to the after update portion of the code. Thank you so much for your help!!! – Shannon Regan Feb 11 '21 at 15:43
IS there a way only start that search after a set number of characters have been entered, like 4? – Shannon Regan Feb 11 '21 at 21:49
Place last for lines in org_id_KeyPress event in an If block. `If Len(strFilter) >= 4 then strSQL = "... End If` – a.sadegh63 Feb 13 '21 at 19:11

MS Access fast Combo Box with VBA

1 Answers1