Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    I'm using a combo box with a large ListCount (>10,000 items). When I drag the scroll box to the bottom (to get to the end of the list), but it "pops" back to about half way down the list; I drag it again to the bottom and it gets a little closer, etc., etc. How do I get it to the bottom of the list the first time???

    I think I solved this problem before for a ListBox by using the ListCount property (presumably so that Access figures out how long the list is), but that doesn't seem to work here.

    Thanks for your help.

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

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    After opening the combo box, type Ctrl+End, then Ctrl+Home. This forces Access to find the last item

    But frankly, I don't think it is a very good idea to present the user with a combo box with that many items. A list box is a little bit better in such a situation. If possible, I would try to break down the selection process into a few steps. The first combo box or list box presents broad categories, the second one subcategories and so on. If you search this Forum, you will find many posts explaining how to limit the entries after each choice.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    HansV,

    Thanks for the tip. I agree - a long Combo List should be avoided. In this case I'm using the combo box to allow the user to select one of the unique values that the particular database field contains (another combo box is used to select the field they want to search). The recordset is then filtered on the value they select. It just so happens that this particular field does not have too many duplicate values so the list is long.

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

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    A combobox this long is *always* going to be a dog because anything over a thousand records is totally inefficient. Why not give them another method to filter the records *before* you offer them a combobox list? I've done this many times with an unbound textbox. They can type in as little as a single letter or number. Then when they tab to the combobox, you requery the combobox filtered by the value in the textbox. You can always do a DFirst() to see if there are any values like what was entered concatenated to an asterisk and let them know they've entered an invalid selection if no comparable value exists.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    I would have thought a list box with >10000 entries was unusable for selecting one entry. Personally I think more than ca 100 entries in the list will cripple the App on usability / performance.
    As Charlotte suggests filtering on a textbox entry is useful.
    IF the list has multiple columns you may want to allow filtering on more than one column entry.
    eg. list has persons name and town, let the user type part of towns name and part of persons name to reduce the entry count.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    Thanks for the advice and suggestions. Actually, the >10,000 entry ComboBox is quite "usable" in this particular application (except for the scrolling problem that was the original reason for this post). The user usually knows what he/she is looking for -- as long as auto-complete is turned on, typing a few characters will get you close -- then dropping down the list will let you see what's nearby.

    No argument here though: if you JUST used the scroll controls to find the entry of interest in a big list like this, it would get REAL cumbersome REAL fast.

    Finally, the performance seems to be just fine, even for a big list (redefining the recordsource, requerying it, scroll speed -- if you choose to scroll, etc. all work very nicely). The only hitch -- scrolling to the bottom by dragging the scroll box...

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

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    To get back to original question, not sure why ListCount property doesn't work. What method are you using to get ListCount that isn't working? Tested this with simple form based on table with 50,000 records & combo box based on same table without any filters applied. Combo is used to navigate to new record. Used this code for form On Load event:
    <pre> Dim lngCount As Long
    lngCount = Me.Combo2.ListCount</pre>

    Other than slight delay when form opens, this works fine. The combo list is "proportional" to vertical scrollbar when you first open form with all 50,000 + items listed. It quickly navigates to selected record. Note the max limit for a combo box is 2^16 or 65536 items. To avoid delay in opening form above code can be moved to combo On Enter event. There was little perceptible delay using combo On Enter so this may be preferable to form On Load.

    Note: This is tested on local database, not on network which would tend to decrease performance. Combo recordsource is a saved SELECT query, optimized by running at least once after saving. All fields used in combo box are indexed. Note when tested same query as SELECT DISTINCT there was perceptible delay, at least when query ran first time, compared to SELECT. Once query was optimized delay was less noticeable. If not using saved query to populate combo this may be an issue.

    While I agree with caveats posted by others that you should try to limit size of combo list in first place, in past sometimes had to work with very large tables (100,000 records plus) and it was possible to create efficient forms, combos, etc by keeping things simple and ensuring tables were properly indexed. However, this data was being used for analytical purposes only, not being updated, so performance was reasonable even on network. If your case involves updates by multiple users which tends to slow everything down then I agree you should try to limit or filter list if possible using some logical criteria. Caveats aside, I'm just curious why ListCount isn't working.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scrolling in a Long Combo Box List (A2K 9.0.4402 SR-1)

    I dynamically change the recordsource for the ComboBox (depending on selections made in other fields). Then I use <font face="Georgia">Me!Combo.Requery</font face=georgia> and then, similar to your code, <font face="Georgia">lngCount = Me!Combo.ListCount</font face=georgia>.

    It's encouraging that it does work for you, at least -- I'll futz around with it some more to see if I can get it to behave. I'll let you know if I find out what I'm doing wrong.

    Thanks for the additional info (including the maximum list size).

Posting Permissions

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