storing a calculated field from a subform

[Guest]

I have a small database to keep track of inventory. A subform keeps the
items, categories, prices, quantities and I calculate a line total and a Sub
total (the sum of all purchases). I need to use this Sub Total in a
calculation on the master form to be stored with client and date info. I
will use this Grand Total for reports. How can I do this?

 

[Guest]

If you want to store the data in the master form's underlying table, in the
AfterUpdate of the subform, add something like this:

Priivate Sub Form_AfterUpdate
[Forms]![MasterForm]![TotalField] = [Forms]![Subform]![Quantity] *
[Forms]![Subform]![UnitPrice]
End Sub

This will force the bound control on the master form to become the new
value. It could potentially be undone if the user returned the focus to a
control on the master form and pressed ESC, however, so you may also want to
recalculate this total when closing the master form.

If you just want to calculate & show the data in an unbound text box on the
master form (without saving it, since it can be recalculated at report time),
then just make an unbound TotalField text box and set its ControlSource to:

=[Forms]![Subform]![Quantity] * [Forms]![Subform]![UnitPrice]

This will update automatically when either the quantity or unit price
changes on the subform.

Of course, in either case, you will probably actually want to do a DSum of
the Subform's data source to total all records on the subform related to the
master form.

 

[Guest]

Normally calculated values should not be stored in a field in a table ...
This makes the value static and could possibly result in an incorrect value
should any value that the result is based on should get altered for any
reason ...
You should instead calculated the value "on the fly" dynamically when the
value is needed using an expression ...
This is the only way that you can be sure that the calculated result is 100%
correct at the time of execution ...

R. Hicks