1

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?

HansUp
  • 95,961
  • 11
  • 77
  • 135
Sophie
  • 21
  • 4

1 Answers1

1

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

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thank you, I've tried this earlier (this is how it was in the original code, but it didn't work, so I ended up switching to double-quotes. I pasted what you wrote here and I still have the same problem: after typing 3 letters, a blank dropdown appears, but nothing else happens, the names don't seem to be pulling through. – Sophie Jan 27 '16 at 15:23
  • I'm really sorry to be a pain, but something is still not okay. I edited the code as you suggested, pasted the SQL in a Query, which returns some names. Still, nothing appears in the combo box, the dropdown remains blank. What am I doing wrong? – Sophie Jan 27 '16 at 15:55
  • @Sophie: the query returns 1 column - does your combobox perhaps have 2 columns, with the width of the first column = 0 ? – Andre Jan 27 '16 at 16:05
  • No, it only has one column, 2.54 cm wide. It was working perfectly when I only had a SELECT DISTINCT query as a RowSource, just took ages to load. Row Source is blank, so is Row Source Type. Limit to List: Yes. Show only row source values: No. Event > all blank, except for On Change, that has the Event Procedure that is above. – Sophie Jan 27 '16 at 16:38
  • 1
    @Sophie: Ah, there's the culprit: `RowSourceType` must be "Table/Query", because you are going to set a query as RowSource. I just reproduced it, with an empty `RowSourceType` it doesn't work. – Andre Jan 27 '16 at 16:43