Why is Recordset Deleted in this code?

[Thom]

OK, when I raise an error in the following code, the Recordset seems to be LOST in the Test form!

Private Sub cmdSaveTest_Click()

On Error GoTo ErrHandler

Dim cnn1 As ADODB.Connection
Dim Counter As Integer
Dim sSQL3 As String
Set cnn1 = New Connection
cnn1.Open g_strConnection

'BEGIN TRANSACTION!! ******************
cnn1.BeginTrans

If cnn1.State = adStateOpen
Counter = 1 ' for FIRST record!
Rs1.MoveFirst

Do Until Rs1.EOF = True

sSQL3 = "INSERT Into Results(Number, QuestionNumber, UserAnswer) "
sSQL3 = sSQL3 & "VALUES (" & Num & "," & Counter & "," & rs1!Answer & ")"

cnn1.Execute sSQL3, , adExecuteNoRecords
rs1.MoveNext
Counter = Counter + 1
Loop

' NEED TO RAISE AN ERROR TO TEST THIS!!!
Err.Raise 3732

' IF WE GET HERE, ALL's WELL!
cnn1.CommitTrans

Else ' NO STATE OPEN!!
MsgBox "NO CONNECTION!"
cnn1.Close
Rs1.MoveFirst
cmdSaveTest.SetFocus
Exit Sub
End If

ErrHandler:

cnn1.RollbackTrans
MsgBox Err.Number & Err.Description
cnn1.Close
cmdSave.SetFocus
Exit Sub

End Sub

When an error occurs, & I go back to the Test form It's like the Questions & answers have been wiped clean! The last record/question is showing in the form-but none of the others are? Does anyone have any idea why? Is there something in the code above that is deleting the recordset? I'd appreciate any help!

[sanon]

According to your code, in your Do...Loop, you processed your query until the last record of rs1 and then your program executed the next line which was Err.Raise. Therefore, the program will jump to ErrHandler and execute every line there. Obviously, there is no a single statement in there that tell you to move the rs1 to the first record. As a result, you can see only the last record in your test form.

Hope this will help..

[Thom]

Oh wow! THANKS Sanon! Based on your reply I'm changing the code to check for an open connection(adStateOpen) and then doing the INSERT statement:

Set cnn1 = New Connection
cnn1.Open g_strConnection

If cnn1.State = adStateOpen Then 'CONNECTION HAS OPENED!

Counter = 1 ' for FIRST record!
rs1.MoveFirst
Do Until rsFathom.EOF = True etc...

Else

MsgBox "Sorry! No Connection was made"
cnn1.Close
rs1.MoveFirst
cmdSave.SetFocus
Exit Sub
End If

One question though; HOW in the heck do I raise a NO connection error to test this code? Or do you think this code will take care of the problem? Thanks a bunch for your help so far!

[sanon]

I'm not sure I understand your last question correctly or not. If you want to have your own error code and description, you can do like this:

On Error Goto errHandler

Set cnn1 = New Connection
cnn1.Open g_strConnection

If cnn1.State = adStateOpen Then
    '....your code...bla..bla..bla...

     'This error code is used for testing in order to know the program goes this far
      Err.Raise vbObjectError + 1000
Else
    rs1.MoveFirst
    '...bla..bla..bla...
End If
Exit Sub
errHandler:
    rs1.MoveFirst
    If Err.Number = 1000 Then
         Err.Clear
         Err.Raise 1000, TypeName(Me), "Connection is successful"
    Else 'This code will take care of other errors 
          Err.Raise Err.Number, Err.Source, Err.Description
    End If