Save result of a calculation or function into a table

[thalam]

Hi there,

I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.

But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?

Thanks

 

[Pat Hartman]

If you are attempting to update the current record, there is no need to run a query at all. Let me tell you one more time, the odds are 99% that you should NOT be storing this value. Are you really sure that you know so much more than the experts who are telling you not to store the value?

To store a calculated value that is stored in an unbound control, add a SINGLE line of code to the form's BeforeUpdate event.

Me.StoredField = Me.CalculatedControl

 

[thalam]

The reason I am trying to save the value into a table is because there are many errors that pop up when I try to recalculate the field.

For example, my calculated field calculates cost*Percentage+Cost = Selling Price, this is the price that the client will see. These are for parts in a machine. Now a machine will have more than just one part. So I will have to calculate this formula for each part, then i need to calculate the total of the selling price. This is where I keep getting an error, whether it be on a form or a report. If I have the value saved in a table, i simply say sum up this field. This is the reason as to why I want to save it into a table. If you have any other suggestion that would be helpful.

I tried the method you showed above, but it still does not work.

 

[Pat Hartman]

Add the calculation to your query.

Select ...., (cost * Percentage) + Cost As SellingPrice
From YourTable;

You can then bind the SellingPrice field to a control. Add a footer to the subform and you can sum the selling price for the items. Use the following as the ControlSource for the control in the footer.

=Sum(SellingPrice)

PS - do not use spaces or special characters in your column or other object names.