report page problem... 1

[adsfx]

Hi - am stuck,
this is probably simple but I cant crack it.
I am working on an 'Accounts Ledger' report, where transactions are listed and at the end of report a 'Balance' is displayed.
However I need a balance at the end of each page.
The sums at the end of each page are calculated by:
thread703-326104 from kosmokramer

However- the start balance is held in a table and I need each page to display the running balance,
and I would like the previous page balance to be displayed at the top of the new page

 

[PaulBricker]

OK, here you go. In the report you will have a textboxes, one in the Pageheader, one in the detail section and one in the page footer. I've named them txtPageHeader, txtDetail and txtPageFooter for this example. You can change them as you need.

In the Format Event for the page header put
If Me.Page = 1 Then
Me.txtPageHeader = DLookUp("FieldName","TableName&quot
Me.txtDetail = 0
Else
Me.txtDetail = Me.txtPageFooter
Me.txtPageHeader = Me.txtPageFooter
End If

In the Format Event for the Detail Section put

Me.txtDetail = Me.txtDetail + Me.FieldToBeTotaled

In the Format Event for the Page Footer put

Me.txtPageFooter = Me.txtDetail

This should do what you need.

Post back with any problems.

Paul

 

[adsfx]

thanx a lot Paul...heres a star

however, in the detail event where:
Me.txtDetail = Me.txtDetail + Me.FieldToBeTotaled

I need to subtract Me.paybalance and add Me.incbalance from the total balance
(paybalance and incbalance are txtboxes in the detail section)

I tried:
Me.txtdetail = (Me.txtdetail + Me.incbalance)- Me.paybalance

thanx a hell of a lot for your help so far

cheers Mark

 

[PaulBricker]

Well I tried using the same type of expression and did not have any errors. That doesn't mean that you would get the info the way you need but I used the expression
Me.txtDetail = Me.txtDetail + (Me.Total - Me.TimeAllocated)

and that returned values for each record as I expected. Can you give me a little more info on where this is breaking down for you. You may need to combine some of my info with the original info you got from kosmo.
Thanks

Paul

 

[adsfx]

Hi,
am having a prob getting the pagefooter balance to work,
at the moment code is:
Me.txtpagefooter = Me.txtdetail + Me.txtpageheader

this should add the last txtdetail result with the balance
(txtpageheader) at the top of the page?

but im not getting correct result ne ideas?

Mark

 

[PaulBricker]

Mark, I've backed up on this a little. In looking at some of my data just using the original method outlined by CosmoKramer, my numbers are showing some errors. I will follow up on it this evening and hopefully post something back tomorrow. In the mean time, please look at your numbers again and let me know if you see any issues with the last number in txtDetail and the result displayed in txtpagefooter. My result adds a value to the results twice (once at the bottom of the first page and then again at the top of the second page) and ends up with an incorrect balance.

Paul

 

[PaulBricker]

Mark, I've had no luck trying to get individual page totals to add up. The first page is correct, but the rest of the page totals are not. I'm not sure where to go from here so I'll wait till I hear from you.

Paul

 

[PaulBricker]

Mark, the original post by CosmoKramer had a small mistake in it. You need to put the code

Me.txtdetail = (Me.txtdetail + Me.incbalance)- Me.paybalance

in the Print Event for the Detail section instead of the Format Event. That should improve your totals. Then I think this is what you need to do. First, add another textbox to the page footer. I called mine txtpagetotal and the control source is
=txtDetail

Then this is the code I ran to accomplish what you are looking for.
First, in the Page Footer Format Event I put

If Me.Page = 1 Then
Me.txtpagefooter = Me.txtDetail
Else
Me.txtpagefooter = Me.txtDetail + Me.txtpageheader
End If

Then in the Page Header Format Event I have

If Me.Page = 1 Then
Me.txtpageheader = 0
Else
Me.txtpageheader = Me.txtpagetotal
End If
Me.txtDetail = 0

This give me the page totals in the textbox txtpagetotal, it gives me the txtDetail + txtHeader totals in the txtpagefooter textbox.

Try it out and let me know. Thank dhookum for his getting the page totals straightened out.

Paul

 

[adsfx]

Cheers Paul,
just got the results i wanted...thanx alot for your help.
Didnt use txtpagetotal in the end
The main thing was putting the code in the detail print event section...like you said - why is this?- it was screwing the whole lot up!
if your interested had to change yur code to the following:

[header section]
If Me.Page = 1 Then
Me.txtpageheader = DLookup("balance", "tbl_balance&quot
Else
Me.txtpageheader = Me.txtpagefooter
End If
Me.txtdetail = 0

[footer section]
If Me.Page = 1 Then
Me.txtpagefooter = Me.txtdetail + Me.txtpageheader
Else
Me.txtpagefooter = Me.txtdetail + Me.txtpageheader *****
End If

****: this line doesnt do anything? - if i take it out
results are wrong!

Regards Mark

 

[adsfx]

Hi,
Ive been dreading this while working on this report-
but if a user selects a date range for the results the balance is only going to be applicable to the records on the report at that time, is there anyway to display the results as just part of the original query - but blank out unneeded results? what is snapshot?

 

[PaulBricker]

I don't think a snapshot is what you need. That just saves a report to a different file extension. It enables emailing reports to people that don't have Access loaded on their computers, kind of MS's answer to Adobe.
If I understand what you need, what you may have to do is create a Totals query the returns the values you want to see and then use a DLookup to pull that info into your Report and maybe set the visible property for some of your other textboxes accordingly. Your Totals query could reflect either the total for all the info or it could reflect the total just for the date range selected on your form.


Paul