Carry the page total to another page first line

[Irshad Alam]

I have a report of few pages, my requirement is that I want a field on each
page for the page total and in the next page it should have the first line as
the last page total stating as ...carried the total till page No 1 or so on
for the next pages.

I mean if the first page total is 1245, then the second page should start
the first line of the page should be : Carried total till last page 1245,
and the below line all data. then again at the last of the page - the page
total field should have 1245 plus all the data of the page and the same so on
the next pages.

please advise me method, code or any sample available to do this.

Regards

Irshad

 

[Marshall Barton]

I have a report of few pages, my requirement is that I want a field on each
page for the page total and in the next page it should have the first line as
the last page total stating as ...carried the total till page No 1 or so on
for the next pages.

I mean if the first page total is 1245, then the second page should start
the first line of the page should be : Carried total till last page 1245,
and the below line all data. then again at the last of the page - the page
total field should have 1245 plus all the data of the page and the same so on
the next pages.

Use a running sum text box (named txtRunTotal) to calculate
the running total. Then you can use code in the page footer
section's Format event to put the value in both the page
header and page footer text boxes:

Me.txtHdrTotal = Me.txtRunTotal
Me.txtFtrTotal = Me.txtRunTotal

 

[Irshad Alam]

Sir,

I followed your advise and did the following and an error produced, the
details is as below:

I added a text box in the details section - named it "txtRunTotal", control
source set it to field I wanted to total, RunningSum - selected to OverAll.
Added One textbox to pageHeader Section and Another TextBox to pagefooter
section.
Pasted the below code to pagefooter OnFormat section:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageHeaderText = txtRunTotal
Me.PageFooterText = txtRunTotal
End Sub

All the pages figures shown in the Pageheader Text and pagefooter text are
correct except the report first pageheader value and lastpage header value is
not correct, In this first and last pages it shows the grand total value.

Please check the above method and advice me how to hide the first page only
textheader (as u can understand the first page does not requires/cannot find
the last page total value and it will look bad). And how to correct the last
page header text (It should have the value of the second last page).

You advice will be very much awaited, which will help me to complete a long
pending issue.

Regards

Irshad

 

[strive4peace]

Report PageHeader: display Balance from previous page
~~~

Hi Irshad,

It is difficult to get Access to do certain things in the Page Header
and Page Footer sections -- they do not behave like group sections.

if you cannot get the current method to work, here is another one...

this will carry a balance to the top of the next page ... you can apply
the analogy to what you want displayed at the bottom of the page...

The value in Controlname is what we are adding

Bal is an unbound control in the detail section

label_BegBal is a LABEL control in the page header and the caption is
changed (don't know why that works and using a textbox doesn't...)

code behind report:

'~~~~~~~~~~~~~~~~~~~~~~~~~
'--- dimension module level variables
Dim gBalance As Currency, gBalanceLast As Currency

'--- update balances for each record
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
If Not IsNull(Me.Bal) Then gBalanceLast = Me.Bal
gBalance = gBalance + Me.controlname
Me.Bal = gBalance
End If
End Sub

'--- write the current balance to the page header
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.Label_BegBal.Caption = Format(gBalanceLast, "#,##0")
End Sub

'--- initial values for variables
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
gBalance = 0
gBalanceLast = gBalance
End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Marshall Barton]

I followed your advise and did the following and an error produced, the
details is as below:

I added a text box in the details section - named it "txtRunTotal", control
source set it to field I wanted to total, RunningSum - selected to OverAll.
Added One textbox to pageHeader Section and Another TextBox to pagefooter
section.
Pasted the below code to pagefooter OnFormat section:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.PageHeaderText = txtRunTotal
Me.PageFooterText = txtRunTotal
End Sub

All the pages figures shown in the Pageheader Text and pagefooter text are
correct except the report first pageheader value and lastpage header value is
not correct, In this first and last pages it shows the grand total value.

Please check the above method and advice me how to hide the first page only
textheader (as u can understand the first page does not requires/cannot find
the last page total value and it will look bad). And how to correct the last
page header text (It should have the value of the second last page).

The only way I can see the first page header displaying the
grand total is if you are using a text box with an
expression like:
=Page & " of " & Pages
You can hide the first page header with this kind of code in
the page header's Format event:
Me.PageHeaderText.Visible = (Me.Page > 1)

