Proper parameter reference in nested subreport

Question

Hi,

I am having trouble referencing a parameter in a nested subreport.  It's somewhat complicated, so I apologize in advance, but I very much appreciate any help.  Stuck dead in the water right now.

I have two reports that I run weekly to produce one PDF "weekly report" that I circulate.  I do this manually now, and I would like to make one "master" report that is not linked to any data source, but that houses my two existing reports as subreports.

The first report is a schedule for the next week, and it is a pretty basic report, and I'm not having any problems associated with it.  It is called ActiveItemsSchedule.

The second report is a more detailed report on all of the active projects, called ActiveItemsReport.  It is more complicated.  It contains groups based on the team leader, and then has a subreport section that shows the milestones for each project in the report.  Back in July I participated in a thread in which we worked through how I could break up the milestones into two different subreports so that I could list them in in two columns (to save space on the page, instead of having one long column).  For reasons discussed in that thread, I use a parameter statements in the queries that serves as the data source for the first and second milestone column subreports.  Those queries are shown below:

First Column Query:

PARAMETERS Reports!ActiveItemsReport!Docket Text ( 255 );

SELECT TOP 50 PERCENT ActiveMilestonesQuery.Docket, ActiveMilestonesQuery.Milestone, ActiveMilestonesQuery.Completed, ActiveMilestonesQuery.OrderIndex, ActiveMilestonesQuery.ModMilestoneDate

FROM ActiveMilestonesQuery

WHERE ActiveMilestonesQuery.Docket = Reports!ActiveItemsReport!Docket

ORDER BY ActiveMilestonesQuery.OrderIndex;

Second Column Query:

PARAMETERS Reports!ActiveItemsReport!Docket Text ( 255 );

SELECT ActiveMilestonesQuery.Docket, ActiveMilestonesQuery.Milestone, ActiveMilestonesQuery.Completed, ActiveMilestonesQuery.OrderIndex, ActiveMilestonesQuery.ModMilestoneDate

FROM ActiveMilestonesQuery

WHERE ActiveMilestonesQuery.Docket = Reports!ActiveItemsReport!Docket AND OrderIndex NOT IN (SELECT OrderIndex FROM TopHalfActiveMilestonesQuery)

ORDER BY ActiveMilestonesQuery.OrderIndex;

This ActiveItemsReport has also been working just fine for months now.  But, as referenced above, now I want to tweak my whole system to make things a little more automated.  Instead of having to manually combine the ActiveItemsSchedule and ActiveItemsReport into one PDF for circulation, I am trying to put those two reports into a single "master" report that I can run once and simply print to PDF.  But I'm running into a problem.

I created an "empty" (i.e., no data source) "master" report into which I pasted the ActiveItemsSchedule and ActiveItemsReport as subreports.  The whole structure is as follows:

WeeklyReport (master report; has no data source)

ActiveItemsSchedule (child of WeeklyReport, but unlinked)

ActiveItemsReport (child of WeeklyReport, but unlinked)

MilestonesSubreportColumn1 (child of ActiveItemsReport, no link, using 1st row query above as data source)

MilestonesSubreportColumn2 (child of ActiveItemsReport, no link, using 2nd row query above as data source)

As soon as I put the ActiveItemsReport (with its subreports) into the master report, I start getting the following prompt (twice, one for each subreport that uses the parameter, presumably) when I run the subreport: "Enter Parameter Value:  Reports!ActiveItemsReport!Docket."  So it was fine with my use of parameters in those queries originally, but as soon as I put that bundle of reports and subreports into a master report, then the parameter use trips up.

I tried making the specification of the parameter in the queries more exact, such as "Reports!**WeeklyReport!**ActiveItemsReport!Docket" instead of just "Reports!ActiveItemsReport!Docket," but then the master report just threw a prompt "Enter Parameter Value:  Reports!WeeklyReport!ActiveItemsReport!Docket."  (Although I'm not sure I'm using the proper syntax to make the specification more exact.  Would I use bangs or dots?)

