I tried a question few days ago without any answer. I try to clarify my problem.
Context :
- MS-ACCESS application linked to the SQL SERVER database
- 2 linked tables : Orders and OrdersLines
- A trigger in SQL SERVER on OrdersLines to calculate turnover (quantity x unit price)
- In MS-access a form (frmOrders) with a subFrom linked with the idOrder field
The problem :
When I add a new record in frmOrders and fill in the Orders and OrdesLines fields => No problem
Then, if I modify any value of any field of the Orders table on the current record => Access error "Data has been modified. Another user has modified this record....". That's the trigger I think
The data re-entry worked after the error message in point 2
I add a me.requery command to the afterupdate event in the edited field in point 2, to avoid error message.
the me.requery command in point 4 unfortunately moves to another record in frmOrders and I have to close the form and reload it to reach the correct record.
I try RsClone = Me.recordset.clone and RsClone.findfirst commands without success (frmOrders is in addNew mode)
When I modify the record after reopening it, the Me.Requery does not cause the result of point 5. It reload the correct record which is editable with no error message.
My Question
How to avoid the record movement of point 5 ?