Microsoft KB Archive/304181

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

Article ID: 304181

Article Last Modified on 4/11/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q304181



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.

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.
 * 1) On the View menu, click Properties.
 * 2) 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:  Open the form in Design View.

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

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: <ol> Open the form in Design View.

Note In Access 2007, go to step 4.</li> On the View menu, click Properties.</li> On the Edit menu, click Select Form.</li> On the Event tab, right-click in the OnDeactivate property box, and then click Build.</li> In the Choose Builder box, click Code Builder, and then click OK.</li>  Type or paste the following code: DoCmd.RunCommand acCmdSaveRecord </li> On the File menu, click Close and return To Microsoft Access.</li> On the Event tab, right-click in the OnActivate property box, and then click Build.</li> In the Choose Builder box, click Code Builder, and then click OK.</li>  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 </li> Open the second form in Design view, and then repeat steps 2 through 10.</li></ol>

<div class="moreinformation_section">

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 &quot;Write Conflict&quot; message that is mentioned in the &quot;Symptoms&quot; section of this article.

<div class="references_section">