Calculated field updated correctly with VBA after 2nd attempt

[MarcinM]

Hello,

I have the following calculated field:
=[quantity]*[pricePerPiece]*[currency].[column](1)

“Currency” is a comobox based on the following query:
SELECT DISTINCT tblCurrencyNames.CurrencyName, tblExchageRates.ExchangeRate
FROM tblCurrencyNames LEFT JOIN tblExchageRates ON tblCurrencyNames.CurrencyName = tblExchageRates.CurrencyName
WHERE (((tblExchageRates.OrderID)=[form]![OrderID]));

I have the following VBA statement in AfterUpdate event:
Forms!frmOrders!sfmorders.Form!CurrencyName.Requer y

Question:
Why is the calculated field updated with the correct value after entering value (in ExchangeRate field) 2nd time? After 1st attempt the calculated field is updated with a value which does not correspond to the formula.


Thank you for hints.

MS Access 2007

Marcin

[twinnyfo]

Please use code tags when posting code or queries.

For this line:

WHERE (((tblExchageRates.OrderID)=[form]![OrderID]));

I'm not sure how you will ever generate correct values in your combo box, as tblExchageRates.OrderID obviously refers to a PK and [form]![OrderID] seems to be pointing to a form.

It should be something more like this:

"WHERE (((tblExchageRates.OrderID)= " & Me.[OrderID] & "));"

(notice the quotes)

I don't know if that has anything to do with your direct problem. I can only assume that Forms!frmOrders!sfmorders.Form!CurrencyName is the calculated field?

The information seems a bit incomplete. Perhaps adding

Me.Refresh

before you find the value?

[jforbes]

Maybe call a .Recalc on your calculated field? Usually Access is good at determining when a field needs recalculated, but sometimes you need to help it out a little.

It looks like you are trying to create an Order with a CurrencyType option. In the past, I've stored the CurrencyType on the Order, then used a Query on the Order Lines table joined back to the Order (then joined to a CurrencyConversion table) to perform the Totals Calculations that take into account the Currency conversion. So you might get around this by moving your calculation into a Query instead of it all being done on your Forms. The advantages of this are that you can use the same query for Forms and Reports as well as there being only one place to modify (or troubleshoot) the calculation.

[MarcinM]

@jforbes
Yes, using a calculated field in a query is an interesting approach. However, my form is based on 3 tables (1 table on 1-side one, 2 tables on many-side) and when I add all the required fields the all the tables the query (and form) becomes read only. Is there a work around to make it editable?

[jforbes]

There are a couple easy ways to address this:

• For inline values, use Unbound fields for the calculations then in the Forms OnCurrent Event use a Dlookup to set the value from the Query.
• For totals, Create a SubForm based on the Query