Combo Box List Rows limit

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,720
I have a Combo Box that has 6 columns

The source of the data for the list is huge. There is over 30k rows

SELECT [ModelGeneral_vluItem].[TotalLeadtimeDays], [ModelGeneral_vluItem].[ItemID], [ModelGeneral_vluItem].[ItemDescription], [ModelGeneral_vluItem].[LastRevisionID], [ModelGeneral_vluItem].[Commodity], [ModelGeneral_vluItem].[MakeorBuyCode] FROM ModelGeneral_vluItem ORDER BY [ItemID];

when the user makes a selection I want to populate another field on the form using data from the 6 columns in the combo box

So in my other field I put the following formula: =[Combo1020].[Column](2)

It works great

UNLESS they choose an item way down the list (lest say Row 1001). Then my other field doesn't populate. It appears that if they do not select an item that is within the first 255 rows (which is the max number you can put in the "List Rows") the formula above doesn twork.

Is there another way to do this?

I have the User select an Item number from the list in the Combo box. I want another field on the form to automatically put the items description. Like a Vlookup would do in Excel.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
I'd say in general combo boxes with 30,000 rows probably violate some basic principle of UI design. Probably the one that says "never put 30,000 rows in a combo box". You're going to have to find a better way. Maybe give them a subform view of a table where they can select, filter, sort or whatever they want to do to find the record that they are looking for.
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,720
If I create a subform using a query, can I put a Checkbox in one of the Query fields? And then based on if that check box is checked, store that data onto another form?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
You could. You could also just work with the "selected" row. Or use the data without moving it to other forms. There are usually a variety of options. UI design is not easy in my opinion but it is important to keep stretching and trying to find simple effective solutions that minimize the demand on users and also work naturally based on what users are used to seeing in other similar applications. Trial and error is important in figuring out how to get things to work.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
Agree that a combo with 30,000 rows indicates other issues. However, regarding
255 rows (which is the max number you can put in the "List Rows")
fairly certain this applies to the maximum rows that can be displayed without scroll bar regardless of how much form room you have. After that, you get scrollbar. Access options has row setting for "do not return lists with more than n rows" and the default is 1000, I believe. It is interesting that you state there is an issue at row 1001.

Surely you can do something to reduce the list for this combo? Preceding cascading combo(s)?
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
I have a combo in one of my DB's with just over 4000 entries. Works just fine?
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,814
Access options has row setting for "do not return lists with more than n rows" and the default is 1000,
then i guess your limit is set higher? I dunno, can't recall ever having a combo list with that many rows so that option hasn't applied to me yet.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,840
Messages
5,446,819
Members
405,417
Latest member
egrospe17

This Week's Hot Topics

Top