Please review code

[AccessRobo]

I have very basic skills and am trying to learn VBA. I just added some error handling code to code I took out of my tutorial book. Please would some of the members have a look and tell me if I have made a mistake. I understand you have to close things and then resume them, but I don't really know what my code says. I read, by not closing and destroying objects defensively you can cause problems and waste memory.
My code is:

Private Sub Form_AfterInsert()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Product")

MsgBox "Added " & rst.RecordCount & _
"th Product."

rst.Close
Set rst = Nothing
Set db = Nothing

CleanUpAndExit:
Exit Sub
ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit
End Sub

 

[mdlueck]

I would suggest putting the destroy of the objects in some place that is guaranteed to always be executed, even if an error occurs:

Public Function Update() As Boolean
On Error GoTo Err_Update

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim lRecordsAffected As Long

  'Define attachment to database table specifics and execute commands via With block
' bla bla bla code here.........

  'Good return code
  Update = True

Exit_Update:
[B]  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing
[/B]
  [COLOR=Red]Exit Function[/COLOR]

Err_Update:
  Call errorhandler_MsgBox("Class: clsObjMEToolingToolTypeTbl, Function: Update()")
  Update = False
  Resume Exit_Update

End Function

On Error the code jumps to the Err_Update: label. Once there, at the conclusion of that label it is told to "Resume Exit_Update" which at that label is the guaranteed destroy of the objects, followed by "Exit Function" which jumps around the error handler at the bottom of the function and exits immediately. This "Exit Function" prevents non-error execution from ever getting to the Err_Update: code.

 

[AccessRobo]

Thank you Michael for your response. I still don't comprehend things properly. But I will ponder things a bit more and look at my other code in my tutorials. I will read your notes a few more times also. One day I am hoping things will click.

 

[KenHigg]

You may want to step through the code as it executes to see how it all works and open the locals window to watch the vars change. F8 steps one line at a time and when you get want to execute to the end of the code block do F5. And if you hoover the mouse pointer over the var in the code window the vars value will be displayed...

 

[AccessRobo]

Thank you Ken. I will do that.

 

[AccessRobo]

I have changed my code after reading Michael's comment. And I stepped through the code as Ken suggested, it just kept on going until it got to exit sub, the yellow disappeared and just the code was left.

Private Sub Form_AfterInsert()
On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Product")

MsgBox "Added " & rst.RecordCount & _
"th Product."

CleanUPAndExit:
rst.Close
Set rst = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")

Resume CleanUPAndExit

End Sub

 

[KenHigg]

Is there a particular part of the code you do not understand?

 

[mdlueck]

And I stepped through the code as Ken suggested, it just kept on going until it got to exit sub, the yellow disappeared and just the code was left.

Were you expecting an error? Quickly glancing I see no obvious errors, so it may very well have run successfully and thus not gone into the error handler.

If you want to see the error handler actually handle an error, perhaps place an obvious error on purpose, such as:

Private Sub btnTestError_Click()
On Error GoTo Err_btnTestError_Click

Dim strTest As String
Dim intTest As Integer

strTest = "A"
intTest = strTest + 1

Exit_btnTestError_Click:
  Exit Sub

Err_btnTestError_Click:
  Call errorhandler_MsgBox("Form: Form_quotesideas, Subroutine: btnTestError_Click()")
  Resume Exit_btnTestError_Click

End Sub

Results in the following MsgBox error message:

Date: 20120530 Time: 10:22:29
AppErrorMsg: Form: Form_quotesideas, Subroutine: btnTestError_Click()
Error Source: SchemaIdeas
Error Number: 13
Error Description: Type mismatch
MessageText: The data is invalid.

 

[AccessRobo]

Ken after Michael reminded me of what fires first, if I can put it that way. I believe I understand. Thank you for checking my code Michael. Thank you to both of you for being so helpful. Craig