Unexpected write conflict error occurs when you close a form in Access

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Symptoms

When you edit data that uses multiple forms that have the same record source, you may receive the following error message:
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.
Then, you can click one of the following three buttons:
  • Save Record
  • Copy To Clipboard
  • Drop Changes

Cause

This behavior occurs when you open two forms that update the same data source at the same time. The first form puts an edit lock on the record or records, and then the second form changes the record or records and saves the changes. When the first form tries to close and write back to the table, the changes that were made by the second form are detected. This causes the error to be returned.

Resolution

To work around this behavior, use either of the two methods listed for a Microsoft Access database (.mdb) file or the method listed for an Access project (.adp) file.

For a Microsoft Access database (.mdb) file

Method 1

Set the RecordLocks property of the form to Edited Record. To do so, follow these steps:
  1. Open the form in Design View.

    Note In Access 2007, go to step 3.
  2. On the View menu, click Properties.
  3. On the Data tab, change the Record Locks property to Edited Record.

Method 2

Add code to the OnDeactivate event procedure of both forms to save the record. To do so, follow these steps:
  1. Open the form in Design View.

    Note In Access 2007, go to step 4.
  2. On the View menu, click Properties.
  3. On the Edit menu, click Select Form.
  4. On the Event tab, right-click in the OnDeactivate property box, and then click Build.
  5. In the Choose Builder box, click Code Builder, and then click OK.
  6. Type or paste the following code:
    DoCmd.RunCommand acCmdSaveRecord
  7. Open the second form in Design view and repeat steps 2 through 6.

For a Microsoft Access Project (.adp) file:

Add code to the OnDeactivate and OnActivate event procedures of both forms to save the record. To do so, follow these steps:
  1. Open the form in Design View.

    Note In Access 2007, go to step 4.
  2. On the View menu, click Properties.
  3. On the Edit menu, click Select Form.
  4. On the Event tab, right-click in the OnDeactivate property box, and then click Build.
  5. In the Choose Builder box, click Code Builder, and then click OK.
  6. Type or paste the following code:
    DoCmd.RunCommand acCmdSaveRecord
  7. On the File menu, click Close and return To Microsoft Access.
  8. On the Event tab, right-click in the OnActivate property box, and then click Build.
  9. In the Choose Builder box, click Code Builder, and then click OK.
  10. Type or paste the following code:

    Note The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run correctly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later). To do so, click
    References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

    Dim rs As ADODB.Recordset
    Set rs = Me.Recordset.Clone
    rs.Bookmark = Me.Bookmark
    DoCmd.RunCommand acCmdRefresh
    Me.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing
  11. Open the second form in Design view, and then repeat steps 2 through 10.

More Information

Steps to reproduce the behavior in Access 2003

  1. Open Access.
  2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
  3. In the Database window, click Tables under Objects, and then click the Employees table.
  4. On the Insert menu, click AutoForm.
  5. Save the form as frmNewEmployees.
  6. Open the Employees form, and then change the First Name field to Nancy1.
  7. Open the frmNewEmployees form, and then change the First Name field to Nancy2.
  8. Close the frmNewEmployees form.
  9. Close the Employees form.

    Note that you receive the "Write Conflict" message that is mentioned in the "Symptoms" section of this article.
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

304181 Unexpected Write Conflict Error When You Close a Form

280730 ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL Server Table

Propiedades

Id. de artículo: 304181 - Última revisión: 30 oct. 2008 - Revisión: 1

Comentarios