Lag on dropdown action, when values change?

[Larry]

I have a combobox which contains several different column names from a
table. When the user selects this column, I change the RowSource of
another combobox to select all the distinct values of that column from
the table. So that when the user clicks the dropdown, all the
appropriate values will be available.

The problem is, when they click the dropdown button on the value
combobox, there is a lag of 1 to 2 seconds, before the values are
displayed. This is causing the users to keep clicking the dropdown
arrow, thinking they did not click it the first time, so of course,
when it does dropdown, the second/third clicks will close it and open
it again or something. So it looks like it can't make up it's mind!

Anyway, further clicks on the dropdown button get instant action. But
if they then change the Column combobox to select another column, and
click the value dropdown again, it is slow again.

In the AfterUpdate of the Column combobox, I ReQuery the Value
combobox, thinking that it would load the data at that time. But it
appears that it doesn't actually load the data until the user clicks
the dropdown themselves. The following is the code I have in the
AfterUpdate event of the column combobox.

Me.cboValue.RowSourceType = "Table/Query"
strSQL = "SELECT DISTINCT " & strColumn & " " & _
"FROM " & QUERY_NAME & " " & _
"ORDER BY " & strColumn & ";"
Me.cboValue.RowSource = strSQL

Me.cboValue.Requery

I have thought about getting the Value dropdown to go ahead and
dropdown at the time it's ReQueried, but I think that would look ugly,
so haven't tried it yet. But I probably will, just to see if it helps.

Any ideas as to why this lag is there and what I can do to stop it?

Thanks,
Larry

 

[Dirk Goldgar]

I have a combobox which contains several different column names from a
table. When the user selects this column, I change the RowSource of
another combobox to select all the distinct values of that column from
the table. So that when the user clicks the dropdown, all the
appropriate values will be available.

The problem is, when they click the dropdown button on the value
combobox, there is a lag of 1 to 2 seconds, before the values are
displayed. This is causing the users to keep clicking the dropdown
arrow, thinking they did not click it the first time, so of course,
when it does dropdown, the second/third clicks will close it and open
it again or something. So it looks like it can't make up it's mind!

Anyway, further clicks on the dropdown button get instant action. But
if they then change the Column combobox to select another column, and
click the value dropdown again, it is slow again.

In the AfterUpdate of the Column combobox, I ReQuery the Value
combobox, thinking that it would load the data at that time. But it
appears that it doesn't actually load the data until the user clicks
the dropdown themselves. The following is the code I have in the
AfterUpdate event of the column combobox.

Me.cboValue.RowSourceType = "Table/Query"
strSQL = "SELECT DISTINCT " & strColumn & " " & _
"FROM " & QUERY_NAME & " " & _
"ORDER BY " & strColumn & ";"
Me.cboValue.RowSource = strSQL

Me.cboValue.Requery

I have thought about getting the Value dropdown to go ahead and
dropdown at the time it's ReQueried, but I think that would look ugly,
so haven't tried it yet. But I probably will, just to see if it helps.

Any ideas as to why this lag is there and what I can do to stop it?

For one thing, your code is requerying the combo twice. That's because
changing the rowsource automatically forces a requery. So you don't
need the line "Me.cboValue.Requery", and it's doubling your overhead.

Aside from that, it might speed up the query if all the selectable
columns of QUERY_NAME were indexed. That may or may not be feasible.

One last thing you might try, if the others don't do it for you, is to
interrogate the combo box's ListCount property immediately after setting
its rowsource; e.g.,

Dim lngCount As Long

strSQL = "SELECT DISTINCT " & strColumn & " " & _
"FROM " & QUERY_NAME & " " & _
"ORDER BY " & strColumn & ";"

Me.cboValue.RowSource = strSQL

lngCount = Me.cboValue.ListCount

The idea is that you don't actually need the count for anything, but
you'll force Access to load all the rows.

 

[Larry]

Thanks Dirk.

I removed the ReQuery and added the ListCount. Looks like ListCount was
what I needed. As soon as the ListCount line is hit, the combobox is
loaded and the user can't really even click on it until the load is
finished.

So I also added a label to tell the user that data is being retrieved
and I make that visible just before the ListCount, then invisible
afterwards. So they will know something is going on.