Combo Box List Rows limit

[gheyman]

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.

 

[xenou]

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]

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]

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]

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)?

 

[welshgasman]

I have a combo in one of my DB's with just over 4000 entries. Works just fine?

 

[Micron]

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.