Combo Box Search Time

[BWD]

Hello Group,

I have a combo box that I currently use with the Auto Expand feature
on. This combo box points to a field in a query with 400,000
records. The box works fine but has about a 30 second latency delay
from the time it gets focus to the time where it will display the
records. I would like to cut this latency down to as little as
possible.

As a side note, I have 2 forms each with a combo box pointing to 2
different queries each with 400K records. The first combo box is a
zip code lookup and works super fast; the moment you click it the
results pop right up. The second combo box is the one that is giving
me a problem, this one points to a text field with a 120 character
field size. Perhaps this has something to do with it?

Any insight appreciated.

 

[Klatuu]

Here is a link to some info that will help

http://allenbrowne.com/ser-32.html

 

[John W. Vinson]

Hello Group,

I have a combo box that I currently use with the Auto Expand feature
on. This combo box points to a field in a query with 400,000
records. The box works fine but has about a 30 second latency delay
from the time it gets focus to the time where it will display the
records. I would like to cut this latency down to as little as
possible.

As a side note, I have 2 forms each with a combo box pointing to 2
different queries each with 400K records. The first combo box is a
zip code lookup and works super fast; the moment you click it the
results pop right up. The second combo box is the one that is giving
me a problem, this one points to a text field with a 120 character
field size. Perhaps this has something to do with it?

Any insight appreciated.

A Combo Box is limited to 65536 rows... and anything over 10000 or so is
approaching user abuse, in my opinion!! Is there any way you can prefilter the
records for this combo's row source? For instance, for a national zipcode
table, it would make sense to let the user select a state (or even a smaller
geographical area) and present a zipcode based on a query selecting only zips
in that state.

A 120 character field will of necessity be slower than a 5-character zip...
but have you indexed the field in the table, or taken other steps to optimize
the query?

John W. Vinson [MVP]

 

[BWD]

Here is a link to some info that will help

http://allenbrowne.com/ser-32.html
--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

Thank you for the link. I will gice it a try and repost to let the
group know how it works out.

Happy New Year all!!!

 

[BWD]

Thank you for the link. I will gice it a try and repost to let the
group know how it works out.

Happy New Year all!!!- Hide quoted text -

- Show quoted text -

I am writing back to reply to both John and Klatuu, first John.

John, I got the zip code combo box to look at 400,000+ zip records
with no issues; super fast and slick. Yes the fields are indexed. I
think the issue is with the 2nd field name that I am trying to
reference the same way, that field is a text field with 120 character
length. It works just fine right after a 15 second load period. I can
live with the load time but I would prefer to work around it if
possible.

Klatuu, I tried your module but the form doesn't seem to be calling
the function correctly. I have the data source set to blank and the
forms "On Current" event set to call the function created. I put a
breakpoint in the modules code so that I could verify that it was
working but it never gets called. Instead the combo box errors out
and says "Record not found".

Any thoughts?

Thanks again.