Single prompt parameter for shared parameter in sub-reports' queries

Question

I have put two sub-reports in a main report. The main report comes out fine, but the [Enter Year] parameter appears twice: once for each sub-report's query where it occurs. This is normal, and I can probably reduce it to one prompt by writing code.

But I think there must be a way to give a named parameter once, and the sub-report queries will pick it up and use it. I would like a non-code method to do this, since next I will do a similar collected report for many sub-reports with shared parameters.

I've tried: 1. Setting the control source for a text field on the main form to =[Enter Year] doesn't cause a prompt at all. 2. Setting the datasource for the main form to SELECT * FROM <common table> WHERE year=[Enter Year] causes a prompt and skips the ones for the sub-reports, but strangely causes the sub-report pages to be repeated infinitely.

This is for Access 2003.

Edit: 1. above doesn't work because I want the "prompt reuse" to be in the query. If it weren't for the parameter being used as a (standard "time period") filter I could of course just set a text field to the value of the parent.

Right now I've based the main report on a simple query that includes the prompted parameter as a column. But for some reason Access says the reference to the column (tmpYear) is "dubious"/"unequivocal" (I'm not running an English Access). Likely this is because the column in the main report query is not "finished" when the sub-report query wants it.

To me, this changes the question to: how to get the value of a main report text field into the queries of the sub-reports.

Edit 2: I now understand that there are two alternatives to solve this: a) Write a global function that returns a prompted value, and use that function in the query design. b) Keep the original query with parameters and set myQueryDef.Parameters("") to the prompted value.

...Except b) still gives the sub-query prompts.

(I'm sorry for making this a piecemeal question, but maybe it will match such a journey of discovery of others. If you want I could post a new short question as to why b) doesn't work.)

RichardC RichardC · Accepted Answer · 2013-03-06T15:49:29

This seems to be possible (at least by my understanding of your question).

You need to put your parameter in the query behind your main report and make it come out as a field:

SELECT YearParam:[Enter Year], <<Other Fields Here>> FROM <<Table NAME>>

Then on the main report, place a textbox in the Report Header with a control source of the field name (YearParam) and name the textbox (txtYearParam, for example).

Now, on each of the queries behind your sub-reports (or on the report filter) make it look at the main report textbox using the following syntax:

Reports!ReportName.txtYearParam

I tested this with a very basic report with 2 sub-reports, and the parameter only appears once and filters correctly.

Single prompt parameter for shared parameter in sub-reports' queries

1 Answers