storing results from a caculated field in Access

  • Thread starter Thread starter mlittler
  • Start date Start date
M

mlittler

Hi,

Does anyone know how to get Access to store the results from a
calculated expression, and then stop it from being updated when it is
viewed again?

I am trying to set up a multiple currency system, and I do not want
the transactions to be recalculated every time someone view the record
in a form.

For instance, I have a form with product information, the price paid for
the product and the currency purchased in.

Then I have a sub-form with basic transaction information, but also the
price and currency it was sold in...

Date Employee Product Units Currency Sell Price Actual
Sell Price
Product1 1 Sterling 10.00
16.00

I have set it up so that it converts the Sell Price into the "Actual
Sell Price" based on the currency the product was bought in. So in this
instance, the product was actually bought in Dollars and then sold in
Sterling with a conversion rate of 1.6

The trouble is that I cannot get the "Actual Sell Price" field (which is
a bound field to a table) to update the table data with the calculated
control that is in it.

In addition, if/when I do get this working, I do not want this
historical transaction data to be recalculted everytime somebody
views the record, as of course the exchange rate data will be changed
every day.
 
I have set it up so that it converts the Sell Price into the "Actual
Sell Price" based on the currency the product was bought in. So in this
instance, the product was actually bought in Dollars and then sold in
Sterling with a conversion rate of 1.6
Click to expand...

You don't say how you have set it up, so I am guessing it's something like

Private Sub Form_Current()
Me!txtActualPrice = CCurr(Me!txtSellPrice) * GetConvRate()

End Sub

but you probably only really want to do this when the field is previously
empty:

Private Sub Form_Current()
If IsNull(Me!ActualPrice) Then ' Note we are using the field,
' not the control...
Me!txtActualPrice = _
CCurr(Me!txtSellPrice) * GetConvRate()


End If

End Sub


As long as the field in the table is not being messed around with, it will
stay the same.

Hope that helps


Tim F
 
Back
Top