By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,401 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,401 IT Pros & Developers. It's quick & easy.

Calculating a table value from values in another table, based on other fields.

P: 17
Currently I have a table, and would like the calculate a field in the table by referencing values from another 3 tables.

i.e. tblData
Expand|Select|Wrap|Line Numbers
  1. Index1, Index2, Index3, Value
  2. 1             2          3
  3. 2             2          2  
  4. 1             3          4
and tblIndex1
Expand|Select|Wrap|Line Numbers
  1. Value    Data
  2. 1            2.5
  3. 2             2
  4. 3           1.5
and tblIndex2
Expand|Select|Wrap|Line Numbers
  1. Value    Data
  2. 1            2.5
  3. 2             2
  4. 3           1.5
etc

so if tblData, field Index1 = 1, then take the Value field from tblIndex1 which is the same as tblData.Index1 and do the same for Index2 and Index 3 and then tblData.Value is a calculation based on those indexes.

i.e. Value = 2.5 * 2 * Index3.Value.

I want to perform this calculation for every ID in tblData.

I've written the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdate_Click()
  2.  Dim rcdTesting As DAO.Recordset
  3.  Dim rcdReference As DAO.Recordset
  4.  Dim rcdReference2 As DAO.Recordset
  5.  Dim rcdReference3 As DAO.Recordset
  6.  Dim I As Variant
  7.      Dim Constant As Integer
  8.      Dim Constant2 As Integer
  9.      Dim Constant3 As Integer
  10.  
  11.  
  12.    Set rcdTesting = CurrentDb.OpenRecordset("tblTest2")
  13.    Set rcdReference = CurrentDb.OpenRecordset("tblReference")
  14.    Set rcdReference2 = CurrentDb.OpenRecordset("tblReference2")
  15.    Set rcdReference3 = CurrentDb.OpenRecordset("tblReference3")
  16.  
  17.  With rcdTesting
  18.    For Each I In rcdTesting
  19.    If ![Outing] = rcdReference![Value] Then
  20.    Constant = rcdReference![Result]
  21.    End If
  22.    If ![Lap] = rcdReference2![Value] Then
  23.    Constant2 = rcdReference2![Result]
  24.    End If
  25.    If ![Time] = rcdReference3![Value] Then
  26.    Constant3 = rcdReference3![Result]
  27.    End If
  28.    ![Value] = Constant * Constant2 * Constant3
  29.    Next I
  30.  End With
  31.  
  32. End Sub
But it says I am declaring the I, in the for each statement incorrectly.

Currently the code is written so it updates on button press, but ideally I would like the field up update automatically as each Index value is entered.

Any help would be greatly appreciated.

Thanks,

Zoe
Aug 20 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,661
Zoe, there are currently too many typos to read your problem without getting confused.

Please take the time to prepare a question that is legible if you would like our experts to take their time to try and help.

-Administrator.
Aug 20 '08 #2

Post your reply

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