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

The way I have found to work around this is to reset the record source

Me.RecordSource = ""
cmd.Execute , , adCmdStoredProc
Me.RecordSource = "SELECT SourceFields FROM SomeTable "

but this seems hacky.

Answer 2

What happens when you execute the store procedure something like this:

db.Execute "<SP_NAME>", dbSeeChanges

Answer 3

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