Write Conflict error on unbound form

[tranchemontaigne]

I have a form with an unbound text box that holds a primary key from a table.

I use an OnLoad event event to build a recordset that looks up the values associated with the record referenced in the unbound text box in the parent form. I use the values of this recordset to populate field values in an unbound subform with the goal of controlling edits - no edits may be made without clicking on a SAVE CHANGES command button.

I use code behind a SAVE CHANGES command button to run an update query to change values in the table where the source values used to populate the unbound subform are stored.

When I run the update query I receive the classic "WRITE CONFLICT" error

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.

Copying 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 the changes.

[SAVE RECORD] [SAVE TO CLIPBOARD] [DROP CHANGES]

(1) Why would this be happening since there the table being updated is not open by any forms?

(2) How do I disable this error message so that I can run the update query without having the database user have to know enough to click the "SAVE TO CLIPBOARD" button in the write conflict error message box?

NOTE: Turning warnings off is the same as having the user click the "SAVE RECORD" command button that prevents the update query from executing.

NOTE: I run MS Access 2000 so there is no IDENTITY attribute in the primary table as a suggested workaround in http://support.microsoft.com/kb/295225
________
BUY VAPOR TOWER

 

[pbaldy]

Are you closing the recordset used to populate the form?

 

[tranchemontaigne]

I explicitly set the recordset to nothing before the scope where the recordset is created is desctructed. Please accept my appologies for not fully documenting the code.

Private Sub Form_Load()
'////////////////////////////////////////////////////////////////
'// Subroutine: Form_Load
'////////////////////////////////////////////////////////////////
'// Author: Tranchemontaigne
'////////////////////////////////////////////////////////////////
'// Created:
'// 2 July 2008
'////////////////////////////////////////////////////////////////
'// Modified:
'// Date Editor Description
'// ---------------------------------------------------------
'// 25 Nov 2008 Tranchemontaigne documentation
'/////////////////////////////////////////////////////////////////
'// Description:
'// Dynamically pre-populates most fields on form through a
'// simple lookup
'//
'// List of possible task outcomes is determined by building
'// an array of task entries (sorted by t08_Events_ID) and
'// populating the a array with task numbers until the
'// selected task is reached, then the task number of the
'// prior entry is selected. This value, lngTask, is then
'// passed to fnUpdateListbox which dynamically populates the
'// possible tasks in lboTasks
'//
'/////////////////////////////////////////////////////////////////
'// Variables
'// Variable Type Description
'// ----------------------------------------------------------
'// strSQL String
'// lngAgreement_ID Long
'// lngTask Long
'//
'/////////////////////////////////////////////////////////////////
'// Library References:
'// Visual Basic for Applications
'// Microsoft Access 9.0 Object Library
'// fnLogError (ErrorLog module)
'// fnUpdateListbox (this module)
'/////////////////////////////////////////////////////////////////
On Error GoTo Err_Form_Open

Dim lngTaskBookmark As Long
Dim strSQL As String
Dim lngAgreement_ID As Long
Dim lngTask As Long
Dim rst1 As ADODB.Recordset
Dim alngTaskArray(100) As Long
Dim lngN As Long
Dim lngCounter As Long

'populate static variables
'set control properties
strSQL = "SELECT "
strSQL = strSQL & "* "
strSQL = strSQL & "FROM "
strSQL = strSQL & "t08_Events "
strSQL = strSQL & "WHERE "
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(t08_Events.t08_Select)=-1) "
strSQL = strSQL & "AND "
strSQL = strSQL & "("
strSQL = strSQL & "(t08_Events.t08_Agreement_ID)="
strSQL = strSQL & [Forms]![frmMainMenu]![frmSubform].[Form]![txtAgreement_ID]
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & ";"

'define recordset object
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.Open strSQL

