Last
month we created an error handling routine that
returned a nonsense value from a function in the
event of a problem:
Function CubitsToInches(dblCubits As Double,
strPerson As String) As Double
Dim dblInchesPerCubit As Double
On Error GoTo PROC_ERR
dblInchesPerCubit = DLookup
("CubitLen", "tblCubit", "Person = '" & strPerson & "'")
CubitsToInches = dblInchesPerCubit *
dblCubits
PROC_EXIT:
Exit Function
PROC_ERR:
CubitsToInches = 0
GoTo PROC_EXIT
End Function
The PROC_ERR section is called when our function
encounters a problem.
If we make just a couple of changes to the above
routine, we can radically alter our program to stop
processing, alert the user, and invite him/her to fix
the problem.
The revised function looks like this:
Function CubitsToInches(dblCubits As Double,
strPerson As String) As Double
Dim dblInchesPerCubit As Double
On Error GoTo PROC_ERR
dblInchesPerCubit = DLookup
("CubitLen", "tblCubit", "Person = '" & strPerson & "'")
CubitsToInches = dblInchesPerCubit *
dblCubits
PROC_EXIT:
Exit Function
PROC_ERR:
MsgBox "There is no person in the
Cubits Table called " & strPerson &”. Please check
the Cubits Table.”
End
GoTo PROC_EXIT
End Function
Our first change is that we've removed the line:
CubitsToInches = 0
This was the line that designated the nonsense value
that was returned in the event of an error.
We've replaced it with:
MsgBox "There is no person in the Cubits table
called " & strPerson & ". Please check the Cubits
Table."
With these changes to the code, we’re directing
Access to present the user with a message box (a
box that pops up on the screen to give some
information to the user) in the event of a problem, as
opposed to returning the nonsense value of zero.
Message boxes can get reasonably complex, but here
we're using a plain vanilla approach.
This is how a message box works: the text in quotes
following the term "msgbox " pops up on the user's
screen. We've added just a couple of elements
following the closing quotes
- The ampersand symbol "&"
- Our variable: strPerson
The ampersand is a concatenation device (a symbol
that joins text strings). Our strPerson variable is
passed to the function and used to look up that
person's cubit length in our Cubit table. With this
revision, if the person is not in the table our
application will tell the user that the person is
missing, and invite them to check the table.
The next line
End
stops all processing. It does nothing fancy---it
simply ends the function--- it doesn't pass control to
the calling function; it simply returns to the user
interface.
Now we've strengthened our error handling to not
only detect the error, but also to explicitly warn the
user that there's a problem and invite them to fix the
mistake.