Combo box on form in Access 2000

  • Thread starter Thread starter W Depow
  • Start date Start date
W

W Depow

I have a database with 370,000 records in the current
table. I have a form based on the table. On the form I
have a combo box with a drop-down list. When I type in the
last name in the combo box, the drop-down list should show
20 records starting with the name I typed in and I should
be able to scroll to the end of the list. Through all
370,000 records. I can do this, only if I do not sort
ascending by last name, first name, middle name. If I set
the underlying query to sort alphabetically by name the
drop-down list in the Combo Box will only show to a max of
65,535 records.
Ex. If I type Depow in the combo box and click on the drop-
down list arrow, the last record in the list is Coburn. I
can't go any further. Coburn is record # 65,535.
How can I correct this problem?
I have had 30,000 records before and did not have this
problem.
 
64K rows is a huge number of rows for the user to go through. Remember that
the data for these rows will need to be transmitted through the network (not
at the same time, luckily) which will slow your database.

A work-around is to use a set of cascaded ComboBoxes. For example, the user
can select one of the 26 characters in the alphabet in the first ComboBox
and the second ComboBox will be populated with only names starting with the
character selected in the first ComboBox.

See code sample in The Access Web:

http://www.mvps.org/access/forms/frm0028.htm
 
I agree with Van, but would also add that I question the
use of Access for a database of that size. Sounds like you
should upsize to SQL Server or something similar that can
handle that type of volume better than Access can.
 
Thanks for a solution Van and Anonymous. I will try the
cascade. The database is not used currently on a network
so everything else including reports and querries work
fast. If I change to SQL will the database resemble the
access database and be easy to use.I like access as it is
easy to use as I am not a programmer. Thanks again.
 
Generally, yes.

It should be noted that "Access" has 2 *main* components: Access itself
which is the GUI Design application and the database engine known as JET.
If you want to use another database engine such as MS-SQL, you simply link
the Tables from MS-SQL to your Access Front-End (the GUI part) using ODBC.

There is also an option using Access Project (ADP/ADE file) rather than
Access Database (MDB/ADE file) but Access Project option will be a lot more
work for you.

Note that a license for MS-SQL server (or other equivalent database
engine)costs reasonable sum of money so check it out first. If there are
not too many users, JET is fine since JET database file can be up to 2 GB
in theory. However, I work on a safe limit of 500 MB.
 
Back
Top