forms - Limiting RowSource in ComboBox (Access) -

i have form has combobox on pulls distinct colleague names huge table includes of our sales (50k+ records). works fine, takes 3-4 minutes open form because takes long access find unique colleague names in table.

i've been trying research , found looks useful, can't seem right.

the code have @ moment:

private sub collname_change() dim strtext string  strtext = nz(me.collname.text, "")  if len(strtext) > 2  me.collname.rowsource = "select collperf.colleague collperf collperf.colleague ""*"" & strtext & ""*""; "  me.collname.dropdown  end if end sub 

i found code on 2 forums, supposed following: "the key not have row source defined combo box. row source defined user starts typing letters. once 3 letters row source of combo box defined , combo box told dropdown."

when 3 letters, dropdown appears, it's blank, doesn't display results.

i'm relatively new access, although built 2 databases, have relatively basic sql queries, have no idea i'm not doing right here.

any advice? or alternatively different solution how take combo box faster , still keep values unique?

you have double-quote mixup there. easier use single quotes instead of double double-quotes.

me.collname.rowsource = _  "select collperf.colleague collperf collperf.colleague '*" & strtext & "*';" 

but query way faster if use starting letters, i.e. remove leading *

me.collname.rowsource = _  "select collperf.colleague collperf collperf.colleague '" & strtext & "*';" 

but depends on requirements.

edit debug:

dim strtext string dim strselect string  strtext = nz(me.collname.text, "")  if len(strtext) > 2      strselect = "select collperf.colleague collperf collperf.colleague '*" & strtext & "*';"     debug.print strselect       me.collname.rowsource = strselect      me.collname.dropdown  end if 

copy strselect immediate window query, , try out. should resolve problem.

see how debug dynamic sql in vba