?
Solved

DSum in a continuous form control where previous record is used to make calculation on current record

Posted on 2005-04-02
8
Medium Priority
?
1,041 Views
Last Modified: 2008-01-09
Access 97: I have a form that is tied to one table.  The form basically is a "check register" and the table holds all checks and deposits in my checking account (this table is called CheckWriter.  The default view of the form is Continuous Forms.  This application was developed by someone else some time ago and I'm trying to change it.  The form contains the fields check number, check date, payee, check amount, deposit amount, and then running balance.  The form's data is basically the entire CheckWriter table.  The running balance control on the form is a DSum function adding the Deposit Amount and subtracting the CHeck Amount, the criteria also uses the TransID field in the tabel.  For various reasons I don't want to use the TransID field anymore. SO, my question is:  How can I do a running balance field, using the DSum function, but have it include the record above the record it's currently calculating, you know, like a real check register.  So I can do something like:  previous record running balance - check amount + deposit amount.
Thanks
Joe
0
Comment
Question by:JoeBoyd
[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
  • 3
  • 3
8 Comments
 
LVL 2

Accepted Solution

by:
LenWright earned 1000 total points
ID: 13689127
Running sum on a form is not as easy as a running sum on a report. Check out this link for a previously accepted answer:

http://www.experts-exchange.com/Databases/MS_Access/Q_20620901.html?query=running+sum+on+a+form&topics=39

0
 

Author Comment

by:JoeBoyd
ID: 13689169
Thanks LenWright.  The Accepted Answer in this link looks like it might be of use, BUT, unfortunately I'm pretty new at this Access stuff and I'm a little confused about the explanation.  What is the control name on the form?  And, how would I do my adding one number (deposit amount) and subtracting another number (check amount).  I'm really not just running a total of one column or field.
Thanks,
Joe
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13690632
Can you post the code you are using?

Bob
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:JoeBoyd
ID: 13691510
Bob,
Sure, here it is:
=DSum("0+[Deposit Amount]-[Payment Amount]","Check Register Display","[Trans ID] <=Forms![Check Register]![zCheck Register].Form![TransID] and Void <> Yes")

Deposit Amount, Payment Amount, and Trans ID are all fields in the table named Check Writer.  TransID field is an autonumber field that is also linked via a relationship with two other tables.

Check Register Display is the query used for the form Check Register which is the first form that opens when you want to view the check register.  The subform that really contains the continuous records of the table CHeck Writer is named zCheck Register.   The Trans ID field is hidden behind a control on the subform, not visible.

Thanks
Jioe
0
 
LVL 12

Assisted Solution

by:Bob Lamberson
Bob Lamberson earned 1000 total points
ID: 13694396
Since DSum is being used, I doubt that there is a runningSum field in your table. You will need to add that field to your table, then create some code behind the form to do what you are asking.

If you are comfortable with this approach I'll be glad to help with the code, so post back here if you would like to pursue it.

Bob
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 14342649
Len and I both contributed to what could be answers, but without joe's .............I say split the points.

Bob
0
 

Author Comment

by:JoeBoyd
ID: 14346921
Bob and Len,
I am very sorry about letting this question fall by the wayside.  I do not do Access development all the time, just more or less hit and miss, which is why I need help alot LOL.  Consequently, to be honest, I plain forgot to respond to this question I posted.  I hate to do this here.  This is such a great service and I really do respect it and everyone's time.  Please accept my apologizes.

I do agree with you Bob, I will spilt the points.  Both of you helped and probably equally.  I hope you both approve of this.  Thanks again
Joe
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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