Combo Box Limited to 36000 records or so

Question

Combo Box Limited to 36000 records or so

Claudia-343424

Ten Centuries

Points: 1080
More actions
December 4, 2006 at 10:35 pm

#76625

Hi Experts,
anyone knows how to avoid the limit to 36000 records that access has with a combo box?
Any help will be appreciated.
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i typically use a Msflexgrid or a vsFlexGrid (3rd part component) in order to display data that exceeds 3K in records, because of the performance and record number limitations that comboboxes,treeeviews,listviews have; i think thye are limited to 32767 records, and anything above 3000 records has a noticable loading delay as items are added to them. Try using the MSFlexGrid, because it is a grid, it is also more readable, and you can detect multiple rows selected and such.

even excel cannot display more than 65535 rows.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CodeLake SSCrazy Points: 2082 More actions · Answer 2

Hi,

Even though this seems like a blunt reply, 3K items in a combobox is a nasty user interface design. You should provide some sort of filtering to reduce the number of items. This way your design will be more robust to future addings to the combobox items as they are always somehow filtered and there are no restrictions to adding them.

Hope this make sense

Kindest Regards,

WRACK
CodeLake

John McC Hall of Fame Points: 3410 More actions · Answer 3

At least until version 11 (1,000,000 rows)

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

Claudia,

The answer is that you can't. Access controls are actually the older versions of Windows controls. In this case the ComboBox is the same control that we Visual Basic 6 developers were stuck with. These controls use a Short (2 byte integer) for the list index. [OK, I know it's actually a UShort but you can't use the other 32767 number because the interface chokes. I know because I've tried it.]

I encourage you to move your data to SQL server and write your user interface in something like VB .Net. With "Click Once" deployment my headaches have gone down. When a user logs into the app there is a delay (2 to 9 seconds) to check for an update.

ATBCharles Kincaid

DaveItz SSCrazy Points: 2146 More actions · Answer 5

Do you still come across this limit when you populate the Row Source Type with a callback function?

You could return a smaller chunk of the data but still have all the data available with filtering stored recordsets that come from quick querying (like with an autocomplete) and stay below the threshold.

jeremy nyc Valued Member Points: 53 More actions · Answer 6

Don't rewrite your entire application because one combo box has too many rows in it!

The smart way to go about this, as someone else alluded to, is to filter the rows shown in your combo box to a more reasonable, and humanly readable, number.

One common way to do this is to put code in the change event that makes sure that you're not showing any rows until the user has typed three letters. That usually cuts down the list to a manageable amount of rows. Groups.google.com will show a lot of code samples for this method in comp.databases.ms-access.

JeremyNYC

Claudia-343424 Ten Centuries Points: 1080 More actions · Answer 7

Thank you for all your answer.

I cannot move to vb.net, this is a small piece of a HUGE program.... and it has SQL Server as back end, but this doesn't really matter. I could rewrite only this user interface, but really this is the only thing that need to be fixed.

Before posting this question I've already tried to write a code that populate the combox after typing the first X char but it doesn't work when the user cancel what he/she has already written using CANC button.

JeremyNYC: If you have and example (a link) that works, I mean, after typing X chars reload the combo box it will be great!

jeremy nyc Valued Member Points: 53 More actions · Answer 8

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

Allen Browne is one of the CDMA hotshots. I haven't used his code, but I'm sure for this, but I'm sure it's quite good.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 9

I usually go to

http://www.accessmonster.com

when I have problems like this. It is not that we can not help here, we would try, but they do have more expertise.

Regards,Yelena Varsha

Claudia-343424 Ten Centuries Points: 1080 More actions · Answer 10

Not sure if I am not able to implement the script suggested by Jeremy but it doesn't work properly. I'll tryaccessmonster.com thanks.

jeremy nyc Valued Member Points: 53 More actions · Answer 11

Claudia,

I really suggest you use comp.databases.ms-access. The audience is much broader, as is the expertise.

Jeremy

DKeeling Old Hand Points: 385 More actions · Answer 12

Hello,

I've just linked an SQL table with 49,000 records to an Access 2000 db, created a query, set up a form with a combo box using the query and I can see all 49,000 records. Is this an access 97 only problem?

Dennis