Dear all,
I have a Table called Records with the following four columns:
ID StartChainage EndChainage DistanceTraveled
The DistanceTraveled is the difference between EndChainage and StartChainage.
For each new record, the StartChainage should be equal to the EndChainage of the previous record. In order to input data to this Table, I have created a Form called Record1 where I can only add (by typing) values in the field called EndChainage, while in the field StartChainage I use the following expression:
=IIf([ID]=1,0,DLookUp("[EndChainage]","Record","[ID]=Forms![Record1]![ID]-1"))
By this expression, I actually say that for the first record in the Table Records (i.e. ID=1) the value in the StartChainage must be 0, else it should obtain the value of the EndChainage field of the previous record. This works fine and I have a Form with fields where I only input the value of the EndChainage and the Form sets automatically the value of the StartChainage for the next record and it also calculates the DistanceTraveled.
The problem is that the calculated fields are NOT updating the relevant fields of the Table. In the Table the only updated fields are the EndChainage ones, i.e. the ones I only type manually the values!
How can I make the Table to get automatically updated by the calculated fields of the Form?
Thank you and best regards,
orcobal
PS. Maybe I could use calculated fields in the Table itself, but this is not what I really want.