Write Conflict resulting in Clipboard message

[GilesC]

Hi,
I have an Access ADP linked to an SQL 7 backend that is causeing me a problem in one particular form.
Occasionally when a user selects a value from a combo box, that in turn runs the code below, it generates a write conflict.
The exact message is

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 to 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.
You then have 3 options.
Save Record, Copy to Clipboard and Drop Changes

Now here is the twist. This can happen even if no one else is in the database and most importantly after selecting any one of the options it will prompt you again if you try and leave the record (even tapping esc doesn't help). The only way out it Ctrl Alt Del at this stage!
I have looked at the MS Knowledge Base Q280730 as suggested in similar questions posted, and made the changes suggested but to no avail...
Any help is appreciated.
Giles

Code:
Private Sub fkCallActionCompleted_AfterUpdate()
On Error GoTo myErr
Dim myCurrentStage As Integer, mySuccess As Integer, myAction As String, myResult As Integer
Dim myNewInstruction As String
myCurrentStage = Me!Stage
mySuccess = fkCallActionCompleted
myAction = Me!ActionRequired
myResult = Me!fkCallActionCompleted
DoCmd.GoToRecord , , acNewRec
ClosingCall:
If myResult = 1 Then 'the call was successful
If myCurrentStage + 1 > 3 Then
If MsgBox("Do you want to mark this call as closed?", vbYesNo + vbQuestion + vbDefaultButton1, "Call Closed?&quot = vbYes Then
MsgBox "Call Closed"
Me!Stage = 4
Me!ActionRequired = "CLOSED"
Forms!FM_Communications!OPTGRP_Resolved = -1
Exit Sub
End If
End If
'the call requires more action
NewInstruction:
myNewInstruction = InputBox("Please enter further action required", "Further Action&quot
If myNewInstruction = "" Then
If MsgBox("You have not enetered any further actions, therefore it is assumed the call is closed" & vbCr & "is this correct?", vbYesNo + vbDefaultButton2 + vbQuestion, "Close Call?&quot = vbNo Then
GoTo NewInstruction
Else
GoTo ClosingCall
End If
Else
If myCurrentStage + 1 > 3 Then myCurrentStage = 0
Me!Stage = myCurrentStage + 1
Me!ActionRequired = myNewInstruction
End If
Else ' the call was not successful
Me!ActionRequired = myAction
Me!Stage = myCurrentStage
End If
Me!RecontactTime = Now() + (1 / 24)
Dim myNewAction As Long
myNewAction = Me!ActionId
Me.Requery
Me!ActionId.SetFocus
DoCmd.FindRecord myNewAction
If myResult = 1 And Me!Stage = 4 Then Me!ActionRequired = Null
Me!RecontactTime.SetFocus
myExit:
Exit Sub
myErr:
Resume myExit
End Sub

 

[thornmastr]

What Access is trying to tell you and is not telling you very well, is that you’ve locked the same record twice.

Make sure you have not defined the record set twice such as using a record set clone, or have bookmarked a record on your form and then book marked the same record from another record set.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[GilesC]

Thanks Robert
As far as I can remember there are no bookmarks or any cloning in the system but I'll double check.
Thanks for giving me hope
Giles

 

[thornmastr]

Giles,

Be very sure when you finish using a recordset to not only close it, but release the record set reference, ie,

set rs = nothing,

especially when using a sql server type BE. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com