running balance in a form (or query)

[Guest]

I have a form with payment amount, charge and balance for lessons. Lessons
are usually paid in groups of four. I would like to calculate a balance in a
form where a person could pay, then if 0 were typed in for the 3 subsequent
payments, the balance would still be deducted. (Or if a negative balance
were reached, that would be calculated as well). I have this in a report
using the NZ function and running sum property to "over group", then sorted
by account. However I don't have this option in a query or a form!

I'm sorry if this question has already been answered; as far as I could
find none of the solutions already posted quite matched my needs.

Any help would be appreciated,
Tarnia

 

[Allen Browne]

Hi Tarnia

This is not as easy a question as you might imagine. Forms are dynamic
things, where users apply filters and change the sort order on the fly. If
your form/query should still give correct answers when that happens, you can
see that things get involved. OTOH, it is dead-easy to do in a report, using
the Running Sum property of a text box.

The solution will probably be to use a subquery to calculate the progressive
total. If that's a new idea, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If the form needs to be updatable, then you will need to use a DSum()
expression instead of a subquery. You will find this very slow and
inefficient.