Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2013
    Posts
    6

    Unanswered: Filtering Report from 5 list boxes

    Newbie questions about running a report from 5 listboxes on a Form. Attached is a word doc explaining and showing my situation. Basically I need my users to make their selections and get the report filtered down. All are listboxes, that requery after each selection.
    The standards came from an excel document, that I moved to access. Then created the report, and finally the form. Let me know if I should attach the db file.

    Thank you for you help!!
    Attached Files Attached Files

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Ok here is the easiest explanation of how to do this
    The type list box according to you needs to be multiselect which makes this an "or" criteria and I would concatonate this last in my sql statement . On this form put a textbox on the form called txtType


    You to build the sql statement on the "fly" and to do this I assume the "find" button is used to put all of this in motion.

    so behind the "Find" button put the follwing code:

    dim strbuild as string, strsql as string, strType as string, strsubType as string, strUse as string, strContent as string, strtech as string, strwhere as string

    'Now I assume each listbox's first column is the data being displayed and for simplicity sakes I am numbering them list1 through list5 in the order we see them

    so then the next lines of code are :

    strsubtype = me!list2
    struse = me!list3
    strcontent = me!list4
    strtech = me!list5

    Notice I have left off list1 (strType)

    'first build the sql statement and the where clause :

    str sql = "select * from tblYourtablename "
    strwhere = "where subtype = " & "'" & strsubtype & "'" & " and [intended use] = " & "'" & struse & "'" & " and content = " & "'" & content = " & "'" & strcontent & "'" & " and technology = " & "'" & strtech & "'"

    'Now to build the multiselect we are going to cheat - only because if you are a novice using a recursive method to get multiselected data may be painful for both of us.

    now behind the afterupdate event of the list1 (Type listbox) put the following code

    if len(txttype) > 0 then
    txttype = txttype & " or type = " & me!list1
    else
    txttype = " type = " & me!list1
    end if

    simply making selections from that type box (one at a time) will build this 'or" criteria for you.

    to clear that box simple select all the text in it an start over - if you want to be fancy hide the txttype control and simply have a button labeled "Clear type criteria"
    behind the on click event of that button place the follwing code\

    txttype = ""


    to continue:

    behind the "Find" button place this code

    strbuild = strsql & strwhere & " and " strtype

    strbuild is now criteria for what ever recordsource you are using for a report or form.

    start there - i am sure you will have questions

    but i think the fastest and easiect way to get around the multiselect is to build the statement after selecting each item in type.
    Dale Houston, TX

  3. #3
    Join Date
    Feb 2013
    Posts
    6
    Your response is greatly appreciated. Attempting now.

  4. #4
    Join Date
    Feb 2013
    Posts
    6
    axsprog,

    So you are correct questions. Attaching my working file for reference. I have done everything I believe you said. I did add the strtype to my code to see if that worked, which of course it did not. That should be the only difference I believe.

    Do I need the to keep the or criteria in my query?
    I am calling the report from the query not table. Is that okay?
    To have the selections display on the report as the openarg, do I just call the txttype?

    Finally - Really need user to be able to multi select each list and include null for each as well. Is this a wrench for the openargs, and current code?

    Again thank you!!!
    Attached Files Attached Files
    Last edited by feinbergiant; 02-26-13 at 14:19.

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    give me a day or two to look at this and provide you the solution
    Dale Houston, TX

  6. #6
    Join Date
    Feb 2013
    Posts
    6
    Greatly appreciated!!!

  7. #7
    Join Date
    Feb 2013
    Posts
    6
    I also moved my requery to on click. I learned bad idea to have it on event.
    Looking forward to learning from you Dale. Thanks again!

  8. #8
    Join Date
    Feb 2013
    Posts
    6
    Anyone else with any advice? I have tried many of the others fixes found by searching forums on here, yet it seems like this may not be possible in Access.

Tags for this Thread

Posting Permissions

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