Prompted multiple times for same parameter value when adding subrep

[DeveloperMar]

I have a main report based on a parameter query that prompts for [Enter Begin Week Date]. I have a subreport that is based on a parameter query that also prompts for [Enter Begin Week Date]. When run separately, each report prompts for the date 1 time. When the subreport is added to the main report, I am prompted numerous times for [Enter Begin Week Date]. Using Access 2000, what do I need to do differently so I am only prompted 1 time for the date?

 

[BMeek]

There are a couple of solutions to your issue. The first and easiest would be to use a subquery. Create the first query to gather your data and place the parameter prompt in it. Then create a second query that uses the subquery as it's table. This will give you the data you are looking for while only prompting the user once.

The second solution is to use a form that allows the user to select the parameter from a dropbox and reference the value in the dropbox in your queries. This allows extra benefits as well; 1) the user can only select the dates you provide. 2) you don't have to worry about typos. 3) The value will be there for all of the queries you reference.



Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A TC in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[knappm]

Could you possible elaborate a bit on this solution. I'm in the same situation, and I can't seem to figure out exactly how to implement this solution. Any help would be appreciated.

Thanks,
Mark

 

[BMeek]

Mark,
Which solution would you like to have details on, using subqueries or creating a form?
Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[knappm]

Heh, well, both maybe?

I don't fully understand either and if I could understand both, then I would be able to choose the best solution for me. When I asked the question, I guess I was referring to creating a form though. That's what I was trying to get to work and couldn't.

Thanks.
Mark

 

[BMeek]

Create a simple form to allow the user to select the date parameter from a combo box. Use a query that sorts the available dates in ascending order to populate the combo box. That way the only selections available to the user are valid dates. Then in the parameter query for the report and the sub-report, reference the value in the combo box under the criteria row in query design. Use the following format: [Forms]![FrmName]![CmboName] where the first value tells Access to look for a form, the second value is the form name, and the last value is the combo box name. Then you'll need to create a button to put the code to call the query behind the click event. This is the order to do the tasks in:
1) Create the form
2) Create a query for the dates to be selected
3) Add the combo box to the form and use the RowSource to assign the query to the combo box.
4) Revise your report queries to reference the value in the combo box.
5) Add a command button for the click-event to run the query and show the report.

My experience is that using the Access help file along with instructions like these can make your tasks not only easier, but more rewarding when you are able to complete something yourself! It's a good feeling!
Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.