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

[zoeb]

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

Index1, Index2, Index3, Value
1             2          3
2             2          2  
1             3          4

and tblIndex1

Value    Data
1            2.5
2             2
3           1.5

and tblIndex2

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:

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

[NeoPa]

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.