Access VBA Macro Programming

Try this simple examplewithouta disk in drive D. Place the code into a code module and

then run it by pressingF5:

Sub Test_Error()

temp = Dir("d:\*.*")

End Sub

This will produce an error message saying that the D drive is not ready. In normal terms, your

program has crashed and will not work any further until this error is resolved. Not very good

from the user’s point of view!

You can place a simple error-trapping routine as follows:

Sub Test_Error()

On Error GoTo err_handler

temp = Dir("d:\*.*")

Exit Sub

err_handler:

MsgBox "The D drive is not ready" & " " & Err.Description

End Sub

The first line sets up a routine to jump to when an error occurs using theOn Errorstatement.

It points toerr_handler, which is a label just below theExit Subline further down that will

deal with any error condition. The purpose of a label is to define a section of your code that

you can jump to by using aGoTostatement.

The line to read the D drive is the same as before, and then there is anExit Subline,

because if all is well you do not want the code continuing into theerr_handlerroutine.

If an error happens at any point after theOn Errorline, the code execution jumps to

err_handlerand displays a message box that says drive D is not ready. However, you may

have noticed that the code execution jumps toerr_handlerwhenanyerror occurs, not just

the drive not ready error. An error could occur because you made a mistake typing in this code.

Fortunately, you can interrogate the error to find out what went wrong. You can also use

theErrobject to give the description of the error and concatenate it into your message so it

also says “Drive not ready.” You do this using theErrfunction. This will return the number

associated with the runtime error that happened. You can add this into the previous example

as follows:

Sub Test_Error()

On Error GoTo err_handler

temp = Dir("d:\*.*")

Exit Sub

err_handler:

92 Microsoft Access 2010 VBA Macro Programming