ADO error trapping hell

[silverblatt]

I'm developing a VB6 application that uses a SQL back end, and am running into a combination ADO and error-trapping issue that I see from time to time, and which I've never found any solution to. The code (shown below) is in the main data entry form, and works properly most of the time to insert a new record or update an existing one, as the case may be. The problem arises when the user attempts to save a record that would result in a duplicate value in any of the several columns that have unique indexes applied to them. The error handler properly traps and displays the "unique key constraint" error thrown by the ".Update" statement, but then a second error (Run-time error '3219': Operation not allowed in this context.") is thrown by the "CtRs.Close" statement. So, my questions are:

1. Why does the "On Error Resume Next" statement not work in this context? In other words, why does it not allow execution to continue despite the second error, as the documentation says it should do?
2. Why does the "CtRs.Close" statement throw an error in the first place, despite the fact that it is only executed if the recordset has been verified to be in an open state?

I know I could bypass the "CtRs.Close" statement if the trapped error is a unique key constraint error, but that would still leave the recordset in limbo and unavailable for future use. I also know that I could add more code to check for duplicate values in each of the indexed columns BEFORE attempting a save, but in addition to being a bunch of extra coding it would seem to address only one of many possible SQL errors that could cause a save to fail. I also know I could switch to exclusively local recordset variables and simply set them all to Nothing as I exit each Sub and Function, but that's a bunch of extra coding and kills the advantage of reusable object variables.

Most fundamentally, I find it really frustrating that "On Error Resume Next" appears not to work as it is supposed to, and that there's no apparent way to clean up or reset this recordset object after each use as good practice dictates. Any suggestions will be appreciated.

'these declarations appear in a separate code module
Public CtConn As New ADODB.Connection, CtRs As New ADODB.Recordset, SQLString As String

Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Error
If DataOK Then
SQLString = "SELECT * FROM Contract"
With CtRs
.Open SQLString, CtConn, adOpenDynamic, adLockPessimistic
If FormMode = "Add" Then
.AddNew
Else
.Find ("ContractID = " & Format$(KeyValue))
End If

'code here assigns value of form controls to their corresponding recordset fields

.Update
If FormMode = "Add" Then
frmMain.txtNewKeyValue = !ContractID
End If
Unload Me
End With
End If
cmdSave_Click_Exit:
On Error Resume Next
If CtRs.State = adStateOpen Then
CtRs.Close
End If
Exit Sub
cmdSave_Click_Error:
MsgBox "Save failed on error " & Err.Number & " (" & Err.Description & ")"
GoTo cmdSave_Click_Exit
End Sub

[si_the_geek]

1. OERN is not working as you want because you are still within error-handling mode, so VB does the safe thing and raises all errors. There is an easy "fix", but OERN is the wrong thing to do there - because any error will leave the recordset open (without warning), and therefore cause problems elsewhere in the program.

2. A record is being edited, so it either needs to be written to the database or cancelled. You can check the .EditMode property, and if apt call .CancelEdit before trying to close it (I'm not sure I got these names correct!).

[silverblatt]

The method is .CancelUpdate, and it solved the problem. After calling it, the call to .Close works fine.

Thanks, si_the_geek!

[jpbro]

A quick note on your error handler: If you use

Resume cmdSave_Click_Exit

instead of:

GoTo cmdSave_Click_Exit

Then your subsequent On Error Resume Next call should behave as expected.

[silverblatt]

Thanks. I had already made that exact change in response to your original suggestions.