# Access "Write Conflict" Error



## Chris_E (Feb 16, 2006)

Good day all,

I have a form that contains a number of Tabbed Pages.

Each page holds several controls and, as part of an "Edit Client Record" feature, I am able to move round parts of my form and modify fields where I need to.

When satisfied with my changes, I perform an "OK" click which, using code as on_click event, I am performing two SQL tasks.

One task updates a mastertable based on the fields in the form. The second updates other fields in a second table.

All goes well until I close the form whereupon I get the nsaty "Write Conflict" Message with three options, "Save Record", "Copy to Clipboard" and "Drop Changes".

Inspecting the result of the SQLs, I know the updates have taken place successfully, I don't actually need to save te from as it now stands. How can I stop this Write Conlift message arrearing?

Here's my code (with some of the waffle taken out)


```
Private Sub btn_RangeUpdate_Click()

On Error GoTo btn_RangeUpdate__Click_Err


    Dim SQLMasterUpdate As String
    Dim SQLKnown090 As String
    Dim LoopingPRNValue As Double
  

' PRNNumber.Value comes from the form and is not changed
' 
' Me.mstrRangeQty.Value comes from the form and is not changed and 
' creates an incremental loop to update several hundred records
' based on the settings for this one 'sample' record


For LoopingPRNValue = Me.PRNNUmber.Value To PRNNUmber.Value + Me.mstrRangeQty.Value

	DoCmd.SetWarnings False

' Here we simply update MasterTable using settings from certain fields in the form
' we do this to each record in the loop.

	SQLMasterUpdate = "UPDATE MasterTable SET " & _
	...
	bunch of SQL Statements that work fine		
	...
	DoCmd.RunSQL SQLMasterUpdate
    
' Here we then update Known090 table, again using settings from other fields in the form
' and we do this to each record in the loop.


	SQLKnown090 = "UPDATE Known090 SET " & _
	...
	bunch of SQL Statements that work fine		
	...
	DoCmd.RunSQL SQLKnown090

Next LoopingPRNValue

' Display a message in the form (using an unbound control) when the job is complete.

Me.txt_results_box.Text = "Successfully edited " & Me.mstrRangeQty & " numbers, starting from " & Me.PRNNUmber.Value

Goto End_Of_Update

btn_RangeUpdate_Click_Exit:

Exit Sub


btn_RangeUpdate__Click_Err:
    MsgBox Err.Description
    Resume btn_RangeUpdate_Click_Exit

End_Of_Update:

End Sub
```
I tried a "IF Me.Dirty=True THEN Me.Dirty=False" but that made no difference!

Any ideas please?

Chris


----------



## OBP (Mar 8, 2005)

Chris, is the Form bound to the same Table that the SQL is updating?
If so then that is where the conflicts may occur, the Form is "holding " the changes that you have made in it which the SQL has already updated.
It sounds like you do not need one of the updates.
If you are happier with the SQL then you could try using "Undo" in the form when closing or moving record along with Requery to get the latest changes from the Table.


----------



## cristobal03 (Aug 5, 2005)

For the record, the *Dirty* event doesn't fire on unbound forms, so if your form is unbound then that's why you wouldn't see any difference. But I agree with OBP; it sounds like your form is bound. If you're populating the form from a *RecordSource* but updating the records through VBA, that's a design problem. Unbound forms typically offer greater design control.

chris.


----------



## Chris_E (Feb 16, 2006)

Thanks to you both OBP and Cristobal.

Well, to be frank, I thought I was doing i the right way (though excuse me if I get the terminology wrong).

My form is based on a query, well, an SQL query to be accurate and the results of this query is what populates the form. Is this what you mean by *RecordSet*?

If the form shouldn't be populated this way (for the purposes of my task), then, forgive me for being a real thicko, but what method should I use to populate the form?

I'm already doing this on a form which updates a single record, but the way I am updating a batch of records, using one record as the 'model' is why I chose the SQL Update plan.

Where do you suggest I go now?


----------



## OBP (Mar 8, 2005)

You are populating the form correctly, it is the update that is causing the problem.
Access automatically updates the Recordset or Table when you make the changes.
So the SQL is not necessary.
Or you can use the SQL but do not use a "Bound" form.
In an unbound form you have to use VBA to put the data in to the Fields for display and then use VBA to put any changes in to the SQL for updating the table.
If you want to create or update a different tabel from the Form then you can use a Recordset and VBA to do it.


----------



## cristobal03 (Aug 5, 2005)

Yeah, sorry about that, I always get *RecordSet* and *RowSource* muddled. And yeah, that is what I meant. Since you obviously have a strong grasp of VBA and SQL, you might as well unbind your form and populate it through VBA. Typically you would use the form's *Open* event to initialize a recordset and the *Current* event to populate the controls with a particular record. But if you do it that way, you'll want to turn off the record selectors and navigation buttons and all that mess.

It's a usability issue with Access that isn't intuitive; if your data set is a query, you can get by as long as all your form controls have and explicitly defined control source (i.e., table and column name). But if you do it that way, you have to use built-in functions to update/save records--either by navigating using the record selectors, or using *File >> Save...*, or what have you. Since you're updating records in a custom manner, you're encountering problems.

Also, I think what I said about the *Dirty* event also applies to forms whose record source is variable, like a query (e.g., queries are themselves unbound; changing data in a query has no reflection on its query source). So again, changing the *Dirty* property won't have any effect. You can test it by tossing some code in the form's *Dirty* event:


```
Private Sub frmMyForm_Dirty()
  MsgBox "Somebody changed something in this here record!"
End Sub
```
chris.


----------



## Chris_E (Feb 16, 2006)

Ah! I'm starting to see some light.

Let me go away and noodle on this a bit.

Hope to get back in a day or so.

Cheers!

Chris


----------



## cristobal03 (Aug 5, 2005)

> *Originally posted by OBP:*
> 
> Access automatically updates the Recordset or Table when you make the changes.


I'd like to qualify this a bit further. The *Update* event doesn't occur when the form becomes dirty. It happens before a set of events, like the *Close* event, or when you move between records. So what you're seeing is this:


you load a record into the form
you make some changes
you update the record source custom-wise
you perform some action that calls the *Update* event (like moving to a new record or closing the form)
_then_ Access tries to update the record
somehow Access throws an exception to writing to the same record (most likely the record is locked somehow)
you see a warning.
Is the source table clearly defined in the form controls? If not, the form might think you're trying to write data to the query, and that's definitely a write conflict.

chris.


----------



## Chris_E (Feb 16, 2006)

Hahaha!



cristobal03 said:


> Since you obviously have a strong grasp of VBA and SQL...


That's the funniest thing I have seen all day! I prefer to think I have "a grasp", yet I honestly find I'm grappling now and again! But I appreciate the compliement Chris!

I just did a quick "workround". I planted a DoCmd ... blah blah acSaveRecord right at the top of the sub.

Now I get an error "The data has been changed", but if I'm cute, I can change that error message to "Yuor data has been updated".

Cheeky but a way round it for today!!

Anon my friends.

Chris


----------



## cristobal03 (Aug 5, 2005)

Also, for anybody who's interested, Microsoft has an example database (ACC2000) to demonstrate event order. There's a link to it, along with instructions for finding event order articles in the help docs, in the following KB article:

http://support.microsoft.com/kb/208781​I thought I'd toss that in here because I'm pretty sure it pertains to this problem and I've forgotten a lot about the minutia of form event order.

HTH

chris.


----------

