@June, I've always had to reference a control on a form to be able to store a calculated value. I tried using the bang instead of the dot, but the value wasn't stored. I had to have the control on the form.
@TopWebb
OK, to store the total initial cost you need a field in the form record source. So add a field to the appropriate table. I would name it something like "TotInitCost".
Make sure the new field appears in the form record source. I say form record source because I base all of my forms on queries.
In the query I have a column like "TotCostcalc: [quantity]*[cost]". I like doing calculations in queries, not in a form.
Next, bind the control on the form named "[CostCalc]" to the field in the query named "TotCostCalc".
Now we need to get the calculated value from the "[CostCalc]" control and put it in the field "TotInitCost".
Add a form event. Set the form before update event to
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.TotalCost = Me.costCalc
End Sub
If you ever change the quanity or code in this record, the initial cost will also change. You could prevent that by adding a test like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.TotalCost) Then
Me.TotalCost = Me.costCalc
End If
End Sub
Once this is working, don't forget to hide the total initial cost control (set the visible property to false).
This is the method I use. I also have to save calculated values due source values changing and I have to keep a history. I try not to save calculated values, but sometimes it is necessary.
---
attached is an Access 2000 mdb....