Error handling for SQL and DLookup Statements
Hello again Experts. I module which contains several functions. These functions return the requiered e-mail addresses using a SQL statement or a DLookup function. These functions are called by different forms, whenever there is the need of emailing someone. Where would be the best location to place some error-handling code? The error # I get is 94, and when I press debug, it takes me to the function, so my guess is to place the code in the functions. Thanks for your help!
Jokra_the_Barbarian
Always put the error handling in the procedure or function, either or create a public error handler for the module and call it from within the function.
ASKER
OK, so let's briefly use my example: I have a couple of global variables pertaining to a procudure called sendmail. Before sending the email, I call the corresponding function to get the email address. In case there is no e-mail address obtained with the SQL or DLookup function, I would like the code to send a message to the user and cancel the sendmail procedure, but I want the rest of the code to continue running. How can I do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have my code set up a different way, not sure if this is the best. I was given this project and had to learn access a few months ago, and haven't set up error handlers yet on anything, except when you run the wizard when creating command buttons, but other than that... Anyways, I'm not much of a coder yet. (Someday... :-)
This is how I call the sendMail function.
Call sendMail(emailAddress, emailAddressCc, Body, Subject)
This function is called when a user presses a button, and depending on their response (accept, reject, close), it sends out an email to someone.
before this code, I get the email addresses. For example...
emailAddress = DEPTMGRmail
DEPTMGRmail looks like this:
Public Function DEPTMGRmail() As String
Dim strSQL As String
strSQL = "SELECT tblDept.DeptEmail "
strSQL = strSQL & "FROM tblDept INNER JOIN tblAdjustments "
strSQL = strSQL & "ON tblDept.DeptName = tblAdjustments.WhySubmit "
strSQL = strSQL & "WHERE tblDept.ContactPosition = 'Pre-Press Manager'"
DEPTMGRmail = CurrentDb.OpenRecordset(st
End Function
If there is not an email address on tblDept.DeptEmail, it sends back a run-time error '94'.
This is how I call the sendMail function.
Call sendMail(emailAddress, emailAddressCc, Body, Subject)
This function is called when a user presses a button, and depending on their response (accept, reject, close), it sends out an email to someone.
before this code, I get the email addresses. For example...
emailAddress = DEPTMGRmail
DEPTMGRmail looks like this:
Public Function DEPTMGRmail() As String
Dim strSQL As String
strSQL = "SELECT tblDept.DeptEmail "
strSQL = strSQL & "FROM tblDept INNER JOIN tblAdjustments "
strSQL = strSQL & "ON tblDept.DeptName = tblAdjustments.WhySubmit "
strSQL = strSQL & "WHERE tblDept.ContactPosition = 'Pre-Press Manager'"
DEPTMGRmail = CurrentDb.OpenRecordset(st
End Function
If there is not an email address on tblDept.DeptEmail, it sends back a run-time error '94'.
Trap the error, return some value to the function, ie "None"
Public Function DEPTMGRmail() As String
On Error GoTo errorHandler
Dim strSQL As String
DEPTMGRmail = CurrentDb.OpenRecordset(st
exitSub:
Exit Sub
errorHandler:
Select Case Err.Number
Case 94
DEPTMGRmail = "None"
Resume exitSub
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume exitSub
End Select
Public Function DEPTMGRmail() As String
On Error GoTo errorHandler
Dim strSQL As String
DEPTMGRmail = CurrentDb.OpenRecordset(st
exitSub:
Exit Sub
errorHandler:
Select Case Err.Number
Case 94
DEPTMGRmail = "None"
Resume exitSub
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume exitSub
End Select
ASKER
Can the errorHandler be placed as a public sub or function?
Yes. Check out this thread: http://www.utteraccess.com/forums/access/access21708.html
This one's a little old, but still applicable: http://www.microsoft.com/officedev/articles/movs204.htm
This one's a little old, but still applicable: http://www.microsoft.com/officedev/articles/movs204.htm
I like the concept of a centralized error handler, I've just never found a routine that works well. Most subs/functions don't require error logging or specalized handling; the chances of errors on the vast majority of them are slim to none, thus calling external routines to deal with errors is, IMO, a waste of resources. The only errors I pass over to a secondary routine are those involving undeclared or unopened objects (my routine determines what type of object and then opens/resets it, depending on the state), but for my money in procedure error handlers are much easier to debug and handle.
mstools (www.mztools.com) is a free Access addin that will automatically add error handlers to all your subs and routines. There are other addins which do this (I use the one from www.codecrafter.com since this addin does a few more things).
mstools (www.mztools.com) is a free Access addin that will automatically add error handlers to all your subs and routines. There are other addins which do this (I use the one from www.codecrafter.com since this addin does a few more things).