A Lazy-Loading Combo Box In Only 3 Lines of Code

If you're not careful, a single combo box can wreck the performance of your form.

Let's say your combo box is based on a list of hundreds of thousands of records.  (For the sake of this article, we'll assume that cascading combo boxes are not appropriate.)  If you set the RowSource so that every record in the underlying table appears in your combo box, that form is going to grind to a halt.

One way to get the advantages of a combo box without paying the severe performance penalty is to use a technique known as "lazy loading."  

Lazy Loading

It's called lazy loading because when the form first opens, nothing is populated in the combo box.  If you click the triangle, the drop down list will appear, but it will be empty.  

The combo box will remain empty until the user enters some minimum number of characters.  Then, and only then, will the combo box be populated.  But instead of all the hundreds of thousands of records appearing, the code uses those first few entered characters to filter the list of possibilities.  Instead of hundreds of thousands of entries, the combo box might only contain a couple of hundred entries. The smaller list will load much faster.

How To Do It

During my presentation on Advanced Combo Box Techniques, I demonstrated this lazy loading approach.  The combo box is empty when the form first opens.  You'll want to make sure the combo box's Row Source property is cleared out:

After the user enters three characters, the row source is updated with the matching items:

Surprisingly, it takes only three lines of code to implement this technique:

• Declaration of a new weComboLookup object at the top of the form's module
• A call to the .Initialize method in the Form_Load event
• Setting the .UnfilteredRowSource property to a SQL string or a named query

The key part is the double asterisk in the WHERE clause.  Once the user enters the minimum number of characters, the text from the combo box gets inserted between these asterisks to perform the filtering.  

Sample Code

Enter the code below in the combo box's form module.  To apply this technique to multiple combo boxes, you will need separate instances of the weComboLookup object for each combo box.

Private ComboLookup As New weComboLookup

Private Sub Form_Load()
    ComboLookup.Initialize Me.Combo0, 3
    ComboLookup.UnfilteredRowSource = _
        "SELECT StateName " & _
        "FROM tblState " & _
        "WHERE StateName Like '**' " & _
        "ORDER BY StateName; "
End Sub

Dependencies

Of course, to make this work, you'll also need the weComboLookup class module.

Video Demonstration

To see the technique in action, jump to the 34:43 mark of my Access User Group presentation.

Referenced articles

Lazy Loading Combo Boxes

Don’t load tens of thousands of records into a combo box. Instead, wait for the user to start typing, and then load only what they need.

No Longer SetMike Wolfe

Cascading Combo Boxes

Cascading combo boxes are one of the best ways to find data in a fixed hierarchy. Here’s what the concept looks like in action.

No Longer SetMike Wolfe

Advanced Combo Box Techniques

A list of resources and further reading to support my presentation on Advanced Combo Box Techniques.

No Longer SetMike Wolfe

A Wonderful, Magical Class Module

Imagine a single class module you can use for progressive combo box filtering, lazy loading combo boxes, AND multi-column filtering!

No Longer SetMike Wolfe

Image by Masakazu Kobayashi from Pixabay