子报表查询中共享参数的单个提示参数 [英] Single prompt parameter for shared parameter in sub-reports' queries

问题描述

我在主报表中放置了两个子报表.主报表可以很好地显示，但是[Enter Year]参数出现两次:对于每个子报表的查询，一次出现一次.这是正常现象，我可能可以通过编写代码将其简化为一个提示.

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.

我尝试过: 1.将主窗体上文本字段的控制源设置为=[Enter Year]根本不会提示. 2.将主窗体的数据源设置为SELECT * FROM <common table> WHERE year=[Enter Year]会引起提示，并跳过子报表的提示，但是奇怪的是导致子报表页面被无限重复.

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.

这是用于Access 2003.

This is for Access 2003.

修改: 1.上面的方法不起作用，因为我希望在查询中使用即时重用".如果不是将参数用作(标准时间段")过滤器，则我当然可以将文本字段设置为父级的值.

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.

现在，我将主报告基于一个简单的查询，该查询将提示的参数作为一列包含在内.但是由于某种原因，Access表示对列(tmpYear)的引用是可疑的"/明确的"(我没有运行英语Access).可能的原因是，当子报表查询需要主报表查询中的列时，该列未完成".

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.

修改2: 我现在知道，有两种替代方法可以解决此问题: a)编写一个返回提示值的全局函数，并在查询设计中使用该函数. b)保留带有参数的原始查询，并将myQueryDef.Parameters(")设置为提示值.

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.

...除了b)仍会给出子查询提示.

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

(很抱歉，我提出了一个零星的问题，但也许它将与发现其他人的旅程相匹配.如果您希望我可以提出一个新的简短问题，说明为什么b)不起作用.)

(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.)

推荐答案

这似乎是可能的(至少根据我对您问题的理解).

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>>

然后在主报表上，将一个文本框放在带有字段名称(YearParam)的控件源的 Report Header 中，并命名该文本框(例如txtYearParam).

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

我用一个非常基本的报告(包含2个子报告)对它进行了测试，该参数仅出现一次并正确过滤.

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

这篇关于子报表查询中共享参数的单个提示参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！