Sub Report footer total into Main Report

[Brad P]

Hello again...

Something so simple yet so complicated...

I have a subreport with a footer. It has a running sum field. I want to take
that final sum and use it in the report footer of the main report. What
happens is the number from the first record of the subreport is showing in
the textbox in the main report, not the running sum total at the end.

Obviously I am not on the right track...any suggestions? The running sum is
a sum of a reoccuring calculation.

Thanks

 

[Allen Browne]

There is a timing issue with the passing of the Running Sum value back to
the main report. Instead, use a total in the Report Footer section of the
subreport.

1. Create a query to use as the RecordSource for the subreport.

2. Enter the calcuation into the query. For example, you might enter this
into the Field row of the query:
Amount: [Quantity] * [UnitPrice]
Save the query.

3. Open the subreport in design view, and change its RecordSource to this
query. You can now put the Amount field onto your report if desired.

4. If you do not see a Report Footer section on your subreport, click Report
Header/Footer on the View menu. Note: this must be the report footer not the
page footer. You can set the Visible property of the Report Footer section
to No if you do not wish to show the section.

5. In the Report Footer section of the subreport, add a text box with these
properties:
Name txtSubTotal
Control Source =Sum([Amount])
Format Currency (or General Number?)

6. Close and save the subreport.

7. Place a text box on the main report where you want to total shown. Set
its ControlSource to:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report], 0), 0)
and replace "Sub1" with the name of your subreport control.

Be sure to set the Format property of this text box also, so that Access
knows the intended data type. The test of HasData avoids showing "#Error" in
the case where the subreport doesn't have any records (and so referring to
the non-existent total gives an error).

 

[Marshall Barton]

I have a subreport with a footer. It has a running sum field. I want to take
that final sum and use it in the report footer of the main report. What
happens is the number from the first record of the subreport is showing in
the textbox in the main report, not the running sum total at the end.

Obviously I am not on the right track...any suggestions? The running sum is
a sum of a reoccuring calculation.

I've seen that too. It seems the main report's values are
filled in before the subreport has calculated the total. I
don't know of any way around it, unless there something you
can do that would allow you to us the Sum function instead
of RunningSum. Probably, you'll have to do the calculation
in the subreport's record source query.

 

[Brad P]

I got it working....I took the calculated fields in the report and stuck it
in the query for the subreport. I then used a sum expression to add up all
the individual numbers produced by the query, and not the report as before.
The main report was then able to bring up that sum.

Thanks for your suggestions...it helped!