Combobox List Limit or something else

[Ian Mac]

All,

Is there a limit to the number of items in a Combobox List?

I ask because I have a Combobox which is querying 200,000+ records from the Main table in my Database.
Today, I typed in a value which is in the original Main table but I had no record returned.
I then changed the Query to Assending on the field and it returned the record.
With the Query Assending the value would be far nearer the top of the list.
A note to this is, when the I hadn't changed the sort and I entered the value the list has 8-10 blank values?

I can't use the Assending query as it is FAR too expensive in terms of speed.

I haven't seen this before and was wondering what might be the problem.

Cheers,

 

[Norie]

Ian

I don't know if there is a limit but I would think that having a combobox with that many values might be pushing things a bit.

Why not try opening the table, sorting it as required and then saving it? That should mean that you wouldn't need to sort in the query the combobox is based on.

Is there no other way to implement what you are using the combobox for?

As to the blank values, are there actually blank values in the table?

 

[Ian Mac]

Ian

I don't know if there is a limit but I would think that having a combobox with that many values might be pushing things a bit.

Why not try opening the table, sorting it as required and then saving it? That should mean that you wouldn't need to sort in the query the combobox is based on.

Is there no other way to implement what you are using the combobox for?

As to the blank values, are there actually blank values in the table?

Hi,

I'm actually going with the Combobox anymore anyway, I'm purely interested in why it's happening.

BTW, this is Access 97, forgot to mention that.

Trouble with sorting the original table is:
a) it still runs a Query on the sort part
b) it doesn't sort the list in the Query for the Combobox??

Also, the table has no Blank values in the field being looked at.
Its all a bit baffling.

The real reason for the Combobox was to LimittoList a quick and dirty way for me to stop any additional values being input, I decided to not be lazy, but would like to find out more about the above.

Cheers,

 

[Corticus]

Yes, the combobox definitely has a row limit, I believe it's an appropriate 65,536 rows.

What I've done when this shortcoming arises, is to add a means of filtering the data before it goes to the combo. Say your combo is holding names, then you could add a text box that allows you to type in the first few letters of the name, and then filters the values that will go into the combo based on these first few letter. I've also add 26 little letter buttons, A-Z, and when one is clicked, the combo only shows names that start with that letter. The latter method was a pain.

The idea, though, is to pre-filter the list. This will help performance, too.

 

[Ian Mac]

Cheers,

Figured it might be the old 65,536, how annoying, also annoying for exporting the data, have ways around it but they are ways around it.

Filtering the list was taking ages too! The linked tables aren't work very well across our network.

I was using it for e.g. XX123456, JY123472 etc. bit of a bind as XX might only have 34 records and JY 80K

I'm just using the Texbox now as it works better for my circumstances. It's very quick at the minute but I can add a progress bar to it when the data expands, so the people who input think it's really flash

Cheers