write conflict

[alicejwz]

Write Conflict
“This Record has been changed by another user since you started editing it. If you save the record you will overwite the changes the other user made. Copying the values into the clipboard will allow you to look at the values the other user entered, and then paste your changes back in if you decide to make changes.
There are three choices [Save Record] [Copy to Clipboard] [Drop Changes]”

I received a Write Conflict error. I believe it has something to do with a record being opened for a length of time than the record is being updated, because I am the only user updating the record.

I am using ACCESS 2000 as FE and SQL SERVER 7.0 as BE.
I am executing two stored procedures within a procedure and then requery the subform that is when the error occurred.

Thanks in advance for your assistance!

Here is my code:
Sub save_shipped_input()
Dim save_shipping_history As New ADODB.Command
Dim clear_temp_fields As New ADODB.Command

Me!shipping_sched_list_subform.Form!cust_ord_qty.Locked = False

' sp adds and deletes a record if an error occurred rollback trans

With save_shipping_history
.ActiveConnection = CurrentProject.Connection
.CommandText = "spUpdate_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@t1", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@t2", adInteger, adParamOutput)
.Execute , , adExecuteNoRecords

End With


‘sp clears some fields

With clear_temp_fields
.ActiveConnection = CurrentProject.Connection
.CommandText = "spClear_temp_shipping_fields"
.Execute , , adExecuteNoRecords
End With

‘if the return value is greater than one an error has occurred
If save_shipping_history.Parameters("ret_val").Value > 0 Then

If Me!shipping_sched_list_subform.Form!shipped_qty_temp <> "" Then
Me!shipping_sched_list_subform.Form!shipped_qty_remaining = Me!shipping_sched_list_subform.Form!shipped_qty_temp
MsgBox "There is an error occurred in the record you have entered. Please write down the work order number and contact the IT Department.", vbInformation
End If

Me!shipping_sched_list_subform.Form!shipment_complete = False

End If


‘Write conflict occurred in the subform
Me!shipping_sched_list_subform.Requery

Call enable_disable_form(True)

Call prep_sched_input

Me!input_shipped_qtys.Caption = "Enter Shipped &Qty's"

Set save_shipping_history = Nothing
Set clear_temp_fields = Nothing

End Sub


‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim sq As Long, sqrm As Long, sqt As Long, update_qty_remaining As Long

If Not IsNull(Me!shipped_qty) Then
If IsNull(Me!shipped_date) Then
MsgBox "You entered a shipped quantity but did enter date it shipped on.", vbExclamation
Me!shipped_date.SetFocus
Cancel = True
Exit Sub
End If
Else
Me!shipped_date = Null
Me!reason_code = Null
Me!shipment_complete = False
End If

If update_qty_remaining = True Then
If IsNull(Me!shipped_qty) Then
sq = 0
Else
sq = Me!shipped_qty
End If

If IsNull(Me!shipped_qty_remaining) Then
sqrm = 0
Else
sqrm = Me!shipped_qty_remaining
End If

If IsNull(Me!shipped_qty_temp) Then
sqt = 0
Else
sqt = Me!shipped_qty_temp
End If

Me!shipped_qty_remaining = sqrm - (sq - sqt)
Me!shipped_qty_temp.Value = Me!shipped_qty.Value
update_qty_remaining = False
End If

'write conflict occurred after executing the next calling sub routine
Call tts_reason_code

End Sub
‘’’’’’’’’’’’’’’’’’’’’’’’’
Sub tts_reason_code()

If Me!shipped_date.Value > Me!mfg_ord_due.Value Then
Me!reason_code.TabStop = True

Else
Me!reason_code.TabStop = False
code.Value = ""
End If

End Sub

 

[alicejwz]

Yes, the subform is bound to a table. In the procedure I ran two stored procedures and they both update the same record in that table I believe that is causing the write conflict error. I believe I might have a solution to the problem. I will see if it will work.


Thanks for your reply.
Have a good day!