'populate form
'lngTaskBookmark = rst1![t08_Events_ID].Value
Me![cboAgreement_ID].Value = rst1![t08_Agreement_ID].Value
Me![cboPriority].Value = rst1![t08_Priority].Value
Me![txtDate].Value = rst1![t08_Date].Value
Me![lboTask].Value = rst1![t08_Process_Flow_ID].Value
lngTaskBookmark = rst1![t08_Process_Flow_ID].Value
'Me![].Value = rst1![t08_Task_No].Value
Me![t08_Staff_ID_TRP].Value = rst1![t08_Staff_ID_TRP].Value
Me![txtComment].Value = rst1![t08_Comment].Value

'define lngTask
strSQL = "SELECT "
strSQL = strSQL & "t07_Process_Flow.t07_Process_Flow_ID, "
strSQL = strSQL & "t07_Process_Flow.t07_Task_No, "
strSQL = strSQL & "t08_Events.t08_Select "
strSQL = strSQL & "FROM "
strSQL = strSQL & "t08_Events "
strSQL = strSQL & "INNER JOIN "
strSQL = strSQL & "t07_Process_Flow "
strSQL = strSQL & "ON "
strSQL = strSQL & "t08_Events.t08_Process_Flow_ID = "
strSQL = strSQL & "t07_Process_Flow.t07_Process_Flow_ID "
strSQL = strSQL & "WHERE "
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(t08_Events.t08_Agreement_ID)="
strSQL = strSQL & [Forms]![frmMainMenu]![frmSubform].[Form]![txtAgreement_ID].Value
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & ";"

'redefine recordset object
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.Open strSQL

'check for empty recordset
lngN = 0
Do Until rst1.EOF
lngN = lngN + 1
rst1.MoveNext
Loop

'build array of tasks
If lngN > 0 Then
rst1.MoveFirst
lngCounter = 0
Do Until rst1.EOF Or rst1![t08_Select].Value = -1 Or lngCounter = 100
alngTaskArray(lngCounter) = rst1![t07_Task_No].Value
lngCounter = lngCounter + 1
rst1.MoveNext
Loop

'select lngTask
lngTask = alngTaskArray(lngCounter - 1)
Else
lngTask = 0

End If


'populate dynamic task options
Call fnUpdateListbox(lngTask, False)
Me!lboTask = lngTaskBookmark

'housekeeping
Set rst1 = Nothing


Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Description
Call fnLogError(gstrObject, "Form_Open", Err.Description)
Resume Exit_Form_Open

End Sub

________
MAZDA NAGARE HISTORY

 

[tranchemontaigne]

[entry was accidentially posted twice]
________
New Jersey Marijuana Dispensary

 

[tranchemontaigne]

Here's the code block used to save an edits

Private Sub cmdSave_Click()
'////////////////////////////////////////////////////////////////
'// Subroutine: cmdSave_Click
'////////////////////////////////////////////////////////////////
'// Author: Tranchemontaigne
'////////////////////////////////////////////////////////////////
'// Created:
'// 2 July 2008
'////////////////////////////////////////////////////////////////
'// Modified:
'// Date Editor Description
'// ---------------------------------------------------------
'// 26 Sep 2008 Tranchemontaigne Added error checking
'// 15 Dec 2008 Tranchemontaigne Modified to support record
'// edits
'/////////////////////////////////////////////////////////////////
'// Description:
'// Error checking
'// Confirm intent to save record
'// Save record
'// Log transaction
'//
'/////////////////////////////////////////////////////////////////
'// Library References:
'// Visual Basic for Applications
'// Microsoft Access 9.0 Object Library
'// fnLogError (ErrorLog module)
'// fnGet_UserID (WindowsAPI module)
'/////////////////////////////////////////////////////////////////
On Error GoTo Err_cmdSave_Click

Dim lngAnswer As Long
Dim strSQL As String
Dim lngAgreement As Long
Dim strPriority As String
Dim strDate As String
Dim lngProcess_Flow_ID As Long
Dim lngTask_ID As Long
Dim lngTRP As Long
Dim strComment As String
Dim strDescription As String
Dim rst1 As ADODB.Recordset

