Results 1 to 8 of 8
  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

    Multiple MultiSelect List boxes (A2K SR1)

    Looking for suggestions on how to use several multiselect listboxes on one form against one table of data to create a resultant recordset to report against.

    I have a table that contains labor distribution data such as hours, employee id, year, month, department, type, etc.

    Users want the ability to select one or many employee ids, years, month, departments, types, etc and be able to see the resulting dataset in a report format.

    I originally did this with Six Single Select Listboxes on a form and a control button that launched numerous queries. Works great, however, the users now want the multi-select capability.

    Any recommended approach to this.

    I figured I could create a recordset to loop through the data to first get all years selected and populate a new temporary table. Then use a new recordset to loop through the first temp table to get months, populate a second temp table, and continue like this until all selections are complete and report off of the final temp table. This would require using many temp tables and use a lot of real estate. I have never tried nesting recordsets and dont know if its possible (although I don't see why they wouldn't be). In either event, is this the way to go or would you suggest a different way to do this.

    Just looking for ideas.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Multiple MultiSelect List boxes (A2K SR1)

    What I would do is to build a query (to be used ultimately by the report) on the fly from the 'Selected' items on each multi select list box.
    In this way there is not need for a temporary table, but then again I may be way off beam here.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    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: Multiple MultiSelect List boxes (A2K SR1)

    Thanks Patt,

    Here is what I ultimately decided to do.

    I created one table (Table1) to store all of the parameters selected for all of the multiselect listboxes. (One column of data for each listbox)

    After making all of the multiselect listbox selections, code does the following:
    - Deletes all data in Table 1(Prior multiselect listbox selections)
    - For each multiselect listbox, loops through the data and updates the selections to the appropriate column in table 1
    - Table 1 is then joined to the dataset in a query to get the answer. (Actually Table 1 is joined as six different tables, one for each link to the required data, to get the resultant set.)

    I hope someone understands this because I sure don't when I read it.

    Is this how you would build a query on the fly from the selected items??
    Regards,

    Gary
    (It's been a while!)

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

    Re: Multiple MultiSelect List boxes (A2K SR1)

    That's one way to do it, in fact there's nothing wrong it. What happens when you don't select anything from any of the listboxes, do you get nothing? And how do you select the "all" condition?

    I was thinking more along the lines of building a query based upon what was chosen in the listboxes.
    The WHERE clause is what is effectively being built 'on the fly'. If you don't select anything then that is the "all" condition.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple MultiSelect List boxes (A2K SR1)

    Hi

    Attached is a sample DB (Access 97) that uses both methods using a single list box, but could be expanded for multiple list boxes. Can't remember who provided me with the code for the multi select query, although it was here and has been posted a few times on the forum. It is very handy code.

    Not sure how much help it will be.

    Regards
    WTH
    Attached Files Attached Files

  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: Multiple MultiSelect List boxes (A2K SR1)

    Patt,

    - The code checks to see if nothing is selected and provides a message to make a selection.
    - At to the all condition, I typically place buttons with each listbox that says select all. In this case, I will just make the user highlight everything until they complain and ask for the select all button.

    - I will look at building the where clause of a query based upon what was chosen in the listboxes (Didn't think of doing it this way). I find the other way easier as it is more visual.

    Thanks for the information.
    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: Multiple MultiSelect List boxes (A2K SR1)

    Thanks for the example.

    Upon reviewing it, it has given me some more ideas.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Multiple MultiSelect List boxes (A2K SR1)

    Hi Gary

    When I said to create the WHERE clause on the fly I meant to build the entire query on the fly, then use this SQL as part of a CreateQueryDef command to create the query everytime.
    This query is then used as the recordsource of the report.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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