Write Conflict?

[willkr]

I'm getting a peculiar error on on of my forms. When I update the amount indicated on the user form, after I update the database I get the following error

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

The problem is that I am the only user.

Here's another strange thing: If I put a breakpoint on the close form command (immediately after the subroutine call to update the database), the error doesn't show up ..... until .....I single step the form close command.



Any ideas? Any?

[June7]

If you initiate edit of a record via form and then use code to edit that same record such as with an SQL UPDATE action, that is a conflict and triggers error. So somehow the same record is getting edited twice at same time. Would have to review code.

[davegri]

As June7 states, a form changing its data values by both altering bound controls and by SQL Update or Insert can cause this problem. Setting .Dirty to false before the SQL update can sometimes help.

Private Sub cmdPricing_Click()
    Dim sSQL As String
    If Me.Dirty Then Me.Dirty = False
    If DCount("InventoryID", "tblPricing", "InventoryID=" & InventoryID) = 0 Then
        sSQL = "Insert into tblPricing (InventoryID) VALUES (" & [InventoryID] & ");"
        CurrentDb.Execute sSQL, dbFailOnError
    End If
    If Not IsNull(InventoryID) Then
        DoCmd.OpenForm "frmPricing", , , , , , InventoryID
    End If
End Sub

[willkr]

Well, I finally figured it out.

I just spent the last hour writing a detailed description of what the program did. As I was writing it I noticed that the form the user makes the corrections on was populated with a SELECT statement, but I was then filling those fields based on a database read. When the user clicks a button to update the record, I did a database write not realizing that all the fields were tied to the database because of the SELECT statement. I removed the SELECT statement from the properties of the form and it all works fine now!

Thanks to both of you for your help.

willkr

BTW, I gave you both Reputation points

[willkr]

As an addendum, I learned something new. When you open a form with a SELECT statement (which fills-in the form since the fields on the form are tied to the record via the Control Source), the data in the form is re-written into the database when the form is closed. That's what caused the Write Conflict.

I guess the reason that I used the button to update the database record is so the user can make a change in the data on the form but then decide not to update the record after all. Is there a "Best Practices" on this?

[June7]

This is not a matter of 'best practice' it is a matter of preference. If you are happy with UNBOUND form process, stay with it. Many experienced developers prefer it.

For BOUND form, could use form BeforeUpdate event to validate. Prompt user if they really want to save and if no, cancel. Or have Save and Cancel buttons on form.

Whatever approach, make sure user can't deliberately or accidentally bypass. I disable form X close, right click shortcuts, and ribbon.