Add Payment without manually entering the balance

  • Thread starter Thread starter NDClark
  • Start date Start date
N

NDClark

I have a Query / Form set up to deduct a single payment made and the balance
is accurately calculated for the field.

I want to be able to enter a payment on a form and a running balance to be
maintained. The problem is I have to enter the current balance each time. I
need the current balance to be presented in the text field of the Query /
Form when a payment is made. I have all the data being saved to a table.

Thanks for any help in this matter.
 
It's dead easy to display a running balance on a report (using the Running
Sum property of a text box), but not easy to do this reliably in a form.

That's because:
a) Users can sort the form differently (e.g. on amount not by date)
b) Users can filter the form (so not all amounts are shown)
c) There may be no strict order for transactions (e.g. where several are on
the same date and you aren't recording the time.)
d) Even after solving those issues, the form's data will be uneditable (if
you use subqueries), or very slow (if you use DSum() etc.)

Nevertheless, it's important NOT to store the balance, so you don't
compromise the integrity of your database. (For example, if someone goes
back and adds an old transaction later, every entry since then will be
wrong.)

Simplest answer: use a report to get the running balance.

Alternatives: subquery:
http://allenbrowne.com/subquery-01.html
or DSum() expression:
http://allenbrowne.com/casu-07.html
(DSum() doesn't reflect the current record until it is saved.)
 
Thanks Allen. I am still not sure how best to display this data. I do
understand though what not to do. Forms are not the way to go with what I am
trying to do.
 
To do it with a report:

1. Create a query that contains the fields from the tables you want
(assuming there's more than one table involved.)

2. Create a report based on this query.

3. Put the fields you want onto the report. Add an extra text box to display
the running sum, and set its Running Sum property (on the Data tab of the
properties box) to Over Group (or Over All.)

Depending how your tables are set up, it may be more involved. The example
above would work if you had ONE field with positive and negative values for
credits and debits.

If you have 2 fields named Due and Paid, you would type an expression into
the Field row in query design:
IIf([Due] Is Null, 0, -[Due]) + IIf([Paid] Is Null, 0, [Paid])
You now have the field set up the way you need so the running sum works in
the report.
 
Back
Top