Saving calculated field from a Form into a table

[AlbaraaF]

We have a form that contains a calculated field ( the total price of restaurant order), and we want to save the value from the calculated field into it's table.

**Note:
this form is from TXN table and it has a subform as you can see from ORDER table.

[zmbd]

AlbaraaF
While this can be done, the usual recommendation is against storing calculated values.

If you need the calculation for a report then I suggest that you move your form's record source into a stored query, include the calculated value as one of the calculated field.

Looks like you are simply calculating an invoice total?

[NeoPa]

Hi.

Instead of using a formula on your Form for the calculated total, you should have a bound Control that gets updated whenever you enter or change the pertinent data on the Form. Does that make sense?

[NeoPa]

It looks like I cross-posted with Zmbd.

The points he makes about storing calculated values are worth considering. One of the frequent exceptions to this 'rule' is with invoice values, due to prices fluctuating over time and the calculated result being impossible, or sometimes just very difficult, to reproduce reliably.

It's worth making sure how this fits with your own database design. If it's possible to reproduce the calculations reliably, even at a very much later date, then it's not advisable to store the result at all.

However, if you do need to do so, my earlier advice shows you how best to do that.

[zmbd]

NeoPa,
One workaround I have done with fluctuating pricing is to store the price at time of purchase/sale. Same thing with discounts on pricing.

What do you think about something along these lines (very simplified - I haven't included shipping, taxes, fees, etc...):
[Prts]
[PK][FK_Spplyr][SpplyrPrtNum][PrtsDscrptn]

[PrtPrchs]
[PK][FK_Parts][PrcsAtPrchs][DscntAtPrchs]

[Invoice]
[PK][InvDate][FK_PrchOrdr][FK_PrtPrchs][UnitOrdrd][UntsRcvd]

The query against the "purchase order (PO#)" (or the "invoice number" should more than one PO# be allowed per invoice, something my company doesn't allow but others may) to pull the invoice together for the total pricing.

[NeoPa]

That can work Z. I only say that sometimes there are exceptions. Each exception can have alternative solutions, and yours minimises the non-normalised data, but not everyone wants to handle such complicated situations.

I think mostly, as well as warning of some of the possible problems, we should answer the question directly. Although we're often more experienced in the technical side of the subject, we rarely have the full story that the OP has to deal with.