CODEWITHSUNDEEP
formsms-accesscomboboxvba
Sophie
Sophie

Reputation: 21

Limiting RowSource in ComboBox (Access)

I have a form which has a ComboBox on it that pulls all DISTINCT colleague names from a huge table that includes all of our sales (50k+ records). It works perfectly fine, but it takes 3-4 minutes to open the form because it takes so long for Access to find all unique colleague names in the table.

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 CollName_Change()
Dim strText As String

strText = Nz(Me.CollName.Text, "")

If Len(strText) > 2 Then

Me.CollName.RowSource = "SELECT CollPerf.Colleague FROM CollPerf WHERE CollPerf.Colleague LIKE ""*"" & strText & ""*""; "

Me.CollName.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'm relatively new to Access, although already built two databases, but they all have relatively basic SQL queries, so I have no idea what I'm not doing right here.

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

Upvotes: 1

Views: 1506

Answers (1)

Andre
Andre

Reputation: 27634

You only have some double-quote mixup there. It is much easier to use single quotes instead of double double-quotes.

Me.CollName.RowSource = _
 "SELECT CollPerf.Colleague FROM CollPerf WHERE CollPerf.Colleague LIKE '*" & strText & "*';"

But your query would be way faster if you would only use the starting letters, i.e. remove the leading *

Me.CollName.RowSource = _
 "SELECT CollPerf.Colleague FROM CollPerf WHERE CollPerf.Colleague LIKE '" & strText & "*';"

But that depends on your requirements.

EDIT to debug:

Dim strText As String
Dim strSelect As String

strText = Nz(Me.CollName.Text, "")

If Len(strText) > 2 Then

    strSelect = "SELECT CollPerf.Colleague FROM CollPerf WHERE CollPerf.Colleague LIKE '*" & strText & "*';"
    Debug.Print strSelect 

    Me.CollName.RowSource = strSelect 
    Me.CollName.Dropdown

End If

Copy strSelect from the Immediate Window into a query, and try it out. It should help resolve the problem.

See How to debug dynamic SQL in VBA

Upvotes: 1

Related Questions