go to the Access MVP home page go to Crystal's Access Basics Quick Jump

Error Handling
by Crystal

Visual Basic for Applications

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 (thanks for that knowledge, Walter).

Who knows what else... what happens with panic?

Go To Top Quick Jump

Forums and Newsgroups

There is no substitute for interaction with others while you are figuring things out. There are a number of forums and newsgroups you can join for free where experienced folks graciously volunteer their time to answer your questions. You are not alone! Here are 4 links to get you started:

Utter Access
http://www.utteraccess.com/

Yahoo! Tech Groups -- MS Access Professionals
http://tech.groups.yahoo.com/group/MS_Access_Professionals/

Microsoft Communities
http://www.microsoft.com/communities/default.mspx

msdn Database Design Forum — New!
http://social.msdn.microsoft.com/Forums/en-US/databasedesign

Go To Top Quick Jump

Forums and Newsgroups

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:

ignore the statement with the problem
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:

press CTRL-BREAK when the message box pops up (MsgBox line executes),
click Debug to temporary halt processing and go into the code
right-click on the Resume statement
from the shortcut menu, choose —> Set Next Statement
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:

to indicate that the word preceeding the colon is a line label
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

here is an excellent paper on debugging:

Access, VBA, and Visual Basic Debugging Tips and Techniques
by Luke Chung, President of FMS
http://www.fmsinc.com/tpapers/vbacode/Debug.asp

for more comprehensive error reporting, check out:

SimplyVBA Global Error Handler for Visual Basic for Applications
http://www.everythingaccess.com/simplyvba-global-error-handler.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

thanks to Arvin Meyer for setting up this site and giving Access MVPs a place to share with others.

Visitors since Jan 2010

Error Handling by Crystal