Report generated from a query

[JLS04]

I'm trying to create a report that is generated from a query. When I open the report, I would like the opportunity to refine the search based on a list of sales people. That is, I would like a drop down box with selection criteria (from an existing table) to pop up when the report is opened. The user could then click on the name of the person he/she wants to generate information for? Can this be done?

 

[ScottGem]

I'm trying to create a report that is generated from a query. When I open the report, I would like the opportunity to refine the search based on a list of sales people. That is, I would like a drop down box with selection criteria (from an existing table) to pop up when the report is opened. The user could then click on the name of the person he/she wants to generate information for? Can this be done?

Yes, but you need to do it slightly differently. Instead of calling the report, open an unbound form. On that form will be your List box where the user selects the person. In the query set the criteria to:

=Forms!formname!controlname Substitute your actual form and controlnames

Add a button to the form to then run the report.

This is a standard tecnique that I use all the time for dynamic reports.

 

[JLS04]

Hey ScottGem,

Thanks for the help. This should work perfectly. The only question I have is, what is the "controlname" referencing?

Thanks again,
John

 

[ScottGem]

Umm, the name of the control on the form. Each object on a form is a control. they all have names.

 

[JLS04]

Got it. Sometimes I try and overthink these things... never a good idea. I just get myself in more trouble!

I created the unbound form, simply a combo box referencing the list I want to choose from. I then created the query I will use to select data from the table I'm pulling info from. In the criteria I put the following =Forms!Producers!Combo2 (where Producers is the name of the unbound form and Combo2 is the control name of the drop down box). When I run the query, it doesn't recognize the data in the criteria line. What am I doing wrong? I know this is probably simple... sadly, so am I!! Thanks for all your help!

 

[ScottGem]

First, make sure the combobox name is correct. Second make sure that the bound column of the combo is the same field where you are putting the criteria into the query.