Write Conflict

[irishjoe]

Hi,

This problem has been bugging me for a long time now.
I have some toggle buttons on a form which are bound to records in a table. When I select some buttons then click to another tab that uses the same table I get the Write conflict error.

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

I have found that if I click on anything before clicking on the other tab, it works fine.

So, a possible solution could be as follows, set focus to a button and send a "space" or "return" to make the program run the buttons code. But I cannot get this to work...

I also tried adding a pause to let the program catch up with itself. I thought if I let the program pause for one second, it would save the changes and give access to the second tab. Yet again, no joy.

Please please please, if anyone has any advice to get me out of this prediciment, let me know.

Cheers.

 

[PHV]

You may try this:
DoCmd.RunCommand acCmdSaveRecord

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[irishjoe]

Yep, tried that too. no joy.

Any other ideas?

 

[Pikwa]

I, too, have had this problem. It is consistent, and less complex the IrishJoe's. Here it is. I have a form with a subform. The subform gets its data from 2 tables. TableX is the main table with groups and members of those groups. The other table is an Action table related to TableX and shows updates to be done to the member of a group. Updates are only recorded in the Action table and are executed, later. Everything works fine except when I modify a member's address. Since the member can be in many groups, I try to show that future action for every instance by updating TableX with a doCmd.RunSQL, so that if the user moves to a different group having that member, they will see the future address change. I then get the Write Conflict error. The biggest problem with it seems to be that if I update a member that is in many groups, one or two of the records gets corrupted. This was so bad that, with an index, Access would not even let me delete the corrupted record. I had to kill the index.
I think a potential solution is to not use the doCmd.RunSQL to update all the records. Instead, open the record set using a sql statement and update it with a coded loop. Not sure, but that is what I'm going to try on Monday. I'll let you know.

Chris

 

[hecash]

I have a similar issue:

Platform: MS Access 2000 Project w/SQL Server 2000 back-end.

Form: Recordsource = Personnel table. Two fields on the table are represented by combo-box controls that select the ID and description from a "Field Office" table in one instance and the ID and description of a "Team" table in the second instance.

Form State: Edit mode

Changing the Field Office combo-box and saving the record is successful.

Changing the Team combo-box and saving the record results in the "This record has been changed" error.

It's beginning to look like this may be a fairly common problem.

 

[hecash]

I just found this at another site. It did not work for me but it may for you.

**********
I was just dealing w/ this problem. The issue is that Access pulls information from the MySQL server and then saves it before it inserts your changes. Before it inserts your changes it compares what it had initially against what is currently in the MySQL server. There are some problems w/ this comparrison having to do with floating point/double and dates that results in a failed comparison. So Access thinks someone else has changed the data.

The solution was rather simple, however. To you table in MySQL add a column of type "TIMESTAMP" and I think even name it "timestamp". This is the same type of field Access uses to interact with SQLServer so it knows what to do with it. Instead of comparing all this data back and forth it just checks this timestamp entry and ignores the rest.

 

[Pikwa]

hecash,
Yes, I saw that. I'm not dealing with mySQL, so it is not the answer. I think the stored procedure is the answer, but I have to wait until tomorrow to try it out. If nothing else, close the current form, update the table(s), then reopen the form.

Chris

 

[Pikwa]

I discovered my problem.
I was in a subform record where the field value could repeat for many parent records. When I changed the field I did an after update sql to update the entire table, including the record on the form.
The form was trying to write the updated record and so was the sql, and I got a Write Conflict. I avoided this by having the sql filter out the current record. Fairly obvious once I had pulled all my hair out.

 

[zionman4]

Pikwa,
I am having a similar problem. Could you show me your code to see how you did that? Thanks!

 

[Pikwa]

IrishJoe:
It seems like you are tabbing to a different view of the same record, and changing it as you leave one tab to the next so the next form takes the record for writing as the first one tries to write it.

Have you tried a SaveRecord? Maybe leave the record with a prev/next record then go back to it as you tab?

 

[Pikwa]

zionman4:

Not so easy to post because the code is on an isolated network at work. But, here is the deal:

- I have a table with distribution lists and members, with one person a possible member of many DLs. joesmith@comcast.net might be in 30 DLs.
- I'm viewing one DL with Joe Smith in it and change the address because he called and changed ISPs. So, I want to show that change on all the other DLs if the user happens to page through the DLs. Don't want the user thinking they have to change the address again and again.
- I set the event "afterUpdate" and do SQL: currentdb.execute "Update tblDL Set address = '" & Me!Address & "' Where name='Joe Smith'"

Address, of course, being the control name for the field on the form from the table, tblDL

- Oopssss..this causes a write conflict.

- So, I add to the WHERE: " AND dl_name <> '" & Me!dl_name "'"
- The record I was just on is already done and so are all the others.

Like I said, simple, but I had to pull some hair out, first.

 

[zionman4]

I'll give a try, because I'm running out of hair myself. Thanks for the input!

 

[irishjoe]

Pikwa: Yeah, I have tried the doCmd.save record thing. The problem still persists.
I don't really want the navigation bar at the bottom because it looks crap in a professional program and I use a treeview for navigation.
What does access do in the background when you click a button? I don't mean the code in the background. Even if I create an empty button, make changes to the form, click the button and then move tab, it works. But if you do the same without the button click, it throws up the error.