Solved

Error handling for SQL and DLookup Statements

Posted on 2004-10-15
8
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 85
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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