Error 3197

[ZEKE]

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.

 

[9_milla]

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

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.