Totals on Report from main report and sub report combined.

K

Kathy

I need to combine totals in the report footer with information from a sub
report. The totals from the sub report show in the deatil. I need to
reference them in the footer.
 
M

MGFoster

Kathy said:
I need to combine totals in the report footer with information from a sub
report. The totals from the sub report show in the deatil. I need to
reference them in the footer.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put a TextBox in the sub-report's footer. As this TextBox's
ControlSource, put a reference to the control in the sub-report's detail
that you want to use in the main report. Put the name of this TextBox
in the main report's formula. E.g.:

=[ExtendedPrice]+IIf(SubReportName.Report.HasData,
Nz(SubReportName!SubReportTextBoxName),0)

The IIf() function is needed to insure no error occurs if there isn't
any data (HasData = False) in the subreport.

Replace SubReportName and SubReportTextBoxName w/ your sub-report &
TextBox names.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRB7sIechKqOuFEgEQIJ2ACgwYH8VDvG0LuAchGpl6sZeka8U2YAoMBQ
COMcZY0Yp28tG9pH3ZViHmql
=U1zO
-----END PGP SIGNATURE-----
 
K

Ken Sheridan

Kathy:

Your description is a bit sparse. Are you saying you want to sum all the
sums from each instance of the subreport and then add this to the sum of a
field or fields from the main report? If so try this:

1. Add an unbound text box txtGrandTotalhidden to the report footer and set
its Visible property to False (No).

2. Add another text box to the footer and set its ControlSource property to:

=Sum([YourField])+[txtGrandTotalHidden]

where YourField is the field in the main report being summed and whose
values you want added to the sum of the subreport's sums.

3. In the report header's Print event procedure initialize the hidden text
box to zero with:

Me.txtGrandTotalHidden = 0

4. In the Print event procedure of the detail section of the subreport (NB
not that of the main report) increment the value of the hidden text box,
examining the PrintCount property to avoid any inadvertent double counting:

If PrintCount = 1 Then
Me.Parent.txtGrandTotalHidden = _
Parent.txtGrandTotalHidden _
+ Me.txtSubTotal
End If

where txtSubTotal is the control in the subreport in which its values are
summed.

As the Print event procedure is being used to increment the value of the
hidden text box each page must 'print', so you might find that the totals are
incorrect if you skip over pages in print preview. Sending the whole report
to a printer should be fine though. Using the Format event procedure would
avoid this, but you could get double counting as the Format event can fire
multiple times and the FormatCount property be 1 for each, so you'd need to
undo the increments in the Retreat event procedure, which can get a little
tricky.

Ken Sheridan
Stafford, England
 
Top