Results 1 to 7 of 7

Thread: Combo Box List Rows limit

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combo Box List Rows limit

    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.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,362
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combo Box List Rows limit

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,563
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combo Box List Rows limit

    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?
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,362
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combo Box List Rows limit

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,650
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combo Box List Rows limit

    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 by Micron; Jun 14th, 2019 at 11:00 PM. Reason: clarification
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  6. #6
    Board Regular
    Join Date
    May 2013
    Posts
    602
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combo Box List Rows limit

    I have a combo in one of my DB's with just over 4000 entries. Works just fine?
    Office 2007
    Access novice. Sometimes trying to give something back

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,650
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combo Box List Rows limit

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •