running queries with listbox selections 1

[Shift838]

I have been asked to do the following, but I have very little access experience. I have been asked to create a form with list boxes to run queries. They are supposed to work where a user selects a query from a list box, then another list box appears to select a parameter say "Created by", then another list box appears to select yet another parameter, and so on for about 5 list boxes. I have the form written, but I have no clue how to execute the query to run the selected parameters. Can anyone give me some hints or tell me how to do this?

 

[Crowley16]

ok, firstly, you can use the MSysObjects table to access a list of the reports, which can then be shown in the list box.

Then for the parameters, you can either generate a dynamic sql statement, based on the values in the list boxes...

or

you can use a parameter query, and assign the parameters to the query, before printing the report...

I'd go with the dynamic sql option as a general rule, but if it's the same set of listboxes each time, then you can just directly reference the values of the list boxes in the query used for the report...

--------------------
Procrastinate Now!

 

[Shift838]

by building a dynamic sql statement to then I assume I would execute the statement with the command button right? If so..how would i make it generate a report?

 

[Shift838]

I am also not familiar with creating dynamic sql statements. where can I find more information on the format of the statement and other criteria regarding this?

 

[FancyPrairie]

This FAQ faq181-5497 contains a function that will build the Where clause for you. It works for single and multi-select list boxes, combo boxes, text boxes, ranges, options groups, and check boxes.

You only have to do 2 things to make it work.

1. Open a new module and copy the functions found in the FAQ and paste them into the new module.
2. Set the tag properties of the controls on the form as specified in the FAQ.

NOTE: The tag property is the part that some people mess up on. And it's the key to make it work.

 

[Shift838]

So each TAG property of each listbox is different then correct? Also each tag property for each listbox could be different dependant on what the user selects within the list box correct?

 

[FancyPrairie]

The Tag property for each list box would be different. For example, your "Created By" list box contained the names of all employees and the names of the employees were contained in the table tblEmployees. And each record of the employee table contained the employee ID and name (last, first). Your control source of your list box might then be "Select empID, empName from tblEmployees;". Now, suppose your report was based on another table (tblJobs) that contained the employee ID. The tag property of this list box would then be:

Where=tblJobs.lngEmpID,Long;

What you might want to do to test it is to define the tag property for just one of your list boxes. Then put a command button on your form. In the Onclick event of the command button, do something like this:

MsgBox BuildWhere (me)

That should tell you if it's doing what you want it to do.