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.

A "lazy-loaded" combo box. Nothing is shown until the user narrows down the possibilities first.

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:

Make sure the Row Source property is empty in design view, otherwise you won't get the performance boost when the form opens.

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

The combo box was too lazy to load all 50 states.

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

The lines above come from the form code-behind.