Solved

Error handling for SQL and DLookup Statements

Posted on 2004-10-15
8
167 Views
Last Modified: 2008-03-04
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!
0
Comment
Question by:horalia
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12324229
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.
0
 

Author Comment

by:horalia
ID: 12324261
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?
0
 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 300 total points
ID: 12324326
Within your getEmailAddress function, check for no returned records or recordcount = 0. If this is true, set the getEmailAddress function to some string, say "NoRecs".

From your procedure you would then write something like this:
  If getEmailAddress = "NoRecs" then
          (send mail to user)
          exit sub
  end if

Without seeing your code, I'm not sure where you are calling the procedures from, but this might give you some ideas.

       
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:horalia
ID: 12324382
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(strSQL).Fields(0)
End Function

If there is not an email address on tblDept.DeptEmail, it sends back a run-time error '94'.

0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12324485
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(strSQL).Fields(0)

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


   


0
 

Author Comment

by:horalia
ID: 12324613
Can the errorHandler be placed as a public sub or function?
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 12325627
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


0
 
LVL 84
ID: 12326709
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).
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 5 53
Access on Mouse move 5 30
Should I keep recordsets open? 3 23
Need help constructing a conditional update query 16 40
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question