messed up totals in report

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'*********************************************************
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
 
Dennis

Your definition of "messed up" may not match mine. Are you asking the
newsgroup readers to review all your code to identify where it might be
breaking, in some un-defined way?

More info, please...!

Good luck

Jeff Boyce
<Access MVP>
 
I agree with Jeff.
I have created hundreds of reports and have never resorted to accumulating
totals in report code. Is there a reason why you are using code versus
setting a control source to something like:
=Sum([ANumericField])
Also, I find your code hard to read since it isn't clear what is a variable
and what might be a control on the report. I recommend naming your text
boxes like "txtSourceTotal" or whatever. Then, in your code, use
If Me.txtSourceTotal ...
SomeVal = SomeVal + Me.txtSourceTotal
--
Duane Hookom
Microsoft Access MVP


Dennis said:
Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'*********************************************************
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
Click to expand...
 
Dennis said:
Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?
Click to expand...
[snip a lot of useless code]

I will go further than Jeff and Duane, there is nothing
wrong with your code, except that this approach can not work
reliably. For many reasons, reports do not process their
data sequentially, so event procedures can not accumulate
any kind of a total from one record to another.

As Duane said either use a text box with an aggregate
function, a running sum text box, or a subquery in the
report's record source query.
 
I'm trying to calculate running totlas, but I can't use
the sum function, because I only want to calculate the
totals from the first or last record in the detail
section. The Sum function calculates the running total
for all of the records in the detailed section. I'm
trying to calculat totals on three levels (Source, Year
and grand total). My totals work fine as long as there
in no bage break in my source header or source footer
section. Note the variables in the code are not inclosed
within [] this might help to determin what is a variable
and a control.
-----Original Message-----
I agree with Jeff.
I have created hundreds of reports and have never resorted to accumulating
totals in report code. Is there a reason why you are using code versus
setting a control source to something like:
=Sum([ANumericField])
Also, I find your code hard to read since it isn't clear what is a variable
and what might be a control on the report. I recommend naming your text
boxes like "txtSourceTotal" or whatever. Then, in your code, use
If Me.txtSourceTotal ...
SomeVal = SomeVal + Me.txtSourceTotal
--
Duane Hookom
Microsoft Access MVP


Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'****************************************************** ***
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
Click to expand...


.
Click to expand...
 
You might be able to place a bound text box in your group header or footer
and then set a running sum. The text box in the group header will be the
first record in the next detail section and the group footer will have the
value from the last record in the preceding detail section.
--
Duane Hookom
MS Access MVP


I'm trying to calculate running totlas, but I can't use
the sum function, because I only want to calculate the
totals from the first or last record in the detail
section. The Sum function calculates the running total
for all of the records in the detailed section. I'm
trying to calculat totals on three levels (Source, Year
and grand total). My totals work fine as long as there
in no bage break in my source header or source footer
section. Note the variables in the code are not inclosed
within [] this might help to determin what is a variable
and a control.
-----Original Message-----
I agree with Jeff.
I have created hundreds of reports and have never resorted to accumulating
totals in report code. Is there a reason why you are using code versus
setting a control source to something like:
=Sum([ANumericField])
Also, I find your code hard to read since it isn't clear what is a variable
and what might be a control on the report. I recommend naming your text
boxes like "txtSourceTotal" or whatever. Then, in your code, use
If Me.txtSourceTotal ...
SomeVal = SomeVal + Me.txtSourceTotal
--
Duane Hookom
Microsoft Access MVP


Is there anyway to prevent my totals from messing-up
whenever the totals occur after a page break?

Here is the code that I'm using:

Option Compare Database
'
**********************************************************
' Creating Global variables
'
**********************************************************
Public SourceTotalAwarded As Currency
Public SourceTotalRequested As Currency
Public YearTotalRequested As Currency
Public YearTotalAwarded As Currency
Public GrandTotalRequested As Currency
Public GrandTotalAwarded As Currency

**********************************************************
' Year Footer
'
' Action:
' Accumulating Grand Totals from Yearly Totals
' Assigning Year Totals tp Year Totals fields
' Reseting Yearly Totals to zero
'
**********************************************************
Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
GrandTotalRequested = GrandTotalRequested +
YearTotalRequested
GrandTotalAwarded = GrandTotalAwarded +
YearTotalAwarded
[Year_Total_Requested] = YearTotalRequested
[Year_Total_Awarded] = YearTotalAwarded
End Sub


**********************************************************
' Year Header
'
' Action:
' Reset Year Totals to zero
'
**********************************************************

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
YearTotalAwarded = 0
YearTotalRequested = 0
End Sub

'
**********************************************************
' BUNumber Header
'
' Action:
' Calculate Source Totals
'
**********************************************************
Private Sub GroupHeader1_Format(Cancel As Integer,
FormatCount As Integer)
If [Total_Amount_Awarded] > 0 Then
SourceTotalAwarded = SourceTotalAwarded +
[Total_Amount_Awarded]
End If
If [Total_Amount_Requested] > 0 Then
SourceTotalRequested = SourceTotalRequested +
[Total_Amount_Requested]
End If
End Sub

' ********************************************************
' Open Report
'
' Action:
' Set all variables to zero
'
**********************************************************
Private Sub Report_Open(Cancel As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0
End Sub

'
**********************************************************
' Report Footer
'
' Action:
' Assigning Grand Totals to Gand Totals fields
'
'****************************************************** ***
Private Sub ReportFooter_Format(Cancel As Integer,
FormatCount As Integer)
[Grand_Total_Requested] = GrandTotalRequested
[Grand_Total_Awarded] = GrandTotalAwarded
End Sub

Private Sub ReportHeader_Format(Cancel As Integer,
FormatCount As Integer)
SourceTotalAwarded = 0
SourceTotalRequested = 0
YearTotalRequested = 0
YearTotalAwarded = 0
GrandTotalRequested = 0
GrandTotalAwarded = 0

End Sub


'
**********************************************************
' Source Footer
'
' Action:
' Assign Source Totals to Source Total fields
' Accumulats Yearly Totals from Source Totals
' Reset Source Totals to zero
'
*********************************************************

Private Sub SourceFooter_Print(Cancel As Integer,
PrintCount As Integer)
[Source_Total_Amount_Awarded] = SourceTotalAwarded
YearTotalAwarded = YearTotalAwarded +
[Source_Total_Amount_Awarded]
[Source_Total_Amount_Requested] = SourceTotalRequested
YearTotalRequested = YearTotalRequested +
[Source_Total_Amount_Requested]
SourceTotalAwarded = 0
SourceTotalRequested = 0
End Sub
Click to expand...


.
Click to expand...
Click to expand...
 
-----Original Message-----
Dennis wrote:

I will go further than Jeff and Duane, there is nothing
wrong with your code, except that this approach can not work
reliably. For many reasons, reports do not process their
data sequentially, so event procedures can not accumulate
any kind of a total from one record to another.

As Duane said either use a text box with an aggregate
function, a running sum text box, or a subquery in the
report's record source query.
Click to expand...

That is what I thought that the results well always be
unreliable. Thanks for the advice!

Dennis :o)
 
Back
Top