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.
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
'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
txttype = " type = " & me!list1
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 = ""
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.
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?