how to save sum of fields on the fly back to the record

  • Thread starter Thread starter MattE
  • Start date Start date
M

MattE

I have a form with a table as the control source. There are four
numeric fields the user can edit, during which time a fifth field shows
the current sum which changes as the user diddles with the numbers.
I'm having a mental block on how to get the sum back to the sum field
in the table.

It seems like I can get the sum from the table and then put it back
there, or I can have it keep an accurate sum while the user's editing,
but not both! Hope this question is clear.

Thanks for any kicks to the head!
 
Don't go there!

If you store a "calculated value" in your table, you will have to figure out
ways to ensure that nobody can alter it (or the underlying components)
without triggering a recalculation of all involved pieces. Keeping all the
numbers (components and calculated value) in sync is a headache, and
unnecessarily takes up room in your database.

Instead, make the calculated value appear on your form, but don't bother
saving it. A check through the tablesdbdesign newsgroup will provide
further support for this approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Matt

The correct answer is that there is no need to do this, and furthermore you
should not be doing it. You can easily calculate the sum of the four fields
in a query field:

SumOfFields: Nz([Fld1],0) + Nz([Fld2],0) + Nz([Fld3],0) + Nz([Fld4],0)

This calculated field can be bound to a textbox on your form or report to
display the total without storing it.

If you have a *compelling* reason for storing the total, then write a
function in your form module like this:

Private Function UpdateTotal()
TotalField = Nz([Fld1],0) + Nz([Fld2],0) + Nz([Fld3],0) + Nz([Fld4],0)
End Function

Then, in the AfterUpdate property of each of your updatable textboxes,
enter:
=UpdateTotal()
 
Back
Top