combo box scroll locks

[Cynthia]

I have an access db where all my tables are linked to sql. In one of my
forms I have a combo box that is accessing around 2000 records. Sometimes
when a user scrolls through these records it locks up the project on sql. Is
there a better way to access my information? I was told that with the scroll
bar it keeps reaccessing portions of the data from sql and this task puts a
load on the server.
Would I be better off copying the data to a temp table when they open this
form?
Would a list box function any better?
Any help would be greatly appreciated.

 

[Baz]

The obvious thing is not to have a combo box with 2000 rows. Not a good
idea at all. You probably want to build some kind of custom search form.

 

[Cynthia]

I have that many pieces of equipment to choose from, how would a custom
search form help?

 

[Baz]

Instead of forcing the user to scroll through 2000 rows (which, as you have
discovered, is a poor idea technically as well as in terms of user
ergonomics) you could pop up a little search form that allows them to enter
search criteria to find the piece of equipment they are looking for.

 

[Cynthia]

Thank you for the help on this. Originally I thought it quicker, the way I
had because as the started typing it took them to the equiment they may want
and they could see what was left.
Do you see a problem with:
1. A command button on the main form for selecting equipment
2. A popup box where the enter search criteria i.e. Equipment starts with
3. Then, would you add a second form that has the filter they selected,
then when they select something it passes that number back to the main form.
Or would you have the filter go directly to the original combo box?

My concern is I have two combo boxes with the same control source. Reason
is if it is equipment I want the id of equipment filled in, if it is
instruments I want the id of instruments, and equipment and instruments come
from different tables.

Maybe answered my own question, I could have the filter also have a select
for if it is equipment or instrumentation.

Thank you again for you advice.
Cynthia