suppress write conflict message in Access VBA

(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).

I had a similar problem.

Example:

Let's say the record has a field aForm!text = "Hello".

If the user clicks a button, VBA code is executed where

aForm!text = "Hello World!"

When I close the record, I get the error message "This record has been changed ..."

Solution:

If you refresh the form by aForm.Refresh and aForm.Requery you can avoid the write conflict.

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