ms access - Passing Query Parameter to Sub-Report

Question

It has been a long time since I have worked with MS Access and am not sure if I am doing this right.

I have a Report bound to a query of single list of user names. The report groups on the user name and has a sub-report in the detail that is a chart.

The sub-report/chart is based on a query that accepts a parameter of the user name. Since the sub report is not bound I need to pass the user name parameter from the main report detail onload event down to the sub-report and thus into the underlying query.

I am not understanding how to make that happen or even if my approach is sound.

(p.s. i originally posted on "Super User" but see not many people use that site.)

Thanks for the help

Answer 1

To get the full benefit of subreports they should be bound to a recordsource (table or query) and use the Link Master Fields and Link Child Fields properties of the Subform/Subreport control to "sync" the subreport data with the parent record.

Say you have a [Users] table

userID
------
Gord
Tim

and a [UserTraffic] table

userID  trafficDate downloadMB
------  ----------- ----------
Gord    2013-04-26  366
Gord    2013-04-25  442
Tim     2013-04-26  890
Tim     2013-04-25  212

Your main report [UserReport] is bound to [Users] and contains a subreport control based on the report [UserTraffic_subreport]

When you use that report in your Subform/Subreport control on your main form...

...be sure to set the Link Master Fields and Link Child Fields properties...

...so your report will come out like this:

Note that this example required no VBA code at all.

Edit

The same principles apply when adding a chart to a report. The following report displays user traffic by date, so the Record Source for the main report is

SELECT DISTINCT trafficDate FROM UserTraffic ORDER BY trafficDate;

The report uses a Chart control instead of the Subform/Subreport control...

...and the properties of the Chart control are...

The resulting report looks like this