Error 3219 - Operation is not allowed in this context

I am using VB 6, ADO, and SQL 7 in a multiuser environment for the project I am currently working on. I have encountered a problem with this Error 3219 and could use some help.

I am using an ADO recordset's .Update event to write data to the database. The problem occurs when two users happen to edit the same record simultaneously. When the second user tries to update a recordset after the first user has already updated it, I get the SQL error message -2147217864 "Row cannot be located for updating. Some values may have been changed since it was last read". My procedure for saving the record is exited and the next command I issue is to requery the database to update everything. That is when I get the 3219 error message. Anything I try to do to the recordset at this point (close, requery, resync, etc)
gives me the same error message. The only way I can resume my program is to reopen the recordset. I would appreciate any suggestions, explanations, or solutions for this error.
See below for a portion of the code.

Thanks for any and all help provided.

The portion of my code that creates this error is as follows:

(mrsEmployee is a module level ADODB recordset variable that is opened on Form Load and Closed on Form Unload)
With mrsEmployee
!EmpID = txtEmpID
!LName = txtLName
!FName = txtFName
!MName = txtMName
!Suffix = txtSuffix
!BirthDate = Date2Field(mskBirthDate)
!SSNum = mskSSNum
!DriversLic = txtDriversLic
!DateEmployed = Date2Field(mskDateEmployed)
!PerDiemRate = Val(txtPerDiemRate)
!MileageRate = Val(txtMileageRate)
!SpouseName = txtSpouseName
!SpouseEmployer = txtSpouseEmployer
!EmergencyContact = txtEmergencyContact
!Remarks = txtRemarks
CreateModBy mrsEmployee
.Update
End With


In my error trap I have tried the following:
If Err <> 0 Then
gstrProcedure = "EnableMovementKeys" & " - " & "frmEmployee"
If gblnOpenCNError Then
gstrProcedure = gstrProcedure & " (Connection not open - error trapped) "
End If
LogErrors (gstrProcedure)
Select Case Err
Case 3219
' CloseEmployeeRecordset 'tried to close the record set with this procedure and it generated err 3219 by skipping this step, I was able to continue with the program execution.
OpenEmployeeRecordset
EnableMovementKeys
Case Else
MsgBox Err & " - " & Err.Description & vbLf & "Message has been logged.", vbOKOnly + vbCritical, "Error"
End Select
End If

What kind of locking and cursor are you using? If there is a chance of two users both hitting the same records, you should use Pessimistic locking. If you need th see changes in the data as they occur, use a dynamic cursor. (There is a performance hit for a dynamic cursor, but you may need it if this is a common occurrance)

I am using adLockPessimistic and adOpenKeySet cursors. The chances of two users hitting the record at the same time are probably slim, I was just trying to prepare for the event by placing the error trap to inform the user that the changes were canceled due to editing by another user. After that I wanted to update the record by requerying the recordset. That is where the problem came in. The only way I could requery the recordset was to re-open it. I could not close the recordset without getting that 3219 error.

You would not have to requery the database if you use adOpenDynamic

I recall getting this error before. I think you have to cancel the current action. I think I solved that by putting something in the error handler that says if err number is = to the error your getting then if editmode not equal to vbedit none .cancel.

How about this then??
I have an office PC and three PCs at three different sites. All three site PCs and the setup are identical. I get Error 3219 - Operation is not allowed in this context only for one of the sites just prior to updating one particular table. All tables synch at two sites all tables other than this particular one synch at the third site. Only one table give me this error.
Any ideas ????
Thanks

@onefromoz

Welcome to the forums! :wave:

It will be better if you will just start your own thread so the focus is more on you and not to the thread starter of this one. :)