'error checking
If IsNull(Forms![frmEdit_Event]![cboPriority].Value) = True Then
MsgBox "Please specify the current priority of this agreement"
Forms![frmEdit_Event]![cboPriority].SetFocus
Exit Sub
End If

If IsNull(Forms![frmEdit_Event]![txtDate].Value) = True Then
MsgBox "Please enter the date this event happened"
Forms![frmEdit_Event]![txtDate].SetFocus
Exit Sub
End If

If IsNull(Forms![frmEdit_Event]![lboTask].Value) = True Then
MsgBox "Please select the task that has been completed"
Forms![frmEdit_Event]![lboTask].SetFocus
Exit Sub
End If

If IsNull(Forms![frmEdit_Event]![cboStaff_ID_TRP].Value) = True Then
MsgBox "Please select the person who completed this task"
Forms![frmEdit_Event]![cboStaff_ID_TRP].SetFocus
Exit Sub
End If


'confirm intent to add record
lngAnswer = MsgBox("You are about to record an event. " & _
"Do you wish to proceed?", vbYesNoCancel)
If lngAnswer = vbNo Then
MsgBox "Event was not saved"
DoCmd.Close acForm, "frmAdd_Events"
Exit Sub
ElseIf lngAnswer = vbCancel Then
Exit Sub
End If

'save event
'set variables
lngAgreement = [Forms]![frmEdit_Event]![cboAgreement_ID].Value
strPriority = [Forms]![frmEdit_Event]![cboPriority].Value
strDate = [Forms]![frmEdit_Event]![txtDate].Value

lngTask_ID = [Forms]![frmEdit_Event]![lboTask].Value
lngTRP = [Forms]![frmEdit_Event]![cboStaff_ID_TRP].Value

If IsNull([Forms]![frmEdit_Event]![txtComment].Value) = True Then
strComment = ""
Else
strComment = fnRemoveIllegalCharacters([Forms]![frmEdit_Event]![txtComment].Value)
End If

'detemine process flow ID
'define recordset
strSQL = "SELECT "
strSQL = strSQL & "t07_Process_Flow.t07_Process_Flow_ID, "
strSQL = strSQL & "t07_Process_Flow.t07_Task_No "
strSQL = strSQL & "FROM "
strSQL = strSQL & "t07_Process_Flow "
strSQL = strSQL & "WHERE "
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(t07_Process_Flow.t07_Process_Flow_ID)="
strSQL = strSQL & [Forms]![frmEdit_Event]![lboTask]
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & ";"

'build recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = CurrentProject.Connection
rst1.Open strSQL

'set variables
lngProcess_Flow_ID = rst1![t07_Process_Flow_ID].Value
lngTask_ID = rst1![t07_Task_No].Value

'build update query
strSQL = "UPDATE "
strSQL = strSQL & "t08_Events "
strSQL = strSQL & "SET "
strSQL = strSQL & "t08_Events.t08_Priority = "
strSQL = strSQL & strPriority
strSQL = strSQL & ", "
strSQL = strSQL & "t08_Events.t08_Date = "
strSQL = strSQL & "#" & strDate & "#"
strSQL = strSQL & ", "
strSQL = strSQL & "t08_Events.t08_Process_Flow_ID = "
strSQL = strSQL & lngProcess_Flow_ID
strSQL = strSQL & ", "
strSQL = strSQL & "t08_Events.t08_Task_No = "
strSQL = strSQL & lngTask_ID
strSQL = strSQL & ", "
strSQL = strSQL & "t08_Events.t08_Staff_ID_TRP = "
strSQL = strSQL & lngTRP
strSQL = strSQL & ", "
strSQL = strSQL & "t08_Events.t08_Comment = "
strSQL = strSQL & Chr(34) & strComment & Chr(34)
strSQL = strSQL & ", "
strSQL = strSQL & "t08_Events.t08_Logged_By = "
strSQL = strSQL & Chr(34) & fnGet_UserID() & Chr(34)
strSQL = strSQL & " WHERE "
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(t08_Events.t08_Events_ID)="
strSQL = strSQL & [Forms]![frmEdit_Event]![txtAgreement]
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & ";"

