Recipe 8.3 Make Combo Boxes Load Faster

8.3.1 Problem

Sometimes you need to use combo boxes that list many items. It takes the user a long time to scroll to the bottom of the list, because the list loads only a few rows at a time. Is there any way to get the list to load all at once?

8.3.2 Solution

There is a very simple VBA technique that forces the rows of a combo or list box to load all at once when you open the form. All you have to do is force the code behind the form to calculate the number of items in the list.

Load frmComboFast in 08-03.MDB. Click the down arrow of the top combo box and scroll to the bottom of the list. Access loads only part of the list each time you scroll, so it takes many attempts to get to the last items on the list. Now do the same with the second combo box. This time, you can scroll immediately to the last item on the list.

8.3.3 Discussion

The Load event procedure in frmComboFast forces the second combo box to load the entire list, by calling the ListCount property of the control:

Private Sub Form_Load( )
  Dim lngCount As Long
  lngCount = cboFast.ListCount
End Sub

To use this code on your form, simply change the name of the control from cboFast to the name of your combo or list box. You can handle multiple controls by reusing the lngCount variable to retrieve the ListCount property value for each combo or list box that you want to load.

The form in this example loads a bit slower than it would if you didn't use this technique, because load time is sacrificed in order to improve the performance of the second combo box. If you need to use combo boxes that have very long lists, this is a price that your users probably will be quite willing to pay.