How much data storage limit a combo box had in Access (1 Viewer)

hrdpgajjar

Registered User.
Local time
Today, 18:41
Joined
Sep 24, 2019
Messages
51
Hi all,
I've created my company data base on Access. Where there are two fields under customer table. 1. Village 2. Taluka

I've put two combobox in my data entry form. 1. Villagecombo and 2. Talukacombo and put property settings to "Limit to list" and "Allow list edit on"

but after some entries, value list not able to add new data. and always asks for add new value box even I enter previously saved value there.


So my question is, "Is there any limit to store data in combo boxes ? "

thanks
 

isladogs

MVP / VIP
Local time
Today, 13:11
Joined
Jan 14, 2017
Messages
18,186
In answer to your question...no.
However, when you have several hundred / thousands rows it will become too unwieldy to use.
Try using a table/query as your row source instead of a value list.
 

hrdpgajjar

Registered User.
Local time
Today, 18:41
Joined
Sep 24, 2019
Messages
51
In answer to your question...no.
However, when you have several hundred / thousands rows it will become too unwieldy to use.
Try using a table/query as your row source instead of a value list.
Thanks for the prompt reply. Can you guide me how can I add a query to a special table which saves all my combo box data ? Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:11
Joined
Feb 28, 2001
Messages
27,001
@isladogs - Colin, I have to disagree with you in one limited sense. I think there HAS to be a limit to the number of items in a combo box value list because Access has to be able to address them. If you look at the way a combo box works, you have a RowSource and a RowSourceType. If you made the type "Query" then the RowSource, like ALL queries, would be limited to 65,536 characters. I think the same size limit applies when you have the type set for ValueList.

@hrdpgajjar - If you create the list within a table with a key field and the desired string value, you can create a combo box with one of the form-based wizards very easily. If the table exists already, you just name it when the combo wizard asks for the data source. It then looks up the properties of the table and helps you build it.

Adding a new value to a table-based list gets a little trickier, though. In your NotInList event, you need to add the new value by executing an INSERT INTO query to append a (single) new record to the table, then requery the combo box. There are articles in this forum for the "Not In List" event that you can look up that would give you some examples, I think.
 

isladogs

MVP / VIP
Local time
Today, 13:11
Joined
Jan 14, 2017
Messages
18,186
Hi @Doc
I'll be happy to amend that to 'effectively no limit' in terms of what is actually usable in practice.

I checked in Access specifications and it includes these two items
1610095742114.png

In fact many limits in the list are out of date and the actual limits have since increased e.g. Number of objects in the lifetime of form or report is now far higher than 754 depending on version in use. The 32750 character limit may or may not still apply and I have no intention of testing it with a value list. However , purely for the purpose of this answer, I have tested loading a 2.6 million record query as the row source in a combo and a listbox. The listbox loads instantly. The combo is totally unwieldy taking ages to dropdown. However whilst totally impractical to use, both would work.
Using a table/query with a reasonable number of records is normally easier to setup/manage/edit than a value list
 

hrdpgajjar

Registered User.
Local time
Today, 18:41
Joined
Sep 24, 2019
Messages
51
@isladogs - Colin, I have to disagree with you in one limited sense. I think there HAS to be a limit to the number of items in a combo box value list because Access has to be able to address them. If you look at the way a combo box works, you have a RowSource and a RowSourceType. If you made the type "Query" then the RowSource, like ALL queries, would be limited to 65,536 characters. I think the same size limit applies when you have the type set for ValueList.

@hrdpgajjar - If you create the list within a table with a key field and the desired string value, you can create a combo box with one of the form-based wizards very easily. If the table exists already, you just name it when the combo wizard asks for the data source. It then looks up the properties of the table and helps you build it.

Adding a new value to a table-based list gets a little trickier, though. In your NotInList event, you need to add the new value by executing an INSERT INTO query to append a (single) new record to the table, then requery the combo box. There are articles in this forum for the "Not In List" event that you can look up that would give you some examples, I think.
thanks will try definitely
 

Users who are viewing this thread

Top Bottom