strive4peace

If you like my work, please donate so I can keep on sharing.
thank you





watch! 2015 Microsoft MVP Virtual Conference on Channel 9 (MSDN) for Access
53 minute presentation

Error Handling

Access Basics Video Tutorials Learn VBA ListFiles Database Random Picker Error Handling Reference Database Whistles & Bells Help Examples Crystal's Analyzer Code Documenter

What is an error handler?
   On Error Goto 0
      On Error Resume Next
         On Error GoTo LineLabel
            Resume 
               Resume Next  
                  Resume LineLabel
                     Debug.Print "My variable = " & variable_name
                        MsgBox "My variable = " & variable_name,,"HERE"
                           Exit Sub
                              Exit Function  

An error handler is an emergency plan.

Think about ...
  • fire drills in grade school
  • the diagram of the plane and all the exits when you fly
  • lighted Exit signs in crowded places
  • storm cellars
  • run-away exit ramps
... a plan in case something goes wrong

In programming, just as real life, it is usually best to have a plan in place.

What happens when there is no plan?

For one thing, the value of global variables can be lost ... and who knows what else... what happens with panic?

Go To Top           Quick Jump



Quick Jump To Sections

Go To Top

Error Handling Statements

On Error GoTo 0

= No Plan
cancel a possible previous plan and announce to Access that you have no plan

how can you remember this?
0 = False
= Cancel any previous plan that might have been in place
Go to false? What? There is no plan ... if something unexpected happens -- panic! Do whatever comes to your mind in the emergency.

On Error Resume Next

says two things:
  1. ignore the statement with the problem
  2. resume with the next statement
(the door you are approaching is on fire, try the next one)
— so there is an error handler (instructions for what to do)

On Error GoTo LineLabel

acts as a detour sign to tell Access what to do if an error happens.


Go To Top           Quick Jump


Error Handling Code

At the top of your program, right after the procedure declaration (skip a line first for better readability), tell Access there is a plan in case of emergency. (If the car breaks down, the mechanic's number to call is specified)

... then come the statements of your procedure ...

... then the lines at the bottom with the Exit code and the actual error handler code – be sure to replace ProcedureName

'This is my "shell" error handling code:
'~~~~~~~~~~~~~~~~~~~~~~
   'set up Error Handler 
   On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~ 

'   ... then your statements

 
'put this at the end of the procedure
 
'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
   On Error Resume Next
   'release object variables if applicable-- ie:
'   if Not rs is Nothing then
'      rs.close
'      set rs = Nothing
'   end if
'   set db = nothing 

   Exit Sub 'or Exit Function
 
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
     & "   ProcedureName"
   
   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement 
   Resume
   
End Sub


WHERE
ProcedureName is the name of your Sub or Function so you can identify what code the problem is in when you see the error
Go To Top           Quick Jump

Code Explanation

The line labels do not matter (Proc_Exit:, Proc_Err:), I like to use the same ones all the time — they only have to be unique within a procedure.

If you get an error:
  1. press CTRL-BREAK when the message box pops up (MsgBox line executes),
  2. click Debug to temporary halt processing and go into the code
  3. right-click on the Resume statement
  4. from the shortcut menu, choose —> Set Next Statement
  5. then press F8 to resume with the statement that caused the problem — you can fix it!
    — or at least see what the problem is <smile>
press F8 to execute the next statement

press F5 to continue execution automatically

While I am developing, I like to make the error handler go to the line that caused the problem so I can see where it is. Resume goes back to the offending line. When code Stops, press F8 to execute one statement at a time.


Go To Top           Quick Jump

Colons and Line Labels

A colon can be used:
  1. to indicate that the word preceeding the colon is a line label
  2. as a seperator to combine 2 statements onto one line
Proc_Err:
a line label is a word before a colon
it is not an executable statement

Stop: Resume
when used like this, the colon allows you to combine multiple statements on a single line

Go To Top           Quick Jump


Links

        article on debugging:

Debugging VBA Code -- easy to understand!
  by Chip Pearson
http://www.cpearson.com/excel/DebuggingVBA.aspx

        comprehensive error reporting tool and articles:

vbWatchdog - Global Error Handler
  by Wayne Phillips
http://www.everythingaccess.com/vbwatchdog.htm

Go To Top           Quick Jump


Comments


"...This line looks especially handy in the error handling: "I like to make the error handler go to the line that caused the problem so I can see where it is." Too often we’re in a quandary when we encounter errors ..."

— Kathy


"Wow, awesome explanation, thanks Crystal!"

— Lee, MS_Access_Professionals
Thanks for the inspiration to make this page (smile)
– Crystal



If you have comments or want to tell me how my page helped you, thank you — leave your comment on a thread I am currently posting to OR



Go To Top           Quick Jump

Free Book on Access: Access Basics

Free Tutorials: Video Tutorials

for information on private programming and training, or if you have comments and ideas, I'd love to hear from you


Copyright © 2009-2015 Crystal Long | All rights resorved | Last updated Dec 2015 |