Load Combo Box Faster

[Dan]

Hi,

I have a form that has six combo boxes. Each has a SQL
statement for the ROWSOURCE which is filtered where
TxtModel is entered. So the ROWSOURCE depends on what
the user enters in that textbox. The problem I been
having is that it takes a long time to load the values
the the drop down box when the user click on the arrow.
I tried also putting the SQL on the Get Focus them make
the ROWSOURCE = SQL statement, but it was also slow.
Is there anyway this can be fix so it can be more
efficicent?
Thanks

 

[joemach]

Dan,

How many fields are you returning from your SQL statement that you don't
need in your combobox?

Also, how many records does the SQL statement return?

joemach

 

[Dirk Goldgar]

Hi,

I have a form that has six combo boxes. Each has a SQL
statement for the ROWSOURCE which is filtered where
TxtModel is entered. So the ROWSOURCE depends on what
the user enters in that textbox. The problem I been
having is that it takes a long time to load the values
the the drop down box when the user click on the arrow.
I tried also putting the SQL on the Get Focus them make
the ROWSOURCE = SQL statement, but it was also slow.
Is there anyway this can be fix so it can be more
efficicent?
Thanks

Do you filter the combo boxes to reduce the number of items in the list
to a manageably small number? If so, but it takes too long to run the
query, make sure that the field you are filtering on is indexed in the
source table.

If you're returning a lot of records, and it's just taking too long for
the list to be fully populated, you can force the list to be completely
populated imediately after you requery the combo box (or reset its
rowsource, if that's how you do it) by making a reference to its
ListCount property. For example,

Dim lngCount As Long

With Me.Combo1
.Requery
lngCount = .ListCount
End With

You don't have to do anything with the count, you just have to evaluate
the ListCount property -- that's enough to force the combo to fully
populate its list.