Solved

Error handling for SQL and DLookup Statements

Posted on 2004-10-15
8
164 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now