saving the value of a calculated field

[Dave Eliot]

On a payments form I have (currency) fields for different payments, ie
Membership, Pins, Contributions, etc. As I enter the amounts in each field
the Total Payment field automatically updates. It works just fine as I enter
the individual amounts -- I can see the correct total change on the form;
however, the total does not get saved when I close the form. In the table
view I can see the individual amounts, but the total is empty.

Here's what I have for the Data Control Source for TotalAmount:
=[MembershipAmount]+[DecalAmount]+[PinAmount]+[ColectaAmount]

What must I do to save the total?

Thanks in advance for any help. I appreciate it.

 

[Guest]

Dave,

You must do nothing at all. It's common sense not to store values from
totals. What you do is save the 'operants' and the outcome should be
calculated as you did via a textfield or either a field in a query. When
using a query you don;t have to look in the table because you don't save the
calculation.

 

[Ken Snell \(MVP\)]

A calculated control cannot save its data to the table directly because its
Control Source is an expression and thus cannot also be a field in the
form's underlying RecordSource query/table.

So, you'd need to program the form to write the value from the calculated
control into the appropriate field of the RecordSource query.

Is there a reason you need to store the total amount in the table, though?
Can't you just calculate the total from the other fields, thereby negating
the need to redundantly store the total?

 

[Scott McDaniel]

On a payments form I have (currency) fields for different payments, ie
Membership, Pins, Contributions, etc. As I enter the amounts in each field
the Total Payment field automatically updates. It works just fine as I enter
the individual amounts -- I can see the correct total change on the form;
however, the total does not get saved when I close the form. In the table
view I can see the individual amounts, but the total is empty.

Here's what I have for the Data Control Source for TotalAmount:
=[MembershipAmount]+[DecalAmount]+[PinAmount]+[ColectaAmount]

What must I do to save the total?

Generally, you should not store calculated values ... what happens if one of the root values change, and (for some
reason) the total doesn't get updated? Since you're obviously storing the root values, just calculate the TotalAmount
when needed, using the formula you list above.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[John W. Vinson]

What must I do to save the total?

Don't.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[Guest]

You have received a lot of good advice. Pay particular attention to Johm
Vinson. His comment pretty much covers it.
I would make one additional comment. Rather than perform the calculation
directly in the control source, I would suggest you do it in a public
function in a standard module and use the function for the calculation. The
reason for this is that you will probably need the calculation in reports or
queries and possibly other forms in your application. If you use a function,
you can be sure the calculation is always the same regardless of where you
call it from.

 

[Dave Eliot]

Thanks to you all.

I will not save the total.
I will not save the total.
I will not save the total.

Seriously, I appreciate all your input.

On a payments form I have (currency) fields for different payments, ie
Membership, Pins, Contributions, etc. As I enter the amounts in each field
the Total Payment field automatically updates. It works just fine as I
enter the individual amounts -- I can see the correct total change on the
form; however, the total does not get saved when I close the form. In the
table view I can see the individual amounts, but the total is empty.

Here's what I have for the Data Control Source for TotalAmount:
=[MembershipAmount]+[DecalAmount]+[PinAmount]+[ColectaAmount]

What must I do to save the total?

Thanks in advance for any help. I appreciate it.

 

[Scott McDaniel]

Thanks to you all.

I will not save the total.
I will not save the total.
I will not save the total.

LOL ... so are you sure you won't be saving the total?

Good luck with your project!

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[John W. Vinson]

Thanks to you all.

I will not save the total.
I will not save the total.
I will not save the total.

Seriously, I appreciate all your input.

"Alright, soldiers, cease firing... I think he's waving a white flag..."

Sorry for piling on, Dave.


John W. Vinson [MVP]