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

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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:

    Dim lngCount As Long
    lngCount = Me.Combo2.ListCount

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.

Reply | Quote

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

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

Reply | Quote