Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2004
    Posts
    31

    Red face Unanswered: Multiple List Box: Need to get results into query for a report HELP!

    I've managed to create a list box with multiple selections.
    Now my problem is to get those results into my query, so I can then print a report based on those selections.
    I am not good at all with writing code.
    If I select ONE item on my list box, it will print my report, but if I select MORE than one item I get errors on my report, not at all what I'm looking for.
    Is there an easy way of doing this?
    Your help is much appreciated, as I can't progress until I solve it.
    My query reads in Criteria (e.g.):
    [Forms]![Form Name]![Listbox1]

    Many, many thanx.
    Regards,
    Jillian
    Last edited by Jillian; 10-28-04 at 11:13.

  2. #2
    Join Date
    Apr 2004
    Posts
    173
    I've never seen this done without code. Any time I've done this I've iterated through the list to find selected values and built a query string. It really isn't that hard to do. If you need help with the code post back.

  3. #3
    Join Date
    Oct 2004
    Posts
    31
    Yes I will need help with the code, and where to apply the code.
    I try to advoid code.
    But thought it might be something that I couldn't in this case.
    Thanks so much. It would be great if you could provide it.
    Thanks also for taking the time to answer.
    Regards,
    Jillian

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    Could you attach your db. My guess is that we'll have to take your query and build the string and slap it all together to make this work.

  5. #5
    Join Date
    Oct 2004
    Posts
    31
    Oh my! It's about 5Mg!
    I'll see if I can cut it back to 'bare bones'. Might take me a while.
    Since they provide multiple selection list boxes, I would have thought it to be easier. *groan*
    Regards,
    Jillian

  6. #6
    Join Date
    Oct 2004
    Posts
    31
    I may not be able to do this any time soon.
    Any other suggestions?

    Regards,
    Jillian

  7. #7
    Join Date
    Apr 2004
    Posts
    173
    Here's my thoughts.

    1. take the existing SQL behind your query absent the criteria

    2. build up the criteria from you multi select list box this would entail building a string with the selections and the accompanying AND n ORs.

    3. take your query via a query definition object and modify the SQL of said query to reflect the newly selected criteria.

    OR

    1. Design the report on the wide open query.
    2. Use the multi select list box to build a filter string for the report.
    4. Then open the report from code with the filter in place.

    I don't know which approach will work based on your situation. Option 2 is much simpler but doesn't work the best for very large datasets.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Good morning,

    You have created the ComboBoxes already. Did you create them in the table?
    If you did so then just create your Query based on that table. Next use that query as the basis for your form. And from there the Report. So long as you keep pulling the fields over you will get your result. Not really any need for any code to show what is in your form. Awwwww, what the heck, check out the small sample database I just created and posted here for you. Not totally sure that was what you wanted, and if not let me know and I'll try it again.

    have a nice one,
    Bud
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2004
    Posts
    31
    Sorry been busy.
    Had to download at home as won't let me at work.
    Will test out later.
    Thanks a million.
    Regards,
    Jillian

  10. #10
    Join Date
    Oct 2004
    Posts
    31
    I love what you've done, and it will help me in other areas, for sure.
    What I am actually trying to do, based on what you have done as a 'sample' - is to choose 'location' 2 and 4 ONLY (e.g.) and then do a report on the records that have locations 2 and 4 associated with them.
    I have a list box, where I can multi-select (e.g. would hold down shift key to select BOTH 2 & 4). But from there, I have no idea how to get the query and report to identify BOTH 2 and 4. If I select just one (e.g. just 2), my report prints out just fine. But if I multi-select ... no go!
    I really appreciate your help on this.
    Coding puts me out in the cold. I'm not good at studying these things.
    I get by, and need to overcome this one stumbling block.
    It will solve several issues if I can.
    Thanks so much.
    Regards,
    Jillian

  11. #11
    Join Date
    Oct 2004
    Posts
    31
    I may be wrong on the bit about being able to select ONE 'location' out of the multiselect list box, and getting a report.
    I thought I could, but now can't replicate that result.
    It just is not recognising [Forms]![Form name]![multilistboxchoices] with either single choice or multiple choice from the list box.
    I tried going back in my archived stage versions, but must have missed out on saving the one where I thought a single selection in the multiselect list box produced a report on that single selection.

    Thanx.
    Regards,
    Jillian

  12. #12
    Join Date
    Oct 2004
    Posts
    31
    I have found out this information...
    " However, when the Multi Select property is set to "Simple" or "Extended" the list box has a value of Null regardless of how many or which items are selected. In order to make use of the user's selection of single or multiple items it is necessary to use a VBA procedure to examine the list, determine what has been selected, and act on it."
    I am definitely getting a 'null' value, so need VBA procedure, and where to apply it (on click, on exit, where???) and take that value over to a Query.
    (and from there to my report).
    Thanx.
    Regards,
    Jillian

  13. #13
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111

    MultiSelect and Print

    Have a look at the attachment, I have put this together with help I have received from the web and a little bit of my own knowledge. It seems to work fine, someone may be able to fine tune it a bit. You do not need to hold down the shift key to select items.

    Hope this is some help.

    Regards,
    Attached Files Attached Files
    Regards,



    John A

  14. #14
    Join Date
    Oct 2004
    Posts
    31
    Haven't had time to look today, but sure do appreciate your help.
    Regards,
    Jillian

  15. #15
    Join Date
    Oct 2004
    Posts
    31
    Thanks so much for your time on this. It was nice to see help from Australia! I'm Australian (Adelaide), but currently residing in Atlanta.
    You certainly pointed me in the right direction, but also I seem to be a dumbo re coding and for a while I seemed to be going around in circles.
    HOWEVER...
    I've ended up getting what I want through filters, and getting the report that way. It actually gives a lot more versatility, but requires the end-user to use a bit more complex thought. Not a totally bad trade-off, especially with added flexibility.
    I think people like you are wonderful with your help, and I certainly appreciate it.
    Regards,
    Jillian

Posting Permissions

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