Solved

Access balance forward

Posted on 2008-10-01
16
329 Views
Last Modified: 2011-10-19
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

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

Open in new window

0
Comment
Question by:1desman1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 10

Expert Comment

by:slamhound
ID: 22621547
Still trying to get my head around your logic but one thing you can try...

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.
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 22621664
Hi 1desman1,

Have you tried using the Running Sum feature of Report Textboxes?

Gary
0
 

Author Comment

by:1desman1
ID: 22621677
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:1desman1
ID: 22621691
@ tbsgadi

yes but it counts the balance for every recordset
0
 

Author Comment

by:1desman1
ID: 22629324
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
0
 

Author Comment

by:1desman1
ID: 22630085
help
0
 
LVL 10

Expert Comment

by:slamhound
ID: 22630132
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...

0
 
LVL 10

Expert Comment

by:slamhound
ID: 22630154
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?
0
 

Author Comment

by:1desman1
ID: 22630186
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
0
 

Author Comment

by:1desman1
ID: 22630205
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
0
 

Author Comment

by:1desman1
ID: 22630286
I see what you're saying with the Debug.Print "I'm here!" (in the message dialog in VB editor) it only printed once
0
 
LVL 10

Expert Comment

by:slamhound
ID: 22630306
What version of Access are you using?
0
 

Author Comment

by:1desman1
ID: 22630308
2007
0
 
LVL 10

Accepted Solution

by:
slamhound earned 500 total points
ID: 22630451
This is a cheat because I can't see how Access is messing this up.

Add:
b = b - Nz(Cr, 0)

to the form's On Page event. This works when Cr is identical so check to make sure the system is using the right Cr.

0
 

Author Comment

by:1desman1
ID: 22630497
Thanks, so it must be a Access bug... but IF there are 2 identical amounts there will be problem???
0
 
LVL 10

Expert Comment

by:slamhound
ID: 22630532
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question