Page Total Summary

[AlanWhaley]

Hi all,

Could any one propose a solution to the following problem I have?

I have used some VBA to get totals at the bottom of each page of a report, by page (i.e. Total for Page = $###.)

I would now like a summary page listing each pages' total, i.e.

Page 001 $###

Page 002 $###

Etc etc

Is this possible using code to create a table based on the page total values, and use that as the basis of subreport?

I have no idea where to start?!?

THANKS IN ADVANCE!

 

[dhookom]

Do you always have the same number of records on a page with maybe only the last page having fewer?

Duane
Hook'D on Access
MS Access MVP

 

[AlanWhaley]

Hi Duane,

Unfortunately not - the descriptions vary in length quite a bit, so I'm having to use the 'can grow' option on my subheadings and detail sections to get the presentation looking OK...

Thanks for the quick reply..!

 

[dhookom]

I expect you could use a report level array and stick values into the array as you publish them in the page footer.

Duane
Hook'D on Access
MS Access MVP

 

[AlanWhaley]

That sounds very interesting...

I've searched the net and haven't found anything I could adapt?

Could you expand a little further?

Thanks

 

[dhookom]

Maybe you could share your code that prints the total in the page footers.

Duane
Hook'D on Access
MS Access MVP

 

[AlanWhaley]

The example can be found in this knowledge base article...

http://office.microsoft.com/en-us/access-help/summing-in-reports-HA001122444.aspx

Here are my versions...



Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
txtPageSum = txtPageSum + Totals

End If
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, _
PrintCount As Integer)
txtPageSum = 0

End Sub

So assumingly we extract the result of txtPageSum after each time it's generated (an addition to the PageHeaderSelection section seems logical)

Thanks

 

[dhookom]

I added a couple text boxes [txtPages] and [txtPageSums] to the Report Footer section and set their Can Grow properties to Yes. Add another text box [txtRptSum] to the Report Header section:
Control Source: =Sum([NetPrice])

Option Compare Database
Option Explicit
Dim arPageSums(10) As Variant  'Set the number to the maximum number of pages
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  If PrintCount = 1 Then
    Me.txtPageSum = Me.txtPageSum + Me.NetPrice
  End If
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
    arPageSums([Page]) = Me.txtPageSum
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)
  Me.txtPageSum = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtPageSums.Height = [Pages] * 300
    Me.txtPages.Height = [Pages] * 300
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
    Dim intPage As Integer
    Dim curLastPage As Currency
    If Me.PrintCount = 1 Then
        For intPage = 1 To [Pages] - 1
            curLastPage = curLastPage + Nz(arPageSums(intPage), 0)
            Me.txtPages = Me.txtPages & intPage & vbCrLf
            Me.txtPageSums = Me.txtPageSums & Format(arPageSums(intPage), "currency") & vbCrLf
        Next
        Me.txtPages = Me.txtPages & intPage
        Me.txtPageSums = Me.txtPageSums & Format(Me.txtRptSum - curLastPage, "currency")
    End If
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[AlanWhaley]

Hey Duane - you're a genius!

It worked really well. I have added a "Page" prefix to the page numbers, and changed their format to "000", but other than that, the concept is beautiful!

I really need to get my head around the VB functions more; the flexibility is really surprising to me...

Now for the next problem... : )