Limit to Records in a drop down box?

[Chuck W]

Hello,
I have a query called qrySelectAccNum with two fields. AcctNum and
PatientName. There are 88,105 records that the query returns. The query is
sorted in Acc order by AccNum. I created a form called frmAbstraction with a
drop down box called cmdSelectAccNum. It is based on the qrySelectAccNum.
For some reason, when a user is using this drop down, the first 70,345
records are showing. The remaining 17,760 records do not show. The drop
down remains sorted by AccNum. Is there a limit to the number of records
that appear in a drop down (I'm using Access 2003)?

Thanks,

Chuck

 

[Jeff Boyce]

Chuck

A combobox ('drop-down') displaying more than a 100 or so rows can be
user-unfriendly. One with thousand (or tens of thousands) is downright
user-surly <g>.

As an alternative approach (and one that performs much better), take a look
and Allen Browne's trick for handling "thousands of rows" -- basically, his
code holds off 'feeding' the combobox until after the user has entered the
first 'n' characters. This way, the number of rows that match what the user
has already entered is considerably fewer. See:

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

and yes there is a limit on the number of rows in a combobox or listbox
(65K is the limit).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================