Page total in access report

[mtomar]

How to get total amount for each page of access report.

 

[LarryDeLaruelle]

Can't do it. Access only supports totals in report and group headers and footers. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Steve101]

You can produce page totals, but you have to do it via code. For example, assume you have a bound numeric control in the detail section of the form called v1, and you wish to Page sum it. Then:

(a) Make sure you expose the Page Header and Page Footer sections (via the Viewe Page Header/Footer menu options)

(b) Add an unbound control called Sumv1 to the page Footer section (vertically under the v1 entry)

(c) Add the following code behind the form, to trigger the the page header and page footer format events:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Sumv1 = 0
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Sumv1 = Sumv1 + v1
End Sub

(d) Modify the report and code appropriately, according to your particular needs.

Run it; it should produce the page totals and resets at the end and start of each page respectively.

Hope this helps,
Cheers,
Steve

 

[Steve101]

You can produce page totals, but you have to do it via code. For example, assume you have a bound numeric control in the detail section of the form called v1, and you wish to Page sum it. Then:

(a) Make sure you expose the Page Header and Page Footer sections (via the Viewe Page Header/Footer menu options)

(b) Add an unbound control called Sumv1 to the page Footer section (vertically under the v1 entry)

(c) Add the following code behind the form, to trigger the the page header and page footer format events:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Sumv1 = 0
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Sumv1 = Sumv1 + v1
End Sub

(d) Modify the report and code appropriately, according to your particular needs.

Run it; it should produce the page totals and resets at the end and start of each page respectively.

Hope this helps,
Cheers,
Steve

 

[Steve101]

Sorry about the double post above. Not sure how this happened; got some form of error and assumed that the first post failed.

 

[mrderek]

I tried the code as written above (modified for my report) but the total calculated comes out significantly higher than the actual total. I have several groupings on the report but don't see how that would make a difference since this is acting only on the detail section. The groups may be complete on a page or may span the page break, can this make a difference?

Thanks,

Derek

 

[LarryDeLaruelle]

Steve:

I think the problem Derek is encountering is the retreat action that occurs when Access moves from on page to the next. I've encountered this before and still haven't found a solution.

Do you have a way to handle this?

Thanks. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Steve101]

Derek, Larry

Sorry this reply has taken a while. I've been somewhat inactive on tek-tips for some time; more pressing matters unfortunately.

I think that the problem may be caused by Access first putting a line on one page, then deciding that it should be placed on the next page. Try adding the line:

Sumv1 = Sumv1 - v1

to the OnRetreat event associated with the detail line on the report; this should compensate for this problem. Please let me know if it works,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)