Progressive Combo Box Filtering

Combo boxes are great.  

You can use the autocomplete feature (AutoExpand = True) to save keystrokes and increase efficiency.  Enter the first few characters of the option you want and Access will complete the remaining portion of the string.

The limitation of this feature is that you have to know the first few letters of your item or the combo box won't autocomplete the match.  But what if you want your users to be able to search through the list of options from within the combobox's text area?

For that, you will need to dynamically update the combobox's RowSource property as the user changes the text.

Progressive Filtering

What is progressive filtering?  

A filter is something that removes unwanted items.  In the case of the combo box, it means removing items that don't match the entered text.

A progressive filter is one where the filter changes with each change to the entered text.

This is a powerful technique.  It lets you create combo boxes where users can take advantage of autocomplete even for lists that contain hundreds of thousands of entries.  

This is crucial both for practical and performance purposes.  The dropdown portion of a combo box can only display 65,536 records (2 ^ 16).  If you have more than that many records in your row source, then any records that don't appear in that first 65,536 will be unavailable for autocomplete.  

(I should note, though, that even if "Limit to List" is set to Yes, you will be able to manually enter an entry that does not fit in the first 65,536 results, so long as it appears somewhere in the full RowSource.)

Demonstration

Here is an example of a progressive filtering implementation I wrote:

The overall concept is simple, but the devil is in the details when getting this to work properly with all the edge cases.

To see how I accomplish this effect and many other cool combo box tricks, join me next Friday, June 18, 2021, at 9 AM EDT for the Eastern Time Access User Group webinar.

Photo by Thomas Martinsen on Unsplash