Referencing parameter queries in a subreport 1

[Tekime]

Hello,

Is it possible to reference the value entered into a parameter query which is the data source of a subreport, from the parent report? The format:

Reports![parentRpt]![subRptName].Report![Parameter Value]

Does not seem to work for me..

Thanks

http://www.fury-tech.com

 

[scriverb]

I don't think so as they are executed as seperate entities in running the report. Now you may be able to link the subreport query to the main report query through a common field as long as their linkeage does not create additional rows for the main query. Now the prompt for the sub query will be available to the main report query as they are both in the same query. You may have to modify the subreport query SQL to just include the linked fields and may have to use some Grouping to keep it at one record but this is a way to do what you have asked.

Post back with the two queries if you need assistance with this.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[PaulBricker]

I'm wondering how you are passing the value to the subreport query? If you put the parameter on the criteria line for the subreport you are prompted each time the subreport Formats. Are you passing the value from an open Form? If so, you should be able to reference that textbox on the main report. Maybe a little more info would help.

Paul

 

[Tekime]

And two years later I reply....

Sorry it took me so long, I've been fighting with an import script for moving client data into the system which has kind of taken precedence over reporting issues (as well as the rest of my life ;-))

I appreciate the responses though, I'm kind of second guessing my approach to this in the first place. I didn't want to reply just to say I would reply when I got back on it, but I don't want you to think your answeres went unnoticed! I'll post back when I get a chance to tackle these reports again (hopefully tomorrow).

Thanks
Tek

http://www.fury-tech.com

 

[Tekime]

Well I've gotten back to the reporting issues as of late; some other demands took precedence for a while but now I need to get reports going on a regular basis.

I've rethought my reporting methods a little, and while I've found a way that I think will work very well for my needs, I'm having major issues with one thing. I cannot set a subreports filter to the parent report's filter. I just cannot do it.

I have a set of reports, about 10 in total. I have a reporting form which lets the user select a start and end date, and generate a report using those dates. For individual reports this works fine, I use a bit of VBA on the OnClick property of each command button, something like this:

[tt]
Dim stDocName As String
Dim strClause As String

stDocName = "rpt_CbByMap"
strClause = "[cb_dateReceipt] >= #" & rptDateStart & "# AND " & _
&quot;[cb_dateReceipt] <= #&quot; & rptDateEnd & &quot;#&quot;

DoCmd.OpenReport stDocName, acPreview, &quot;qry_RptCbByMap&quot;, strClause
[/tt]

BUT, I need one report which contains some of the other reports as sub-reports. I want to select the dates as I normally would, and have the sub-reports filter match that of it's parent filter (the 'main' report).

I have tried Me.Filter = Me.Parent.Filter in the OnLoad and OnPrint events of the sub-reports report and footer areas, tried setting it from the parent report, etc. to no avail. I suspect that it doesn't work because the Filter property cannot be used until the entire sub-report has loaded... but I can't determine a way to resolve this and make it work.

http://www.fury-tech.com

 

[PaulBricker]

Assuming that you have the date field in each subreport, can you set the filter in each subreport to
[DateFieldName]Between Forms!FormName!StartDate And Forms!FormName!EndDate
Then if you leave your main report filter the way it is, it should, in theory, work. At least it did on my test report with one subreport.
Be sure to set the FilterOn property to Yes.
Paul

 

[Tekime]

I wanted to tell you sooner but Tek-Tips has been problematic lately. The solution you recommended is perfect, and the added benefit of setting the filter within each report is that I can run them alone or as sub-reports with no modifications. Thanks a ton for your help!

http://www.fury-tech.com

 

[PaulBricker]

I've been out of town since Friday so it's good to get back and see it worked.

Paul