Saving to Table (2.0 :()

Topic

New Reply

…hopefully I am just missing something….

I have designed a form that my company uses to log quotes in and post to a table.
I am having a problem getting the total quote amount to post to the table.
Right now it is a calculated field that multiplies the dollar amount by the piece amount.
I have thought about putting an invisible control source for the total amount but cannot get the calculated field to mirror/copy/auto-update to the control sourced field.
I have thought about setting an update query to run independently and update the total amount column.
I have thought about omitting it entirely and just doing the calculations in a subsequent query.
Then I thought about asking for help.

Which do you recommend and did I miss something?

Thanks,
Mark

Reply | Quote

Replies

Just my opinion … I do not store these type of calculated fields in a table, because they can be recalculated when needed in a query, form or report.
However, if you feel you must …. On the OnExit event of the Cost or Quantity textbox (which ever is entered last), place code similar to this:

Private Sub Quantity_Exit(Cancel As Integer)
‘Calculates and displays Extended Cost

Dim vCost As Currency
Dim vQuantity As Double
Dim vcalcExtendedCost As Currency

vCost = [Forms]![yourFormName]![Cost]
vQuantity = [Forms]![yourFormName]![Quantity]
vcalcExtendedCost = vQuantity * vCost
[Forms]![yourFormName]![yourTotalQuotetextboxName] = vcalcExtendedCost
Requery
Refresh

End Sub

Reply | Quote

I recommend you do not store the calculated value in your table unless you have a very good reason. One of the rules set by one of the first three normal forms is not to include any value in a table that depends on the value in another field. If you have the cost of the object and the number of objects you can always recreate the total value in a query or report.

If you absolutely must store the total value in the table because of some business rule then add a control to the form and set its control source property to the total field in your table. Set this control’s visible property to no or false. In the before update event of the form set this control equal to the control with the calculated value.

Reply | Quote