Commiting form field data to database

[cantech2]

Hello,

I have a form called BILLS with a field called LABILL
I also have a subform called LABTEST with a calculation field called TOTAL.

I have set the value of LABBILL to be euqal to the calculated value in TOTAL.

The above works well on the form but does not show up on displayed on the form but is not committed to the database.

How can I commit the value in LABBILL to the database. If there is a change in the calculated value of TOTAL, LABBILL will change on the form, I hope the new value will overwrite the old value in the database.

Thanks,
Humphrey.

 

[PHV]

You are storing derived values in your database ?
This is often considering as a bad practice.
Calculated values should be set within queries or report to preserve accuracy.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Humphrey

Following up on PHV (Super Tek of the week - congrats PH), For the very reason you are posting your question is why storing calculations breaks one of the rules for normalization of a relational database...

If there is a change in the calculated value of TOTAL, LABBILL will change on the form, I hope the new value will overwrite the old value in the database

Fortunately, you can calcualte the value every time you view the form or run a report. A common approach is to include an unbound field on the form or report, and use DSUM as the control source...

DSUM ("[FieldUsedForCalc]", "[YourTable]", "[IDfield] = " & YourMatchingID)

For example, for an sales order invoice...

DSUM("[SalesQty]*[SalesPrice]", "OrderDetailTbl", "[InvoiceNo] = " & InvoiceNo)

Richard

 

[PHV]

willir,
first: thanks for the nice words about me
second: thanks for your jump in this thread to add some clarity.