i.e. tblData
Expand|Select|Wrap|Line Numbers
- Index1, Index2, Index3, Value
- 1 2 3
- 2 2 2
- 1 3 4
Expand|Select|Wrap|Line Numbers
- Value Data
- 1 2.5
- 2 2
- 3 1.5
Expand|Select|Wrap|Line Numbers
- Value Data
- 1 2.5
- 2 2
- 3 1.5
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
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