I hope I've explained this as clearly as possible.  I would greatly appreciate any insight into my problem: Why won't Access let me put a report that uses a parameter into another report while still recognizing the parameter I use in my source queries, and how can I solve this problem.

Thanks!

Emily

Answer 1

Hi Emily

I'm so glad you got it working :-)

I got an email notification a few minutes ago to say you were having problems, but I couldn't find the message.  I guess you solved the problem and deleted that message but, just for curiosity, what was the solution?

I'm very happy to give you a step-by-step explanation of how it works, so I'll repost my original message with comments interspersed in bold italics:

Create a normal subreport, based on ActiveMilestonesQuery, and add the subreport twice to the main report, side-by-side. Set LinkMasterFields and LinkChildFields for both to "Docket". Name the left-hand subreport control "MilestonesSubreportColumn1" and the right-hand one "MilestonesSubreportColumn2" (just as you have them named currently).

Nothing especially tricky about this – you have simply placed the same two linked subreports side-by-side on the main report. If we do nothing else then the two will display identically.

Now, make the following tweaks to your subreport:

1. Add a textbox to the report header named txtRecordCount, with this ControlSource:

    =Count(*)

This textbox will contain a count of all the records displayed in this instance of the subreport (after the link fields filter has been applied)

2. Add a textbox to the detail section named txtRecordNum, with this ControlSource:

    =1

    and set its RunningSum property to "Over All"

This textbox simply contains the constant value of 1.  However, since we have set the RunningSum property, each record will add the value to the previous value, so we get 1, 2, 3 ... This is an old trick to get a running record number in a report.

    (Both these textboxes can be made invisible, as you wish)

They will still perform their functions even if we can’t see them!

3. Add these two lines to the Declarations section of the subform module:

    Public p_ColumnNum As Integer

    Public p_ColumnCount As Integer

Because these variables are declared as Public, they become exposed as custom properties of the report (hence the “p_” prefix) and we can set their values from code in other modules. Because they are declared “As Integer”, their values will be initialised to zero.

4. Add the following code to the Format event procedure for the Detail section:

The Format event occurs when a report section is being constructed and placed on the page prior to the final rendering. You can use it to show or hide certain controls or set their values, to draw lines or load images, or to stop this instance of the section from printing entirely. To stop the section from printing, you set the Cancel argument to True (or any non-zero value). Each line of code is explained below:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Declare a variable for the number of records per column

    Dim RecordsPerColumn As Long

If our property variables have not been set up, or are invalid, then exit and do nothing special

    If p_ColumnCount <= 1 Or p_ColumnNum = 0 Or p_ColumnNum > p_ColumnCount Then Exit Sub

Calculate the records per column by dividint the total number of records by the column count

    RecordsPerColumn = (txtRecordCount \ p_ColumnCount)

If the columns don’t divide exactly (there is a remainder) then add one to the records per column

    If (txtRecordCount Mod p_ColumnCount) <> 0 Then RecordsPerColumn = RecordsPerColumn + 1

The tricky bit :-)

We subtract one from the record number in the textbox (so the numbering starts from zero) and divide without remainder by the number of records per column. This will give us 0 for the first column of records, 1 for the second, and so on. If this value is NOT equal to one less than the column number for this subreport, then don’t print the record.

    If ((txtRecordNum - 1) \ RecordsPerColumn) <> p_ColumnNum - 1 Then Cancel = True

    End Sub

Now, open your main report (ActiveItemsReport) and you should see two columns, each listing ALL the milestones for each docket.

We haven’t set the subreports’ custom properties yet, so the code above will not run.

Now, the icing on the cake :-)

In the Format event procedure of the report header section of ActiveItemsReport, add these four lines of code:

Now set the custom properties.  This must be executed after the subreports are loaded, but before they start to format any data. The best place is in the Format event of the main report’s header section.

