suppress write conflict message in Access VBA

Question

My problem.

I have written a stored procedure to calculate a number of fields that are being presented through a form on an Ms Access front end (to a MSSQL2000 db).

as soon as I call Me.Form.Requery to get the changed values. I can the irritating message that I have caused a write conflict.

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changed to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

I know that its me that has changed the data so I want to suppress the message or prevent the message from occurring.

Answer 1

(I guess I should put my comments in a post, since I'm actually answering the question)

The reason you're getting the write conflict message is because you've created a write conflict by editing the record via the form and via the stored procedure. To avoid the error, simply save the record in the form before executing the stored procedure. From your code snippet posted above, it should be:

Me.Dirty = False
cmd.Execute , , adCmdStoredProc

This is a common Access error, and it's caused by trying to edit the data through a bound form and through direct SQL updates. You need to save the edit buffer of the form before updating the same record via SQL.

In other words, you should be grateful that the error message is happening, because otherwise, you'd lose one or the other of the changes.

I question the design, though. If you've got the record open in a bound form, then make the edits to the data loaded in the form, rather than running SQL to update it. I'm not sure why you need a stored procedure to make changes to a record you've already edited in a form -- it sounds like a design error to me (even if the solution is quite simple).

Answer 2

I have an access form linked to a back end SQL database. A trigger on a table linked to a subform updates a field in the form. If I edit the same record in the form after I have changed values in the subform I get the error message "Write Conflict This record has been changed....".

The field that was updated by the trigger can't be changed on the form directly so it is always ok to select 'Save Record' but I couldn't figure out how to select 'Save Record' automatically.

As others have said the way to avoid the error is to requery the form before editing the record but that makes the user experience less smooth.

I've finally found a workaround:

In the form's class module enter:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 7787 Then
Response = acDataErrContinue
Me.Recordset.MovePrevious
Me.Recordset.MoveNext
End If

End Sub

I've spent a long time looking for a solution - I hope it helps someone else