Access – Disabling ‘Write Conflict’ Dialog Box (Access 2000 SP2)

Topic

New Reply

I have binded textbox fields (Access 2000 form) to a linked SQL view. When I update a SQL field (SQL UPDATE statement in VBA), everything at that point in time is fine. At least everything appears to be alright to the end user. However, it is when a user tries to then navigate through the recordset, that the “Write Conflict” dialog box appears. I want to disable this. Setting “DoCmd.SetWarnings” to “False”, behind custom navigation command buttons (first, previous, next, last) does not work. I wish it did.

Any help here would be greatly appreciated.
Thanks,
Allen

Reply | Quote

Replies

Allen,

Could you post the code that you have behind your navigation buttons?

Reply | Quote

Here is the code:

Almost 8,000 character long piece of code moved to attachment – Mod

Reply | Quote

I don’t understand the code for the “back” button – there is no instruction to move to the previous record.

But if you use SQL in DAO or ADO to update the record, you are going behind the back of Access, as it were. When you move to another record, Access notices that the record has been tampered with by another process. Try executing the SQL using DoCmd.RunSQL instead of a DAO or ADO instruction.

Reply | Quote

I apologize for including the “Back” button code snippet. It is not a record navigation button. Please disregard this piece of code.

Thank you for taking a look and for your explaination. I appreciate it. I tried DoCmd.RunSQL and did not have any luck. There has got to be a way to turn off the “Write Conflict” dialog box.

Thanks,
Allen

Reply | Quote

Suppressing the error message could be done by writing an error handler, but it is not the way to go. Instead of ignoring the symptom, you should try to find the cause, and do something about it. Perhaps someone can help if you provide more information.

Reply | Quote

Solution Found – Just before I run the DoCmd.RunSQL, which runs a SQL UPDATE statement, I added the code:

Me.Refresh

This code ensures that your data is written to disk. Then the UPDATE statement runs without the “Write Confict” dialog box, which makes sense since the data is stored on the SQL Server.

Thanks for everyone’s help.
Allen

********************************************************************************************
Code Snippet (run when checkbox is single-clicked):

‘Update “tblRequest” OWNER and OWNER_FLAG for the selected request
strSQL1 = “UPDATE tblRequest SET dbo_tblRequest.OWNER = ‘” & gstrUserName & “‘, ” & _
“OWNER_FLAG = 1 WHERE dbo_tblRequest.REQUEST_ID = ” & CInt(Me.REQUEST_ID.Value)

‘Temporarily turn off default update warnings
DoCmd.SetWarnings False

‘Write data to disk
Me.Refresh

‘Execute SQL statement
DoCmd.RunSQL strSQL1

‘Reflect new data changes in form
Me.Refresh

‘Turn on default update warnings
DoCmd.SetWarnings True

Reply | Quote

Solution Found – Just before I run the DoCmd.RunSQL, which runs a SQL UPDATE statement, I added the code:

Me.Refresh

This code ensures that your data is written to disk. Then the UPDATE statement runs without the “Write Confict” dialog box, which makes sense since the data is stored on the SQL Server.

Thanks for everyone’s help.
Allen

********************************************************************************************
Code Snippet (run when checkbox is single-clicked):

‘Update “tblRequest” OWNER and OWNER_FLAG for the selected request
strSQL1 = “UPDATE tblRequest SET dbo_tblRequest.OWNER = ‘” & gstrUserName & “‘, ” & _
“OWNER_FLAG = 1 WHERE dbo_tblRequest.REQUEST_ID = ” & CInt(Me.REQUEST_ID.Value)

‘Temporarily turn off default update warnings
DoCmd.SetWarnings False

‘Write data to disk
Me.Refresh

‘Execute SQL statement
DoCmd.RunSQL strSQL1

‘Reflect new data changes in form
Me.Refresh

‘Turn on default update warnings
DoCmd.SetWarnings True

Reply | Quote

Suppressing the error message could be done by writing an error handler, but it is not the way to go. Instead of ignoring the symptom, you should try to find the cause, and do something about it. Perhaps someone can help if you provide more information.

Reply | Quote

I apologize for including the “Back” button code snippet. It is not a record navigation button. Please disregard this piece of code.

Thank you for taking a look and for your explaination. I appreciate it. I tried DoCmd.RunSQL and did not have any luck. There has got to be a way to turn off the “Write Conflict” dialog box.

Thanks,
Allen

Reply | Quote

Hans,

I have a similar situation in which I’m modifying data in one open form from another form. I first wrote this using DAO recordsets and encounterd the write conflict, I then modified the approach to use docmd.runsql. This worked on my machine, but when I intalled it on the coutomer’s machine the write conflict reappeared. I then tried an OpenQuery approach, again worked on my machine but not on the customers’s. We are both in Windows XP MCE, but he’s using Office 2002 and I’m in Office 2003.

Appreciate any thoughts/insights/advice you might have on this.
Marty

Reply | Quote

Could you close the other form, then run the update code, and finally reopen the form again?

Reply | Quote

I don’t understand the code for the “back” button – there is no instruction to move to the previous record.

But if you use SQL in DAO or ADO to update the record, you are going behind the back of Access, as it were. When you move to another record, Access notices that the record has been tampered with by another process. Try executing the SQL using DoCmd.RunSQL instead of a DAO or ADO instruction.

Reply | Quote

Here is the code:

Almost 8,000 character long piece of code moved to attachment – Mod

Reply | Quote