Refer to the properties by Me.<subreport control name>.Report.<variable name>

    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

        Me.MilestonesSubreportColumn1.Report.p_ColumnCount = 2

        Me.MilestonesSubreportColumn2.Report.p_ColumnCount = 2

        Me.MilestonesSubreportColumn1.Report.p_ColumnNum = 1

        Me.MilestonesSubreportColumn2.Report.p_ColumnNum = 2

    End Sub

Now you should get the first half of the milestones in the left-hand subreport, and the others on the right.

This is completely general and reusable, and uses no special queries or other constructs. Also, if you ever wanted to split the report into three columns instead of two, all you need to do is copy and paste a subreport, rename it, and add two more lines to your ReportHeader_Format procedure.

I hope you followed that, and that anyone else who comes along might find it useful.

Good luck! :-)

Graham

Answer 2

Hello Emily

I'm sure you could get this working, but it seems overly complicated and it will always have the disadvantage of being able to run your ActiveItemsReport either on its own or as a child of WeeklyReport, but not both.

Here is another approach you might like to try.

Create a normal subreport, based on ActiveMilestonesQuery, and add the subreport twice to the main report, side-by-side.  Set LinkMasterFields and LinkChildFields for both to "Docket".  Name the left-hand subreport control "MilestonesSubreportColumn1" and the right-hand one "MilestonesSubreportColumn2" (just as you have them named currently).

Now, make the following tweaks to your subreport:

1. Add a textbox to the report header named txtRecordCount, with this ControlSource:

    =Count(*)

2. Add a textbox to the detail section named txtRecordNum, with this ControlSource:

    =1

    and set its RunningSum property to "Over All"

(Both these textboxes can be made invisible, as you wish)

3. Add these two lines to the Declarations section of the subform module:

    Public p_ColumnNum As Integer

    Public p_ColumnCount As Integer

4. Add the following code to the Format event procedure for the Detail section:

    Dim RecordsPerColumn As Long

    If p_ColumnCount <= 1 Or p_ColumnNum = 0 Or p_ColumnNum > p_ColumnCount Then Exit Sub

    RecordsPerColumn = (txtRecordCount \ p_ColumnCount)

    If (txtRecordCount Mod p_ColumnCount) <> 0 Then RecordsPerColumn = RecordsPerColumn + 1

    If ((txtRecordNum - 1) \ RecordsPerColumn) <> p_ColumnNum - 1 Then Cancel = True

Now, open your main report (ActiveItemsReport) and you should see two columns, each listing ALL the milestones for each docket.

Now, the icing on the cake :-)

In the Format event procedure of the report header section of ActiveItemsReport, add these four lines of code:

    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

      Me.MilestonesSubreportColumn1.Report.p_ColumnCount = 2

      Me.MilestonesSubreportColumn2.Report.p_ColumnCount = 2

      Me.MilestonesSubreportColumn1.Report.p_ColumnNum = 1

      Me.MilestonesSubreportColumn2.Report.p_ColumnNum = 2

    End Sub

Now you should get the first half of the milestones in the left-hand subreport, and the others on the right.

This is completely general and reusable, and uses no special queries or other constructs.  Also, if you ever wanted to split the report into three columns instead of two, all you need to do is copy and paste a subreport, rename it, and add two more lines to your ReportHeader_Format procedure.

Good luck!  :-)

Answer 3

Thanks again!  Very complete and clear.

My previous problem was just that I had pasted the code into the event for the wrong object.  I missed it even when I double-checked things.  But that's why we need to triple-check sometimes.

Best,

Emily

Answer 4

Works perfectly!  Thanks much!

In the spirit of learning, any chance you could very briefly step through what happens where, and why that works?  I can grasp much of why that works, but am not that familiar with the Format events and such, and so it would help me and probably others in the future.

Thanks again!

Emily