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.

Felipe

Do you really want to change any data in your form? – Earnestineearnings

yes I do. I've 2 applications that 'do' the same thing and I want to reuse the sproc from the other. When the sproc fires it updates the data on that form. – Felipe

So the sproc updates the data and not any data entry in the form itself? – Earnestineearnings

not at that stage. Data is entered, then the sproc does something with it, updating some of the fields on the form. – Felipe

Save the record in the form before running the stored procedure. – Interpellant

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

Interpellant

To justify the design. I have 2 systems Access and .Net doing the same thing (running a calculation on the fields in the db). Access used to do the calculation with VBA, the .Net used the stored procedure. I have to make a change and want to apply DRY so the sproc is the best place for it. – Felipe

Great answer! I was triggering a recordset update on an AfterUpdate event. This resulted in the VBA code trying to update the same record as the record in the user bound form. adding "Me.Dirty = False" to the VBA committed the form data allowing the VBA to do its thing without issues. Thanks! – Inverter

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

Oxpecker

Thanks! For my solution I just changed the MovePrevious/MoveNext to a msgbox informing the user that you need to try their changes again. This makes it a much better solution. – Cramfull

Ten years on from your solution, I implemented it into an Access app I built for my company. This worked well! – Consultation

Answer 3

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.

Winola

Answer 4

If none of these answers worked, then try this. In the forms that you're having the error in, plug in this code.

Private Sub Form_Deactivate()
     DoCmd.RunCommand acCmdSaveRecord
End Sub

Basically, the error is two forms editing the same information. So you have to tell Access to save after you leave either of the forms.

Source: https://support.microsoft.com/en-ca/kb/304181

Stairhead

Answer 5

I had the same problem. I changed fields by code from slave form (slave table) in the master form (master table) and I have always got the warning before closing of the form:"record has been changed by another user..."
Wrong status:
The wrong syntax in the slave form (action after field update) was:

Private Sub Value1_AfterUpdate()
    Me.Parent.Controls("Result").Value = 123.52 
End Sub

The Parent Form then after detected, than another "user" changed its field.

Solution:
Force master form to change values from itself. Use the master form as class:
1) Define private variables for values, which shall be changed from slave form
2) Define Public let and get properties in the master form - i.e "Public Property Let letResult(ByVal myVal as double) ..."
3) Define public procedure Write_Values in the master form which sets values in fields in master form from the private values (value got from the Public property let ...)

4) Define syntax in the slave form which sends values to let property in the master form.
5) In the end, call the public procedure Write_Values in the master form from the slave form

example code master form:

Private myResult As Double
Public Property Let letResult(ByVal myval As Double)
    myResult = myval
End Property
Public Sub Write_Values()
    Me.Result.Value = myResult
    DoCmd.RunCommand acCmdSaveRecord
    Me.Dirty = False
End Sub

---

example code slave form:

Private Sub Value1_AfterUpdate()
    Me.Parent.letResult = 123.52
    Me.Parent.Write_Values
End Sub

Illustrious

This was a neat solution to my problem which was to update a form from a sub-form. – Conjunction

Answer 6

I had the same problem, with two controls in two different forms linked to the same field in an backend linked table. I resolved it by having only one control field linked to the table and when either one is update the other writes the information in the second other control.

It works. You may ask why do I need two control fields in two forms? One registers the data and the others uses it to do calculations after all data is entered. And this to allow users no access to the form that does the calculations. Of course I could have accessed the data for calculations from the form registering the data with a line of VBA of code for reference in the calculation form. I hope it helps. It did for me.

Renwick

Answer 7

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.

Felipe

The reason that works is becore it saves the edits to the bound form when you change the recordsource. If you just did Me.Dirty = False before calling the stored procedure, you'd get the same result. – Interpellant

Answer 8

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

db.Execute "<SP_NAME>", dbSeeChanges

Isisiskenderun

Answer 9

Check to see if you have a trigger on the table that you're trying to update. If so, make sure that it doesn't return anything in the text window when you execute it.

I had this same issue and couldn't resolve it UNTIL I added a "SET NOCOUNT ON" at the beginning of the trigger.

Nothing was being updated externally by the trigger as it was an "AFTER INSERT,DELETE" trigger, but my problem has been solved.

Ackler