Combo box begining row in dropdown

[Scoop]

I have a combo box in a for that is used for jumping to a particular
record when it is selected. The form is for managing customer
information and the combo box is bound to CustomerID and displays
AccountNumber (as it is more familiar and user friendly). The customer
table is fairly large...> 300,000 records.

When you start typing the AccountNumber and then click the drop down
for the combo box, a certain number of records are displayed and the
user can scroll down to select one in the list.

I don't know exactly how many records the combo box lists or where to
find/adjust this property. The problem I am having is that the combo
box only lists account numbers between "A" and "D" so if you type
"DAV..." you are able to see all the records that begin with "DAV" but
if you type "SMI..." or anything beyond "D", the list does not adjust
downward so you are not able to view records that begin with letters
you are typing when they are beyond the records that are being
displayed.

I checked to be sure the row source doesn't have "max record" count in
the query and I figure I could probably requery the row source on the
OnChange event but it seems that there should be an easier way of
doing this.

Any ideas?

 

[aaron.kempf]

Uhm.. Are you using ADP?

Are you using a FILTER to display the records you want?

With 300,000 contacts; when you change what you want to search for--
you should send a new query to the db server.
It sounds to me like you're trying to filter or something; and there
are certain places where you can have a limit to the # of rows that
you see.

-Aaron

 

[aaron.kempf]

re: I figure I could probably requery the row source on the
OnChange event but it seems that there should be an easier way of
doing this.


Yes-- this is _COMPLETELY_ what you need to do

 

[BruceM]

Here is a link to a technique for managing large numbers of records in a
combo box row source:
http://allenbrowne.com/ser-32.html

 

[BruceM]

By the way, you are on the right track with requerying on the Change event,
except that Allen's system uses the Change event to set the row source based
on the first three (or whatever) letters you type into the combo box.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImproveFormPerformance.mdb" which might give you a way to
make your form more efficient. You could replace the listbox in my sample
with a combo, which would give you the type-in abilities.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Scoop]

I don't have a filter so it seems as though it is something inherent
in Access that is limiting the number of rows that the user can scroll
through.

Thanks for the link and feedback, I will try out the suggestions and
report back.