The last page header showing the grand total is more of a
mystery. The only way I can think of that happening is if
you preview the report and skip directly from the first page
to the last page. In any case, I think the use of Pages
requires that you add a line of code to the ReportHeader
section's Format event:
Me.PageHeaderText = 0

If you go through the pages sequentially in preview should
display the correct values, but randomly jumping around the
pages may still display the last viewed page total in the
header. Regardless of all that, the printed copy should be
correct.

 

[Marshall Barton]

Report PageHeader: display Balance from previous page
~~~
It is difficult to get Access to do certain things in the Page Header
and Page Footer sections -- they do not behave like group sections.

if you cannot get the current method to work, here is another one...

this will carry a balance to the top of the next page ... you can apply
the analogy to what you want displayed at the bottom of the page...

The value in Controlname is what we are adding

Bal is an unbound control in the detail section

label_BegBal is a LABEL control in the page header and the caption is
changed (don't know why that works and using a textbox doesn't...)

code behind report:

'~~~~~~~~~~~~~~~~~~~~~~~~~
'--- dimension module level variables
Dim gBalance As Currency, gBalanceLast As Currency

'--- update balances for each record
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
If Not IsNull(Me.Bal) Then gBalanceLast = Me.Bal
gBalance = gBalance + Me.controlname
Me.Bal = gBalance
End If
End Sub

'--- write the current balance to the page header
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
Me.Label_BegBal.Caption = Format(gBalanceLast, "#,##0")
End Sub

'--- initial values for variables
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
gBalance = 0
gBalanceLast = gBalance
End Sub

Crystal, that approach is at best "delicate" and at worst
won't work. By "delicate", I mean that it might work in
some tests but not in others and even if it works for all
test cases today, some seemingly innocuous design change can
break it tomorrow.

Even checking FormatCount = 1 (or PrintCount = 1 in the
Print event) is not reliable because "large" retreats caused
by CanGrow and the various KeepTogethers can reset the
Format/Print Counts and throw the module level variable off.
Skipping around in preview mode can also produce odd looking
results.

AFAIK, there is absolutely no way to compare/calculate
values across multiple records using VBA code (hence the use
of running sum). I have noticed that using a text box
instead of a module level variable might make a difference,
but as my approach to Irshad's situation demonstrates, it's
not foolproof either.

 

[Irshad Alam]

Sir,

After reading your reply I tested the mdb and noticed the following:

I am able to hide the first page PageHeaderText-Box by writing the code you
advised.

Secondly I noticed from your reply and checked for the last page grandtotal
figure, its correct if I go page wise, It is correct if I print. Only the
problem occurs if I jump direcltly to last page preview. This I tried to make
understand the user and warned him the same, that he should follow it.

Now, Another requirement of the user is that he want to label of the
textheader having a meaningful label, like for example on the second page
textheader label it should be ''Continued..page..total..till..pageNo.1..'',
next page should have the label as
''Continued..page..total..till..pageNo.2..'' and so on till next.

I tried but failed, please complete this by advising, so that the issue can
also be resolved.

Regards

Irshad

 

[Marshall Barton]

After reading your reply I tested the mdb and noticed the following:

I am able to hide the first page PageHeaderText-Box by writing the code you
advised.

Secondly I noticed from your reply and checked for the last page grandtotal
figure, its correct if I go page wise, It is correct if I print. Only the
problem occurs if I jump direcltly to last page preview. This I tried to make
understand the user and warned him the same, that he should follow it.

Now, Another requirement of the user is that he want to label of the
textheader having a meaningful label, like for example on the second page
textheader label it should be ''Continued..page..total..till..pageNo.1..'',
next page should have the label as
''Continued..page..total..till..pageNo.2..'' and so on till next.

I tried but failed, please complete this by advising, so that the issue can
also be resolved.

What did you try and what did it do that you are having a
problem getting to work?

From what you said above, I will tkae a stab in the dark
that the page header text box could use a text box
expression like:
=''Continued..page..total..till..pageNo." & Page-1

 

[Irshad Alam]

Thank you so much, It worked perfect and it completed my requirement.

Actually I was trying if..then..method to get it done, anyhow your valuable
advise let me complete it.

Best regards

Irshad