Full Version: Write conflict error!
UtterAccess Forums > Microsoft® Access > Access Forms
golsen
Hi everyone,
I have a problem that I can't figure out.
Basically, once I update a textbox that holds a field value for FolderName (in the after update event), I want to rename the folder name in multiple tables. That part is working.
However, afterwards when I am trying to refresh the form, a message box pops up saying:
"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 ..."
THere is my code:
*************************************************************
Dim oldFolderName
Dim newFolderName
oldFolderName = Me.cl_Folder_Name_.OldValue
newFolderName = Me.cl_Folder_Name_

'code that will update the folder names in all tables
DoCmd.Save
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [Depo Summary] SET [Depo Summary].[ds_Folder Name:] = '" & newFolderName & "' WHERE [Depo Summary].[ds_Folder Name:] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [ScanLocation] SET [ScanLocation].[sc_FolderName] = '" & newFolderName & "' WHERE [ScanLocation].[sc_FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [Case Revisions] SET [Case Revisions].[r_FolderName] = '" & newFolderName & "' WHERE [Case Revisions].[r_FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [Outsider/Case] SET [Outsider/Case].[oc_FolderName] = '" & newFolderName & "' WHERE [Outsider/Case].[oc_FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [Finances] SET [Finances].[mn_FolderName] = '" & newFolderName & "' WHERE [Finances].[mn_FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [LateCharges] SET [LateCharges].[l_FolderName] = '" & newFolderName & "' WHERE [LateCharges].[l_FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [Billable Items Received] SET [Billable Items Received].[bi_FolderName] = '" & newFolderName & "' WHERE [Billable Items Received].[bi_FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [After Follow up Letter] SET [After Follow up Letter].[cl_Folder Name:] = '" & newFolderName & "' WHERE [After Follow up Letter].[cl_Folder Name:] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [TempFollowUp] SET [TempFollowUp].[FolderName] = '" & newFolderName & "' WHERE [TempFollowUp].[FolderName] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [Case List] SET [Case List].[cl_Folder Name:] = '" & newFolderName & "' WHERE [Case List].[cl_Folder Name:] = '" & oldFolderName & "';"
DoCmd.RunSQL "UPDATE [TestifiedDates] SET [TestifiedDates].[tt_FolderName] = '" & newFolderName & "' WHERE [TestifiedDates].[tt_FolderName] = '" & oldFolderName & "';"
DoCmd.Save
Me.Refresh
DoCmd.SetWarnings True
********************************************************************************
*
It's when it get to this me.refresh code that the error occurs.
Can anyone help me out with this problem?
Geir
Loop
maybe try me.requery
erwardell
do not use me.refresh or me.requery. You have already made the change in the textbox and you have a bunchof update querys so there would be no need to do a refresh or a requery. I would comment it out and test to see if that helped. Also you really to not need the docmd.save commands at the beginning and end of the action queryies.
Loop
but without a requery, doesnt that mean that the bound textbox on the form will not display the changes made by the docmd's until the database refresh interval occurs, allowing the user to overwrite the change with the original value?
erwardell
But according to your first post, if I am reading it correctly you already updated the textbox. So it should already reflect the change unless there is something more that I do not know about.
Loop
oops, your right. I really got to stop skim reading....
erwardell
Not a problem I tend to do the skim reading as well.
golsen
Hi,
Sorry I haven't been able to reply before, however I am still having the same problem.
I've tried w/o the refresh, and then I do not get the error message, however, then I get the same error message once I close the form.
I do not know if it has something to do with the fact that I am actually changing the value of the Primary Key. So, the folder name is the primary key. I know, I know... the PK should be unique, have no meaning, and never be changed, and believe me... I totally agree. However, I am doing this for a client of mine, and they absolutely want to be able to change the FolderName (PK). In addition to the code I wrote earlier, I have also created some code that makes sure that the same PK doesn't exists, etc.
However, how can I get rid of this error message since the code is actually working (it is updating the tables), however that error keeps popping up once I close the form.
Any help would be appreciated!
Geir
erwardell
Well good luck on it, If I were you I would explain to the customer that how they have it does not follow the rules of normaliziation. If they do not want further problems down the road then they should make the adjustments concerning Primary keys and soforth. It is unfortunate that we can not help people that insist on doing it the wrong way.
golsen
I totally agree.
tried to explain it to them, however they insist that they want to be able to change the Folder Names, which is the PK.
That's just how the corporate world works when you have do deal with people who has no idea about databases and what they are requesting.
However, I still think it's a way to do this.
Geir
golsen
I got it to work.
I was actually a small fix. I just removed the update statement for the case list, which was the following code:
***********************************************
DoCmd.RunSQL "UPDATE [Case List] SET [Case List].[cl_Folder Name:] = '" & newFolderName & "' WHERE [Case List].[cl_Folder Name:] = '" & oldFolderName & "';"
***********************************************
Since the form was based on the CaseList (as recordsource), I didn't have to update that one. It does that when you are renaming the textbox that holds that value. And that is why it displayed the error saying someone had changed it, since I tried to update it twice.
Thanks for all input!
I'll try and teach the client about the importance of normalization and database structure, however some people just want it their way no matter what.
erwardell
That is fine if they want to change the folder name, but the folder name cannot be a Primary key. if you have another field setup as a PK and as a autonumber then you can have that field in other tables as a foreign key. then with relationships you can have it auto update when they change it. If you could post a sample database I can make the needed changes as an example.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.