Results 1 to 11 of 11
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Speeding Up Multi Select Listbox (A2K SR1)

    While using a multi-select listbox in a database, users want the ability to select all parameters in the listbox. The listbox recordsource is controlled by other filters the users set to limit amount of data. The users also want the ability to select all parameters. (Here is where the problem lies). Allowing the users to select all Parameters, makes about 3,000 entries available in the listbox. To make it easier to then select all parameters, I have a select all button with the following code:

    <pre> Dim X As Integer
    For X = 0 To Me.SELECTWBS.ListCount
    Me.SELECTWBS.Selected(X) = True
    Next X
    </pre>




    This works great unless the users want to use all of the data. When 3,000 entries are selected, it takes about 90 seconds or so for the code to select all of the items.

    Any suggestions on how I can make the multiselect listbox run faster other then making the dataset smaller.
    Regards,

    Gary
    (It's been a while!)

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    Gary,

    Can you add one more parameter in the list that says 'All Parms' (Place on top) and if this is selected then do the 'Everything Selected Process'.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    List and comboboxes perform optimally with up to 100 list items. Even simple ones start to seriously degrade at 1000 items. Why not just gray out the listbox if they click on an All Parameters radio button or check box and build the list in code instead of trying to mark every item as selected in the list itself and then looping through that same collection. I don't understand how you could possibly build a query with 3000 parameters anyhow, so you might want to rethink the design and use a treeview or a datagrid control instead.
    Charlotte

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    Charlotte,

    Thanks for the information. Other then this one multiselect listbox, all of the queries are being driven via code. There are several multiselect listboxes acting as filters that reduce the amount of data the users need and code then builds the queries. For example, the users first select a project, then subproject, cost type, funding type, WBS Level and finally WBS elements to run reports on this data. This normally reduces the WBS selection to somewhere between 2 and 100 elements. I was a bit lazy on the this multiselect listbox as the number of elements being processed were low. Recently, the users requested for the ability to select ALL projects and ALL WBS elements which caused the multi-select listbox to swell to the 3000 elements.

    I was going the way of writing code to do this instead of the process shown, however, just wanted to check to see if there was a quicker solution.

    Your comment "I don't see how you could build a query with 3000 parameters" makes perfect sense to me, however, users are another story. They said they want the ability to see all the detailed data for each WBS should they want to. This only produces a report about 300 pages but who am I to argue. (Job security)
    Regards,

    Gary
    (It's been a while!)

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

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    Don's suggestion is an approach I frequently use in lists, but I don't think your users want to select ALL the items, they just want to be able to see them, right? Usually you only have a multiselect option on one side--either they select all items on the left and one on the right or vice versa. Dealing with anything else is a nightmare.
    Charlotte

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    Charlotte,

    I use Don's suggestion as well in various databases. Unfortunately sometimes they just want to see "all" of the items and sometimes they actually select all of them as well.

    The recordset of the Select WBS Multi-Select Listbox is updated each time other selections are made in each other multi-select listbox, checkbox, etc. The users like this since they can see the data changing on the fly while making data selections. I have used the treeview in other databases, but for this database, they like this approach. I do allow users to select <=, =, or >= a level to simulate a treeview like system.
    The attached zip file shows the data selection page should you be interested.

    Thanks for the help. I will write some code that processes data differently should the "Select All WBS" button be pressed to speed up the system.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    Don,

    Thanks for the suggestion. I have done this in other databases but our users like the "Select All" button.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    You're welcome Gary,

    Just another possible suggestion. Can you disable the list if they Click the 'Select All' option. This will make the list grey and then run the All Select Process. At the same time toggle the Select All button to a Deselect All Button and re-enable the list for selection.

    If I come across another option I'll pass it on.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    Don,

    Thanks again. I am already doing this such that the all of the items selected including reports either become disabled or change colors based on selections made.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    I did a presentation last night on Access performance, and I'm starting to think that some of this is sort urban legends, or at least things have changed over time. I did an example on a continuous form with 154K records, and a combo doing a lookup on a table with 52K records, and the response time was quite reasonable, both in displaying records, and in filling in the combo box. Now I must confess that I had good hardware (1.6GHz Centrino processor with 1.2GB of RAM), but quite honestly, the delays in presenting data or changing values in the combo box were basically imperceptible. I also ran some studies on a much slower processor with only 64MB of RAM, and the delays were then noticable, but not totally unworkable. I was amazed at how well it worked - so were mamy in the audience of 35 or so. So I think things have changed to some extent, and as long as workstations are robust, and you have a robust LAN, you may be able to get away with things you couldn't a few years ago. FWIW!
    Wendell

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

    Re: Speeding Up Multi Select Listbox (A2K SR1)

    It has a lot to do with how you populate them, but it also is affected by whether those thousands of items are being loaded from a local table or one on a server. Dragging that kind of traffic across a network is problematic in my experience with normal workstations and a pro operating system. The hardware now is much faster, but the limit on the number of rows a combo or listbox can handle is still in the specs somewhere. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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