Page sums for multiple values

  • Thread starter Thread starter onmars
  • Start date Start date
O

onmars

I have been able to place a sum in the page footer following Article ID
: 296249.

However, when I try to sum multiple columns of values, the sums are
incorrect. Below is the code I am using to sum "Fee" and "Rent" values.

Thanks.

Option Compare Database

Dim x As Double

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

End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Me!FeePageSum = Me!FeeRunSum - x
Me!RentPageSum = Me!RentRunSum - x
x = Me!FeeRunSum
x = Me!RentRunSum
End Sub

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

End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0

End Sub
 
You need to create another variable to keep track of the
other sum.

See code additions inline below.

BTW, it looks like your value are monetary. If so It would
be more accurate to use the Currency data type instead of
Double.
--
Marsh
MVP [MS Access]


I have been able to place a sum in the page footer following Article ID
: 296249.

However, when I try to sum multiple columns of values, the sums are
incorrect. Below is the code I am using to sum "Fee" and "Rent" values.

Dim x As Double
Click to expand...

Dim y As Double
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Me!FeePageSum = Me!FeeRunSum - x
Click to expand...

Me!RentPageSum = Me!RentRunSum - y
x = Me!FeeRunSum
Click to expand...

y = Me!RentRunSum
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
Click to expand...

y = 0
 
Thanks. The sums total correctly in Print Preview. But, when it goes to
print, the first page does not print the sums. Only subsequent pages
print.
Do you see a problem with my code?

Option Compare Database
Dim x As Currency
Dim y As Currency
Dim z As Currency
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
End Sub
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
RentPageSum = RentRunSum - x
OtherPageSum = OtherRunSum - y
SDPageSum = SDRunSum - z
x = RentRunSum
y = OtherRunSum
z = SDRunSum
End Sub
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No checks on that date", _
vbOKOnly + vbInformation, _
"No Matching Records"
Cancel = True
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
End Sub
Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
y = 0
z = 0
End Sub

Marshall said:
You need to create another variable to keep track of the
other sum.

See code additions inline below.

BTW, it looks like your value are monetary. If so It would
be more accurate to use the Currency data type instead of
Double.
--
Marsh
MVP [MS Access]


I have been able to place a sum in the page footer following Article ID
: 296249.

However, when I try to sum multiple columns of values, the sums are
incorrect. Below is the code I am using to sum "Fee" and "Rent" values.

Dim x As Double
Click to expand...

Dim y As Double
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Me!FeePageSum = Me!FeeRunSum - x
Click to expand...

Me!RentPageSum = Me!RentRunSum - y
x = Me!FeeRunSum
Click to expand...

y = Me!RentRunSum
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
Click to expand...

y = 0
End Sub
Click to expand...
Click to expand...
 
Is this question a continuation of the same question by
Natalia on the 11th? If it is, please keep the follow up
questions in the same thread.

If not, then take a look at that thread and report back with
the requested information.
 
Thanks for responding. It is similar the Natalia post but, not of that
thread.

Based on the code I posted, via the ms article and your help, I am able
to print preview page totals. However, when I print the pages, the page
totals on the first page do not print. Only subsequent page totals
print. In other words, page 1, page 2, page 3....show the correct page
totals, but page 1 prints 0's as totals. Page 2, etc. print correctly.

Thanks.

Marshall said:
Is this question a continuation of the same question by
Natalia on the 11th? If it is, please keep the follow up
questions in the same thread.

If not, then take a look at that thread and report back with
the requested information.
--
Marsh
MVP [MS Access]



Thanks. The sums total correctly in Print Preview. But, when it goes to
print, the first page does not print the sums. Only subsequent pages
print.
Do you see a problem with my code?

Option Compare Database
Dim x As Currency
Dim y As Currency
Dim z As Currency
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
End Sub
Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
RentPageSum = RentRunSum - x
OtherPageSum = OtherRunSum - y
SDPageSum = SDRunSum - z
x = RentRunSum
y = OtherRunSum
z = SDRunSum
End Sub
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No checks on that date", _
vbOKOnly + vbInformation, _
"No Matching Records"
Cancel = True
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
End Sub
Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
y = 0
z = 0
End Sub

Article
ID
Click to expand...
Click to expand...
 
Let's change the message box to:

Debug.Print "Page=" & Me.Page & " PrintCount=" _
& PrintCount & " x=" & x & " RunSum=" & Me!RunSum

Add that line at the top of the Page Footer Print event, run
the report and Copy Paste the first half dozen lines in the
Debug window to your reply.

The reason I'm asking for this additional information is
that I haven't been able to reproduce the problem and am
trying to figure out what's happening in your (and
Natalia's) report. So far, all I can do is speculate what
might be causing the problem.
 
Yes, I see now that Natalia and I share a problem. I must have followed
your instruction incorrectly.
This is the first code line I have now. Which prints totals on the
first page that are same as the second page, but in parentheses.

Option Compare Database
Dim x As Currency
Dim y As Currency
Dim z As Currency


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

End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Debug.Print "Page=" & Me.Page & " PrintCount=" _
& PrintCount & " x=" & x & " RentRunSum=" &
Me!RentRunSum
RentPageSum = RentRunSum - x
OtherPageSum = OtherRunSum - y
SDPageSum = SDRunSum - z
x = RentRunSum
y = OtherRunSum
z = SDRunSum
End Sub
 
How many pages in the report? I am having trouble imagining
how the first page can get the negative(?) of the second
page???

