446,401 Members | 928 Online 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 Index1, Index2, Index3, Value 1             2          3 2             2          2   1             3          4 and tblIndex1 Expand|Select|Wrap|Line Numbers Value    Data 1            2.5 2             2 3           1.5 and tblIndex2 Expand|Select|Wrap|Line Numbers Value    Data 1            2.5 2             2 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 Private Sub cmdUpdate_Click()  Dim rcdTesting As DAO.Recordset  Dim rcdReference As DAO.Recordset  Dim rcdReference2 As DAO.Recordset  Dim rcdReference3 As DAO.Recordset  Dim I As Variant      Dim Constant As Integer      Dim Constant2 As Integer      Dim Constant3 As Integer        Set rcdTesting = CurrentDb.OpenRecordset("tblTest2")    Set rcdReference = CurrentDb.OpenRecordset("tblReference")    Set rcdReference2 = CurrentDb.OpenRecordset("tblReference2")    Set rcdReference3 = CurrentDb.OpenRecordset("tblReference3")    With rcdTesting    For Each I In rcdTesting    If ![Outing] = rcdReference![Value] Then    Constant = rcdReference![Result]    End If    If ![Lap] = rcdReference2![Value] Then    Constant2 = rcdReference2![Result]    End If    If ![Time] = rcdReference3![Value] Then    Constant3 = rcdReference3![Result]    End If    ![Value] = Constant * Constant2 * Constant3    Next I  End With   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 