Combo takes too long to load and show

  • Thread starter Thread starter Ronin \(theLost\)
  • Start date Start date
R

Ronin \(theLost\)

I have a combobox in a form. Its values are taken from a table(*) in a
dynamic manner (depending on previous selections). The problem: my combobox
has hundreds or thousands of values, and takes a long time to load and
display. Is there any trick to speed up the process?

(*) Table fields are like: Index(Key), Name, Field2, Field3, Field4, etc.
Query is like "SELECT * ... WHERE Field2=something ORDERED BY Name"
Combobox shows field "Name"
 
First thing - hopefully you really don't have a fields named "Name" and
"Index". Using Access keywords and common property names creates all sorts
of potential for problems.

There are two ways that I use to speed up combos - the first (which it
sounds like you are already doing) is to create one or more other controls
that limit the number of values returned in the combo's rowsource.

The other way is to create a change event for the combo. This event will
modify the rowsource of the combo so that it filters the rows based on the
characters currently typed into the combo. It uses the Text property of the
control which contains whatever is currently typed into the control. Note
that the Text property is not the same as the VALUE property (which is the
default property of many controls). Lets say you are storing Custid and the
field you are displaying is CustName:

Private Sub Text6_Change()
Const strBaseSQL As String = "Select Custid, Custname from Customer "
Me.txtSQL = strBaseSQL & "Where Custname like """ _
& Me.Text6.Text & "*"" Order by Custname;"
Me.cboMyCombo.RowSource = Me.txtSQL
End Sub
 
Back
Top