Pass a value to calculated field from different table

[MarcinM]

Hello,
There are 3 tables with fields as shown below. All the tables are on the same form.
Side One: tblOrders
Side Many: tblOrderDetails, tblExchangeRates

I would like to make a calculated field “Value” in tblOrderDetails.
How can I pass to this calculated field the value of exchange rate field from tblExchangeRates?

Thank you for any hints.

tblOrders (one side)
orderID (PK)
(other fields)

tblOrderDetails (many side)
orderElementID (PK)
orderID (FK)
elementName
Quantity
Price
CurrencyName
Value (=Quantity*Price*exchangeRate)
(other fields)

tblExchangeRates (many side)
exchangeRateID (PK)
orderID (FK)
currencyName
exchangeRateValue

PK – primary key
FK – foreign key


Marcin.

[jimatqsi]

MarcinM,
Welcome to Bytes.com. Always good to see new posters.

Why is OrderID in the tblExchangeRates? One could assume that the exchange rate does not vary by order but by currency being used by the customer. Once you know the customer, you know the currency, and you can use Dlookup function to pull the exchange rate from the the exchange rate table (orderID not being necessary for the lookup).

Does this help?

jim

[twinnyfo]

MarcinM,

I also am unclear as to how all three tables are on one form? If anything, these should be subforms of the main form (depending on which is your master table--Orders?).

[MarcinM]

Thank you for your answers.
A few words of explanation

<< Why is OrderID in the tblExchangeRates?
<< One could assume that the exchange rate does not vary
<< by order but by currency being used by the customer.
There may be MANY currency names in ONE order, that is why OrderID is in tblExchangeRate (side many of the relation).

<< I also am unclear as to how all three tables are on one << <<< form?
Master table - tblOrders (main form)
Both tblExchangeRates and tblOrderDetails are on the subform.

The idea is that when a user chooses a curreny name (in tblOrderDetails on the subform)an appropriate exchage rate will be drawn from tblExchangeRate (based on OrderID, currencyName).

[twinnyfo]

When someone chooses a currency type, simply filter the exchange rates subform based on that value.

[MarcinM]

Thank you for your posts.

The following solution seems to be working
I assigned "currencyName" to a query and refer to necessary columns using VBA (columnt(x))

Private Sub PoleKombi_AfterUpdate() 
 me.exchangeRateID = Me.comboBox.Column(1) 
 me.currencyName=  Me.comboBox.Column(2) 
 me.exchangeRate = me.comboBox.Column(3) 
 me.value = Quantity*Price *exchangeRate)
end sub