How to save a field calculated on a form to its table

[TopWebb]

I have a form into which I enter items purchased, description, quantity, price, etc. This info is saved to a table. I would like to capture the total initial cost in the table as well so that even if the price or quantity subsequently change, I know the initial cost.

[ItsMe]

You can create a new field/column in your table and bind a new control to the new field.

Me.NewControl.Value = Me.CalculatedControl.Value

[TopWebb]

Thanks for the suggestion. I tried something like that before I made the post and just tried again, without success.
I placed into the 'Control Source' the following: [widgets]![Total Cost]=[quantity]*[cost]. The formula works fine by itself ([quantity]*[cost]) but, of course, doesn't save anything to the table. Adding the [widgets]![Total Cost] before the calculation doesn't cause it to save anything.
I suspect I need to put something into one of the 'On ...' fields, perhaps 'On Exit'. I haven't used these in the past and need some guidance.

[ssanfu]

One method:

Add a hidden control to the form bound to the new field. Use the FORM BeforeUpdate event to put the calculated value into the new hidden control.

"TotalCost" is the NEW hidden control (notice NO spaces)
"TotalCalcCost" is the visible calulcated control (notice NO spaces)

In the FORM BeforeUpdate event:
Me.[widgets]![TotalCost] = Me.[widgets]![TotalCalcCost]

[ItsMe]

You can create a new event handler in the VBA module by going to the property sheet and selecting the Event tab. You will want to make sure that you are viewing the properties for the Form.
.


From there you will want to click the ellipses (...) next to the appropriate event. Then, select code builder. This will take you to the IDE and the display your new sub procedure. Place your code in the middle.

Here is an illustration of focus on the RowSource property. You can see the ellipsis there.
.

[June7]

Don't think the hidden bound control is necessary. Can just populate the field.

Me!TotalCost = Me.TotalCalcCost

Actually, I recommend not saving the calculated total, instead save the raw data (quantity, price, tax rate, etc) and do calc when needed. Saving calculated data (data dependent on other data) is usually bad idea.

[TopWebb]

Steve -
Thank you for your suggestion. I added a control that I could eventually hide and bound it to the new, Total Cost field in the related table.
Then I tried various different ways to calculate the value. I put it into the before and also the after field in the Properties for the Control. I also tried it in the Before and also the After field in the Properties for the Form. Nothing seemed to make a difference. I also tried '=[TotalCalcCost]'
Some notes: Since this control on the form was referencing other controls on the same form, the [widgets]! did not show. Also, I entered "Me." at the beginning. Was that supposed to be a substitute for something else?
I suspect this is something relatively simple that I have just not been able to find. I appreciate your assistance.
btw, I really like your tag: Veni, Vidi, Velcro.
-Webb

[June7]

Me is a VBA alias for the form or report object the code is behind.

Did you consider my comments in prior post?

[MarvinM]

TopWebb,

June is absolutely right about it being wise not to store calculated values in a table...
unless you have a good reason for it. I saw in your original post that the factors (like price) could change, but you do not want the calculated value to change accordingly. I would call that a good reason.

As for your question, your suspicion is correct. I would use the On Current event for the Form. Let's say the name of your textbox control where you calculate the total is named TotalCalcCost (Please don't use spaces.) And let's say that the field in your bound table that you want to populate is named TotInitCost. You are correct to put the source for your control =[quantity]*[cost]. Then for the On Current event for the form (click on the ellipses) add one line of code between the "Sub" and "End Sub" lines like this:

Private Sub Form_Current()
     TotInitCost = TotalCalcCost
End Sub

That should do it. Good Luck!

[June7]

That's why I suggested storing the price (and other raw data that could change) in effect at the time of the order. It's called a paper trail and CYA. It documents what contributes to the calculated value.

[MarvinM]

Ah, Yes!
That is a very good point. If there is a fixed price for the product, he could have a price history for each item. I think that's called Type 2 data. If the prices change from one order to another, it may be due to a discount, which would need to be tracked too. This sounds like it is a table of Items Purchased, rather than something which he would have any control over the price. I like your thinking. It is better to keep track of the details.

[ssanfu]

@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

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:

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....

[June7]

@Steve, interesting - I have lots of code saving values to fields without a bound control. Would have been a real headache if I had to create a bunch of hidden textboxes.

[TopWebb]

Thank you for this clarification although I'm not too sure how to use the info.
I am a fairly competent user of Access in that I can design good systems to handle fairly complex requirements. However, I only use the tools I find in the Tables, Queries, Forms, Reports, and Macros. I'm aware that some more indepth programming is available, but I have never taken that task on.
As far as the reason to save the calculated value, for this application, I wish to save the initial financial outlay. While quantity or cost may change over time and I will be tracking that, I also wish to keep a record of the initial cost. Perhaps the best way is to just enter it and not try to get fancy by making the calculation.

[ssanfu]

@June, I tried what you posted in Post #6. Maybe I entered something wrong. I'll try again.

The field to update is in the form record source, not bound to a control.

You use VBA like "Me!FieldName = Me.CalcAmount" where "Me!FieldName" refers to a field in the record source and "Me.CalcAmount" is the new calculated data in a control on a form??