MS Access 2010 Multi column Combobox Autocomplete

Question

I have a simple combobox in Access which serves as a result set holder and contains two columns id and name.

I have developed a simple search feature, i.e user types in a keyword in a textbox and the combobox returns the results after searching the substring,

Example:

TXT BOX Input: App

Combobox Results:

ID Name

1   Cinnamon Apple
2   Apple Candy Box
42  Carton of Apples
54  iphone App
6   App Store

Now when I go to search the combobox box within the results and I type "i", I expect to select "iphone App" from the drop down but nothing happens because it is searching the first column i.e ID

So how can I search for the names and autocomplete the combobox? or search by second column

Also, It is important for me to show the ID, If I hide the ID column I can search by the Name just fine, since it is the only column in the result set.

Answer 1

Hiding the ID is the simplest way of accomplishing what you want, but I recognize that you said you don't want to hide the ID field.

I think it is possible to do what you want. Basically, on every keypress you will need to reset the RowSource for the combobox to be filtered down to only items that match the users keypress. In my experience, this works best in dropdown menus that you are using for filtering, as opposed to dropdowns used for data entry, especially if you are using a continuous or datasheet form. There are a lot of gotchas and caveats you'll run into when doing this. I can't really recommend attempting this unless you are an experienced VBA programmer. My own implementation uses quite a number of events to make it work including Enter, KeyUp, KeyDown, KeyPress, and AfterUpdate. I also use a timer so that I can accumulate their keypresses and only change the rowsource after some 300ms of no keypresses. I also use ADO with a Disconnected Recordset so that I can filter the recordset without making calls to the database again (in the name of efficiency and "keeping the wire cool").

If you want some code, I did post a very basic version of this early in my development of the idea over at UtterAccess. I think it's probably going to be buggy and it might not work properly with you wanting to show the ID field. That is something that I had to make work properly on one of my projects since I originally posted my code.

http://www.utteraccess.com/forum/Autocomplete-Autosugges-t1986007.html

Answer 2

The Workaround is to show Name and then ID in the Combobox, So when I start typing in the Combobox , it Autocompletes Name by Default instead of ID.