Actually, I kind of think you maybe forgot the ReportHeader
Print event that sets x, y and z to 0. If that's correct,
please add that procedure and try again.

Either way, I really need to see the output from the Debug
Window too.
 
The report I am using from home has two pages. Here is the code.
By Debug window output, do you mean the details in the immediate
window? Listed below:

Page=1 PrintCount=1 x=0 RentRunSum=23927.5
Page=1 PrintCount=1 x=0 RentRunSum=23927.5



Option Compare Database
Dim x As Currency
Dim y As Currency
Dim z As Currency


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

End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Debug.Print "Page=" & Me.Page & " PrintCount=" _
& PrintCount & " x=" & x & " RentRunSum=" &
Me!RentRunSum
RentPageSum = RentRunSum - x
OtherPageSum = OtherRunSum - y
SDPageSum = SDRunSum - z
x = RentRunSum
y = OtherRunSum
z = SDRunSum
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No checks on that date", _
vbOKOnly + vbInformation, _
"No Matching Records"
Cancel = True
End Sub

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

End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
y = 0
z = 0
End Sub
 
With the addition code, and 3 pages of records. The first page totals
show as credits of the second page. Second and third page are correct
totals.


The immediate window show:
Page=1 PrintCount=1 x=0 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=28697.5
Page=1 PrintCount=1 x=28697.5 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=28697.5
Page=1 PrintCount=1 x=0 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=29697.5
Page=1 PrintCount=1 x=0 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=29697.5
Page=3 PrintCount=1 x=29697.5 RentRunSum=29722.5
Page=1 PrintCount=1 x=29722.5 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=29697.5
Page=3 PrintCount=1 x=29697.5 RentRunSum=29722.5

My code is
Option Compare Database
Dim x As Currency
Dim y As Currency
Dim z As Currency


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

End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Debug.Print "Page=" & Me.Page & " PrintCount=" _
& PrintCount & " x=" & x & " RentRunSum=" & Me!RentRunSum

RentPageSum = RentRunSum - x
OtherPageSum = OtherRunSum - y
SDPageSum = SDRunSum - z
x = RentRunSum
y = OtherRunSum
z = SDRunSum
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No checks on that date", _
vbOKOnly + vbInformation, _
"No Matching Records"
Cancel = True
End Sub

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

End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
y = 0
z = 0
End Sub
 
With the addition code, and 3 pages of records. The first page totals
show as credits of the second page. Second and third page are correct
totals.


The immediate window show:
Page=1 PrintCount=1 x=0 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=28697.5
Page=1 PrintCount=1 x=28697.5 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=28697.5
Page=1 PrintCount=1 x=0 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=29697.5
Page=1 PrintCount=1 x=0 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=29697.5
Page=3 PrintCount=1 x=29697.5 RentRunSum=29722.5
Page=1 PrintCount=1 x=29722.5 RentRunSum=24797.5
Page=2 PrintCount=1 x=24797.5 RentRunSum=29697.5
Page=3 PrintCount=1 x=29697.5 RentRunSum=29722.5

My code is
Option Compare Database
Dim x As Currency
Dim y As Currency
Dim z As Currency


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

End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As
Integer)
Debug.Print "Page=" & Me.Page & " PrintCount=" _
& PrintCount & " x=" & x & " RentRunSum=" & Me!RentRunSum

RentPageSum = RentRunSum - x
OtherPageSum = OtherRunSum - y
SDPageSum = SDRunSum - z
x = RentRunSum
y = OtherRunSum
z = SDRunSum
End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No checks on that date", _
vbOKOnly + vbInformation, _
"No Matching Records"
Cancel = True
End Sub

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

End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As
Integer)
x = 0
y = 0
z = 0
End Sub
Click to expand...


Man, was this ever a tough one. It took me quite a while to
reproduce the issue by varying my report's setting for
KeepTogether and skipping around pages in the report's
preview. The core issue is that the Page Print event
doesn't always fire unless I preview the pages in sequential
order or jumped directly to the last page. Don't ask me to
explain the logic behind what's happening, but I think it's
a matter of not executing some(?) events on the pages that
are not being displayed.

Regardless of how/why it happens, I think this will work
reliably, at least it did in my tests. Get rid of all the
code you have for this and use this instead:

Private Sub PageFooter_Format(Cancel As Integer, FormatCount
As Integer)
Static curRent(100) As Currency
Static curOther(100) As Currency
Static curSD(100) As Currency

curRent(Me.Page) = Me!RentRunSum
RentPageSum = curRent(Me.Page) - curRent(Me.Page - 1)

curOther(Me.Page) = Me!OtherRunSum
OtherPageSum = curOther(Me.Page) - curOther(Me.Page - 1)

curSD(Me.Page) = Me!SDRunSum
SDPageSum = curSD(Me.Page) - curSD(Me.Page - 1)

End Sub


Note there is no ReportHeader event code and the use of the
Format event instead of the Print event. The array
dimension of 100 is any number larger than the most pages
the report may ever have.

Good luck and be sure to let me know if you still have
trouble with this.
 
It works beautifully! Thanks for all your hard work. It would be great
if the original MS Article could be amended with your fix.

Thanks!
 
It works beautifully! Thanks for all your hard work. It would be great
if the original MS Article could be amended with your fix.
Click to expand...


You're welcome.

These things bug me as much as they do you. If you really
want to do something about it, go to the article, scroll
down to the bottom and tell them what you think of the
content. I have been assured that someone actually looks at
the feedback and, if there are enough(?) negative comments,
something will be done about it.
 
Back
Top