Combobox row source based upon first letter typed

[dzirkelb]

We have a combo box which currently pulls every record in our table, well over 70,000 records. This obviously isn't very good. I am trying to make the combobox results based upon the first letter they type in. Here is what I currently have:

for the OnChange event, I have this:

Me.Field40.RowSource = "SELECT [Part #], [Mfg Name], UM, Source, VendorID, ID FROM INVENTORYMASTERcombobox WHERE ([Part #] Like " & Me.Field40 & "*"");"


Field40 is what hte combobox is called. However, when I type something in, nothing populates in the drop down. What am I doing wrong?

 

[RoyVidar]

If you really, really want it to update per each keystroke, I think you'll need to investigate the .Text property of the combo control

...WHERE [Part #] Like """ & Me.Field40.Text & "*"""

But, do have a look at Allen Brownes suggestion for this kind of challenge http://allenbrowne.com/ser-32.html

 

[dzirkelb]

This is a branch off of that link...I couldn't get it to work so I am starting from one point and going forward till I get the entire code to work.

That said, i added the .text so it looks like this:

Me.Field40.RowSource = "SELECT [Part #], [Mfg Name], UM, Source, VendorID, ID FROM INVENTORYMASTERcombobox WHERE ([Part #] Like " & Me.Field40.Text & "*"");"


And still nothing. I put in a letter and the drop down box contains no records.

 

[boblarson]

I think you are going to have a problem trying to set a rowsource based on the input of the same control that you are trying to set the rowsource for. Why don't you just put a second combo box on your form, with letters from A-Z and then select it first and base the other's rowsource off of it and then requery the Field40 (you really should give your controls meaningful names, by the way so 2 years down the line, someone looking at your code and/or forms will know what it is without having to go figure it out).

 

[RoyVidar]

Look at the differences between your where clause and the one I suggested.

When this is amended, check that the column count property of the combo matches the number of columns you return.

 

[dzirkelb]

Here is the sql now:

Me.Field40.RowSource = "SELECT [Part #], [Mfg Name], UM, Source, VendorID, ID FROM INVENTORYMASTERcombobox WHERE [Part #] Like """ & Me.Field40.Text & "*"""


Still nothing. Column count is 6

I probably won't do the select a letter option as people will be copying and pasting their Part # into the field directly most of the time. I am doing this code because access limites the dropdown to 66k with change, where we have 70k. Querying the database for the first letter will guarantee the option will be available in the drop down.

 

[RoyVidar]

Strange, that is more or less exactly what Allen Browne does (for more than N letters), and what works on a testsetup here with 77K records.

What are the properties of the combo?

Column Count, Row Source Type, Auto Expand, Limit To List...

 

[dzirkelb]

It was the autoexpand...I had it set to no. I changed it to yes and it works great. Thanks guys!