473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Pass a value to calculated field from different table

15
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.
Nov 21 '14 #1

✓ answered by 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))

Expand|Select|Wrap|Line Numbers
  1. Private Sub PoleKombi_AfterUpdate() 
  2.  me.exchangeRateID = Me.comboBox.Column(1) 
  3.  me.currencyName=  Me.comboBox.Column(2) 
  4.  me.exchangeRate = me.comboBox.Column(3) 
  5.  me.value = Quantity*Price *exchangeRate)
  6. end sub

5 1324
jimatqsi
1,271 Expert 1GB
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
Nov 22 '14 #2
twinnyfo
3,653 Expert Mod 2GB
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?).
Nov 24 '14 #3
MarcinM
15
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).
Nov 25 '14 #4
twinnyfo
3,653 Expert Mod 2GB
When someone chooses a currency type, simply filter the exchange rates subform based on that value.
Nov 25 '14 #5
MarcinM
15
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))

Expand|Select|Wrap|Line Numbers
  1. Private Sub PoleKombi_AfterUpdate() 
  2.  me.exchangeRateID = Me.comboBox.Column(1) 
  3.  me.currencyName=  Me.comboBox.Column(2) 
  4.  me.exchangeRate = me.comboBox.Column(3) 
  5.  me.value = Quantity*Price *exchangeRate)
  6. end sub
Nov 30 '14 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: Allen Browne | last post by:
Subform is based on a single-table query that contains a calculated field: Amount: Round(CCur(Nz(*,0)),2) Continuous subform displays this field in a text box named Amount. As user enters new...
2
by: Manfred | last post by:
Hi Would like to add a new Field to Table which is 75 % (or another Percentage) higher than the Value in another Field in same Table.Is this possible with Expression or would I have to enter the...
2
by: MLH | last post by:
I feel pretty lucky on my last SQL question. I think I'll try one more... If there is a table (tblCorrespondence) with a field in it named and I am extracting a dynaset of records having an ...
1
by: sara | last post by:
I am learning how to use simple functions to make my apps more powerful and efficient. On one screen, I want to populate field B: ItemDescription by looking up the ItemDescription in the Items...
3
by: Marina | last post by:
I am using Access 2002. I have a client transactions table with a field for HoursBilled, BillingRate, and AmountBilled. Amount Billed is a caluclation of HoursBilled * BillingRate. I can get...
3
by: kelley.l.turner | last post by:
Hi all, I am very new to MS Access so please bear with me! I have created a simple calculated field in my data entry form, yet when I view my data table or try to generate a report based on...
5
by: Henrik | last post by:
The problem is (using MS Access 2003) I am unable to retrieve long strings (255 chars) from calculated fields through a recordset. The data takes the trip in three phases: 1. A custom public...
9
by: Haas C | last post by:
Hi all! Is there anyway I can override a value in a calculated field on a form? For example: I have a form which displays the following fields based on a query: Premium Due field has the...
3
by: Ciara9 | last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to...
3
by: fperri | last post by:
Hello, I have a calculated field in my query that uses a function to come up with the value. The function has various fields from the table used in the query passed into it as parameters. For some...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.