Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Combo box > 65000 entries (Access2000/SP2)

    I have seen this problem in the past. I have a table of addresses and suburbs that has 99000 entries, but when I set the source of a combo box to this table it only accesses 65000 records. Is there a way around this?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo box > 65000 entries (Access2000/SP2)

    I believe you've hit the limit on combobox rows, but I'll be dipped if I can find the reference anywhere at the moment.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Combo box > 65000 entries (Access2000/SP2)

    The actual limit is 65536 (2 ^ 16) rows. The list would have to be filtered or otherwise limited to less than this number. See MSKB 207601:

    ACC2000: Combo Boxes Limited to 65536 (64 KB) Rows

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box > 65000 entries (Access2000/SP2)

    The list box and combo box can contain a maximum of 2^16 = 65536 items, because internally, a 16 bit pointer to the items is used.

    A combo box with 99,000 entries would be extremely slow, and unpleasant to use. I would use cascading combo boxes instead, by splitting the list up into categories, subcategories etc. - as many levels as needed. For example, you could let the user select a state first, etc. Or you could let them select the first letter from the first combo box, etc.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo box > 65000 entries (Access2000/SP2)

    Actually it's reasonably fast with the limited 65,536 records so that was not a problem.

    This table holds every streetname in Australia and it's corresponding suburb, so there are duplicate streetnames.

    I solved the problem by creating a query of Distinct streetnames, this reduced the list to 53,500 records.

    However, if I was designing the database from scratch, I would have had the table being built up with the NotInList event, that way I doubt if we would have reached 2,000 records in that table as the number of records in the main table where the Street and Suburb are held numbers 3,450 records.

    As always, thanks for your input, guys.

Posting Permissions

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