Question:
I’m trying to create a DBCombo function on a form which operates similar to MS Access’s ComboBox. As the user types in an entry, character by character, the DBCombo’s list would show any/all partial matches (i.e., “AIR” yields “AIR”, “AIRCONDITION”, “AIRPLANE”) to that entry that were already in the table. As more characters were typed in, the list would get smaller, etc. (Very similar to the way Windows’ Help | Search functions…) The user could select an existing item on the drop-down list or use the new entry if it is unique.
Answer:
In the Change event for the dropdown, you’ll want to query your database for data LIKE what’s in the box. You can put ‘%’ signs around the criteria and it will work like a wildcard. For instance:
set ssTemp = db.CreateSnapshot(“SELECT Name from CUST where Name LIKE ‘%” & dbcombo.Text & “%'”)I don’t think that is the correct syntax for DAO 3.0, but you get the idea. Then empty the combo box and refill it with the data from the snapshot. As you’ll probably realize, this will be a pretty slow process if you do it for each character. You could probably modify this to use a timer control, which starts each time the text box changes, and if the timer expires, then refill the list. I believe that’s the way MS Help works now.Alternately, if your combobox starts out with all the possibilities loaded, you could load a hidden listbox (with its sorted property set to TRUE) at the same time you initially loaded the combobox. Then all you’d have to do is search the hidden listbox for the partial matches, reloading the combobox from that.This would save all of the hits on your database which ought to speed things up a bit, especially if it’s a network app and the database lives on a different machine than the front end.
