Solved

Error handling for SQL and DLookup Statements

Posted on 2004-10-15
8
169 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

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)

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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