[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error handling for SQL and DLookup Statements

Posted on 2004-10-15
8
Medium Priority
?
179 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 1200 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Technology Partners: 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
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…
Suggested Courses

830 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