'save edits
MsgBox "edits to t08-Events are problematic"

Debug.Print "t08-Events strSQL update: " & Chr(10) & strSQL & Chr(10)
'Call fnRunSQL(strSQL)
DoCmd.RunSQL strSQL

'log transaction in t08_Events
'build transaction description
strDescription = lngAgreement
strDescription = strDescription & "|Priority = "
strDescription = strDescription & strPriority
strDescription = strDescription & "|Date = "
strDescription = strDescription & strDate
strDescription = strDescription & "|Process_Flow_ID = "
strDescription = strDescription & lngProcess_Flow_ID
strDescription = strDescription & "|Task_ID = "
strDescription = strDescription & lngTask_ID
strDescription = strDescription & "|TRP = "
strDescription = strDescription & lngTRP
strDescription = strDescription & "|Comment = "
strDescription = strDescription & strComment

'log entry
Call fnLogTransaction("UPDATE", "t08_Events", [Forms]![frmEdit_Event]![txtAgreement], strDescription)

'housekeeping
DoCmd.Close acForm, "frmEdit_Event"
Set rst1 = Nothing

'Update master contract record
strSQL = "UPDATE "
strSQL = strSQL & "t04_Agreement_Master "
strSQL = strSQL & "SET "
strSQL = strSQL & "t04_Agreement_Master.t04_Process_Flow_ID = "
strSQL = strSQL & lngProcess_Flow_ID
strSQL = strSQL & ", "
strSQL = strSQL & "t04_Agreement_Master.t04_Status_Date = "
strSQL = strSQL & "#" & strDate & "#"
strSQL = strSQL & ", "
strSQL = strSQL & "t04_Agreement_Master.t04_Priority = "
strSQL = strSQL & strPriority
strSQL = strSQL & " WHERE "
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(t04_Agreement_Master.t04_Agreement_ID)="
strSQL = strSQL & lngAgreement
strSQL = strSQL & ")"
strSQL = strSQL & ")"
strSQL = strSQL & ";"
Debug.Print "t04_Agreement_Master strSQL update: " & Chr(10) & strSQL & Chr(10)
Call fnRunSQL(strSQL)

'log transaction in t04_Agreement_Master
'build transaction description
strDescription = lngAgreement
strDescription = strDescription & "|Priority = "
strDescription = strDescription & strPriority
strDescription = strDescription & "|Date = "
strDescription = strDescription & strDate
strDescription = strDescription & "|Process_Flow_ID = "
strDescription = strDescription & lngProcess_Flow_ID

'log entry
Call fnLogTransaction("UPDATE", "t04_Agreement_Master", lngAgreement, strDescription)

'update display
With [Forms]![frmMainMenu]![frmSubform].Form
![t04_Priority].Value = strPriority
![cboProcess_Flow_ID] = lngProcess_Flow_ID
![txtStatus_Date] = strDate
![subfrmEvents].Requery
End With


Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Call fnLogError(gstrObject, "cmdSave_Click", Err.Description)
Resume Exit_cmdSave_Click

End Sub

________
Health Shop

 

[tranchemontaigne]

Perhaps a copy of the DB will help

Any assistance would be appreciated
________
Subaru 360

 

[pbaldy]

As noted elsewhere, the form is bound to t08_Events which is also being updated in the code, so that's likely the source of your problem.

 

[tranchemontaigne]

pbaldy,

Thanks immensely!

This is exactly what I needed to know to fix all related problems. As follow up, I discovered that though I had bound a form to use the FieldList to initially build form controls, I failed to unbind the form after unbinding the individual controls. I also solved the reloading of the events subform by dynamically changing the subform before updating the table values, then I reloaded the events subform.
________
PIERO FERRARI SPECIFICATIONS

 

[pbaldy]

No problem, glad it helped you out.