If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error 3197
The error message box reads:
Error 3197 "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. Copy 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. The "Save Record" button is not available. Background: I am working with Access 2003 SP3 on a Windows XP Professional version 2002 SP3 machine. Table reside on a SQL Server 2005 and are linked via ODBC to the Access database. Now I will explain my problem. I have a Form called "Jobs" bound to the "Header" table . It has two tabs each has a subform. Tab1 subform is unbound and has no issues. Tab2 subform is bound to the "Details" table with the Parent/Child relationship to Form "Jobs". The "Details" table has fields and one of those fields is a Boolean field called "IsRemoved". "Details" subform Recordsource has WHERE "IsRemoved" = False. The subform displays this field as a checkbox. When user clicks on a "IsRemoved" checkbox to remove this record, i use SQL to identify all records associated with this one record and mark them "IsRemoved" = true as well. The error occurs here when I try to requery the subform to remove the records marked "IsRemoved". Code below: 'Mark all associated records IsRemoved field True as well strSQL = "UPDATE Details SET Details.IsRemoved = True " strSQL = strSQL & "WHERE (((Details.HeaderID)=" & Forms!Header!ID & "))" DoCmd.RunSQL (strSQL) 'Requery subform "Details" to hide records marked IsRemove = true Forms!Header![Details].Form.Requery The error pops up when trying to execute the requey line. I trap the error after the user clicks on any button or the X and error handle it to retry then requery line and then resume next. The error does not occur again and then code finishes to do exactly what I asked it to do. All my Bit fields in my SQL tables do not allow null (0 or -1) and every tables has a TimeStamp field in it. So my question is, What do I need to do to prevent the user from seeing this "Write Conflict" message pop up? Sorry for the long narrative. But I read posts all the time that do not provide enough information to diagnose the problem. |
#2
|
|||
|
|||
Error 3197
Hi there
I'm having a very similar problem with using Access 2003 as the frontend and SQL Server 2005 as the backend. Though my problem only occurs as a result of a trigger checking whether or not a new recordset can be posted or not based on the company's business rules. The trigger is designed to do a rollback and not commit any changes to the underlying table if all requirements aren't met. I've been through many forums all of which don't suite me needs to finding a solution. Many claim that you need to have a timestamp, or check for bit fields that shouldn't have any 'NULLS' in it or to try the 'Me.Requery method before updating the recordset. Also to try using the 'Applications.SetOption = False' or I have checked all these suggestions and still have no solution. The trigger itself has the 'SET NOCOUNT ON' and when testing the trigger it works fine. Stepping through my code works fine as well until I hit the 'Docmd.close' which results in the 'write conflict' message being displayed. All three buttons 'SAVE', 'COPY' and 'DROP' changes are enabled and when clicking on 'SAVE', it updates correctly. I would like this message not to display and to have the default option to SAVE as the trigger has already validated what needs to be updated and or to just completely suppress/hide the message box. I have also disabled the 'Confirm Record Changes' and 'Record Deletions' checkboxes under the Tools, Option, Edit/Find Tabs menu. Everything I've attempted thus far has been fruitless. My form consists of a 4 Tabs, 1 of which has two subforms. There are various textboxes and combo boxes that are linked. I have an update button that posts the new recordset into a table on one of the subforms. Is there perhaps and API call that could possibly disable the message box from being displayed or to somehow manually use a default option for the messagebox? Please assist Many Thanks "ZEKE" wrote: The error message box reads: Error 3197 "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. Copy 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. The "Save Record" button is not available. Background: I am working with Access 2003 SP3 on a Windows XP Professional version 2002 SP3 machine. Table reside on a SQL Server 2005 and are linked via ODBC to the Access database. Now I will explain my problem. I have a Form called "Jobs" bound to the "Header" table . It has two tabs each has a subform. Tab1 subform is unbound and has no issues. Tab2 subform is bound to the "Details" table with the Parent/Child relationship to Form "Jobs". The "Details" table has fields and one of those fields is a Boolean field called "IsRemoved". "Details" subform Recordsource has WHERE "IsRemoved" = False. The subform displays this field as a checkbox. When user clicks on a "IsRemoved" checkbox to remove this record, i use SQL to identify all records associated with this one record and mark them "IsRemoved" = true as well. The error occurs here when I try to requery the subform to remove the records marked "IsRemoved". Code below: 'Mark all associated records IsRemoved field True as well strSQL = "UPDATE Details SET Details.IsRemoved = True " strSQL = strSQL & "WHERE (((Details.HeaderID)=" & Forms!Header!ID & "))" DoCmd.RunSQL (strSQL) 'Requery subform "Details" to hide records marked IsRemove = true Forms!Header![Details].Form.Requery The error pops up when trying to execute the requey line. I trap the error after the user clicks on any button or the X and error handle it to retry then requery line and then resume next. The error does not occur again and then code finishes to do exactly what I asked it to do. All my Bit fields in my SQL tables do not allow null (0 or -1) and every tables has a TimeStamp field in it. So my question is, What do I need to do to prevent the user from seeing this "Write Conflict" message pop up? Sorry for the long narrative. But I read posts all the time that do not provide enough information to diagnose the problem. |
Thread Tools | |
Display Modes | |
|
|