In Access I want to calculate a value and not have it vary after

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

RetailPrice =Cost*Markup. This expression is in a Form. Later I change the
value of Markup in its appropriate Table. How do I ensure that the value of
RetailPrice in its original Record remains constant and does not vary as
Markup varies. But once I change the value of Markup, I want all SUBSEQUENT
records to reflect that change. Thanks for any help.
 
This is a case where you need to store a calculated value, RetailPrice. Once
RetailPrice is calculated and saved in its original Record it will remain
constant as Markup varies. Unless you recalculate the original record when
Cost or Markup has a different value than than original.
 
RetailPrice =Cost*Markup. This expression is in a Form. Later I change the
value of Markup in its appropriate Table. How do I ensure that the value of
RetailPrice in its original Record remains constant and does not vary as
Markup varies. But once I change the value of Markup, I want all SUBSEQUENT
records to reflect that change. Thanks for any help.
Click to expand...

Steve's correct; you do indeed want to store the value.

One way to do so is to have a TWO textboxes on the form - one visible,
named txtCalcRetail, with a control source

=Cost * Markup

and a second one, with its Visible property False, named txtRetail,
bound to the RetailPrice field in your table. In the Form's
BeforeUpdate event push the one into the other:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<validation checking here, e.g. make sure that neither Cost nor Markup
is NULL>
If <everything is ok> Then
Me!txtRetail = Me!txtCalcRetail
Else
MsgBox "Please fill in blah blah blah", vbOKOnly
Cancel = True
End If
End Sub

John W. Vinson[MVP]
 
Back
Top