title: Single prompt parameter for shared parameter in sub-reports' queries
body: <p>I have put two sub-reports in a main report. The main report comes out fine, but the <code>[Enter Year]</code> 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.</p>
  
  <p>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.</p>
  
  <p>I've tried:
  1. Setting the control source for a text field on the main form to <code>=[Enter Year]</code> doesn't cause a prompt at all.
  2. Setting the datasource for the main form to <code>SELECT * FROM &lt;common table&gt; WHERE year=[Enter Year]</code> causes a prompt and skips the ones for the sub-reports, but strangely causes the sub-report pages to be repeated infinitely.</p>
  
  <p>This is for Access 2003.</p>
  
  <p><strong>Edit:</strong>
  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.</p>
  
  <p>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.</p>
  
  <p>To me, this changes the question to: <strong><em>how to get the value of a main report text field into the queries of the sub-reports</em></strong>.</p>
  
  <p><strong>Edit 2:</strong>
  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.</p>
  
  <p><em><strong>...Except b) still gives the sub-query prompts.</em></strong></p>
  
  <p>(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.)</p>