1desman1
asked on
Access balance forward
Hi experts,
I need to run a balance forward, i have a dsum on the page header that is the previous balance and i need to carry it to the recordset. I have it working with the following exception, it the report goes to a new page it adds the 1st entry on that page twice...
bbf is the dsum
cr is the recordset
b is the running sum that code outputs = help
I need to run a balance forward, i have a dsum on the page header that is the previous balance and i need to carry it to the recordset. I have it working with the following exception, it the report goes to a new page it adds the 1st entry on that page twice...
bbf is the dsum
cr is the recordset
b is the running sum that code outputs = help
Option Compare Database
Dim x As Integer
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
x = 0
End Sub
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If x = 0 Then
b = Nz(cr, 0) + Nz(bbf, 0)
x = 1
Else
b = Nz(cr, 0) + b.OldValue
End If
End Sub
Hi 1desman1,
Have you tried using the Running Sum feature of Report Textboxes?
Gary
Have you tried using the Running Sum feature of Report Textboxes?
Gary
ASKER
I'm renowned for being vague... sorry, also i pulled the above code off this site... just trying it get it to work for me
What I need it a running sum with a before balance i.e in the header i have a calculated field that prints as follows:
balance = 100
Cr.......... Balance
10......... 110
10.........120
ect the above code does work BUT on the second page it does this:
Cr.......... Balance
10......... 140 (counts the 2nd page first total twice, should be 130)
Any clearer?
Thanks
What I need it a running sum with a before balance i.e in the header i have a calculated field that prints as follows:
balance = 100
Cr.......... Balance
10......... 110
10.........120
ect the above code does work BUT on the second page it does this:
Cr.......... Balance
10......... 140 (counts the 2nd page first total twice, should be 130)
Any clearer?
Thanks
ASKER
@ tbsgadi
yes but it counts the balance for every recordset
yes but it counts the balance for every recordset
ASKER
i've attached a demo database of what i'm talking about, preview the report and look at the second page.
if anyone can achieve what i'm after would be great ~ Thanks
test.mdb
if anyone can achieve what i'm after would be great ~ Thanks
test.mdb
ASKER
help
I would sill be interested in how often the events are triggered. If the Detail_Format is being triggered twice then there is your problem.
Looking at your DB now...
Looking at your DB now...
Hmmm...Your database shows the balance as a static field and I only get one (very long) page in the report. Are you able to post your stripped down database with the acutal report?
ASKER
thanks slamhound, i did try that but nothing displayed different.. i hit ctrl+g and it opened the VB editor. not sure what to do
ASKER
if you view in print it displays pages, did you do that? there is a form in there called Report which open in print view
thanks again
thanks again
ASKER
I see what you're saying with the Debug.Print "I'm here!" (in the message dialog in VB editor) it only printed once
What version of Access are you using?
ASKER
2007
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, so it must be a Access bug... but IF there are 2 identical amounts there will be problem???
My only fear is that the subtraction code I gave you will take the Cr from the current record on page 2 and not the old record on page 1. If they are identical it will work fine. The only way to tell is to try it out with some variations.
Add debug.print "I'm here!" in the main Sub to make sure it's only being executed once. Hit Ctrl-G to see the output. If it's being hit twice per page then it's not surpirsing you're getting doubles.