?
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,074 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
  • 3
  • 3
7 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

578 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