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

Accepted Answer

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

Passing Query Parameter to Sub-Report

Solution

Problem