Report Totals not adding up corrrectly.

[Guest]

Hi all,

I have a report where I am trying to use code to generate totals based on a
field that I've put into a grouping level. The field that I'm totalling is
repeated in the detail section and is creating inaccurate totals, if I do it
the simple way

The issue I'm having is that one of the totals I'm creating is being
doubled, tripled, etc. as the users run the report from a previewed screen
(we have all our reports set to preview as the users don't always print them
after they're generated).

I've included the code below.

Thanks,
Jamie Fellrath


Option Compare Database

Dim txtLastGrant As String
Dim curSumCDC, curSumCTC, curTotalCDC, curTotalCTC As Currency

' curSumCDC, curSumCTC, curTotalCDC, and curTotalCTC are the variables
I'm using for the totals. curSumCDC and curSumCTC are at a lower grouping
level, and curTotalCDC and curTotalCTC are displayed in the report footer.

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

Me.SumCDC = curSumCDC
Me.SumCTC = curSumCTC

'Me.SumCDC and Me.SumCTC are the text boxes that contain the totals for
the sub-level.

curTotalCDC = curTotalCDC + curSumCDC
curTotalCTC = curTotalCTC + curSumCTC

'I'm simply incrementing the amounts of curTotalCDC and curTotalCTC as I
go along to get the correct total for the report footer.

End Sub

Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)

curSumCDC = curSumCDC + Me.CurrentDirect
curSumCTC = curSumCTC + Me.CurrentTotal

'I'm incrementing the amounts of curSumCDC and curSumCTC as I go along
to get the correct total for the sub-level footer.

End Sub

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

curSumCDC = 0
curSumCTC = 0

'I'm resetting these two variables for a new total at the header of the
sub-level

End Sub

Private Sub Report_Open(Cancel As Integer)

curTotalCDC = 0
curTotalCTC = 0

'Setting these two variables to 0 at the beginning of the report.

End Sub

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

Me.TotalCDC = curTotalCDC
Me.TotalCTC = curTotalCTC

'This is where I assign the totals in the report footer

End Sub

 

[Allen Browne]

Access fires the events again when the report prints after preview, so you
need to reset your variables in the Report Header's Format event.
Report_Open does not fire again.

While that will address your immediate issue, it won't solve your problems
though. You cannot rely on totals collected through the report events across
multiple pages. If the user previews or prints only some pages of the report
(e.g. just page 4), the events for the intervening pages may not fire, so
the totals do not get calculated, so you get incorrect results.

Using a Running Sum will give reliable results.

 

[Guest]

I'm afraid I don't quite understand. I thought that a running sum was what
I was doing. Could you elaborate a bit, please?

Thanks for your help!

 

[Allen Browne]

Instead of programmatically collecting the totals in the events of the
sections, add an extra text box to the section and set its Running Sum
property to Over Group (or Over All, depending what you want.)

 

[Guest]

Thank you Allen! That's the stuff.

Not sure why I never used that property before.