Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Handling (Access 2000)

    Dear All

    I'm finding my error handling code does not catch certain problems, one being if the email is 'cancelled' from the Outlook window during a SendObject action.

    My current code is simple, it's based on the standard Error Handler stuff written by Access control wizards, here's an example: -

    ExitHandler:
    Set DbS = Nothing
    Set VendorEmailAddys = Nothing
    Set EmailAddyDef = Nothing
    Set VendorEmailDef = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler

    I seem to have got the idea from some where I can add more error handling ability specific to the error number on an If ...... Then loop basis. Do I need to do this, or am I missing something simple from the code? If it counts my on 'error goto ErrHandler' line is immediately after the 'Private Sub' line, do I need to move this, even to add additional error handling for each If ...... Then or Do loop??

    I can make sure the user doesn't harm the code by using an mde, but don't want the user inconvenienced, or able to cancel without the database 'knowing'.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Handling (Access 2000)

    1. In order to be able to handle specific errors, you must know the error numbers. During testing, replace MsgBox Err.Description by

    MsgBox "Error# " & Err.Number & " occurred, with message:" & vbCrLf & Err.Description

    Each time an error occurs, write down the number (and description if you like). When you think you have a good idea of which errors can occur, replace it by code based on the following model. You will have to fill in the details yourself, based on what you wrote down and what you want to do with it; the error numbers I used are just examples.

    Select Case Err.Number
    Case 2165 'Cannot hide the control that has the focus
    ' Code to execute if you try to hide the active control goes here
    Case 2501 ' action cancelled by user
    ' Code to execute for error 2501 goes here
    Case Else ' All other errors
    MsgBox "Error# " & Err.Number & " occurred, with message:" & vbCrLf & Err.Description
    End Select

    2. You wrote that canceling an e-mail is not caught by the error handler. If an action does not cause an error, you can't catch it with an error handler. You may have to look for alternative checks, for example testing if an object is Nothing. I don't know enough about your code to give advice about that.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handling (Access 2000)

    Hans

    Thanks, the Select Case is some thing I've been looking at and will prove useful. I don't think I explained myself well though. The problem is this: - canceling an email from the Outlook window does give me an error message, error 2501, it is not 'controlled' by my error handling code though. Rather than giving me the usual pop up box with the error description in it I get the full VBA screen, with the de###### error message, as though there were no error handler in my code at all. I can post the code that's causing the problem if this explanation doesn't help.

    I was wondering if placing an error handler in the actual loop was the way forwards?

    During testing I usually just ' out the error handling code as this gives me what I feel is the best of all worlds for tracking the error down, the error message, the error number and the VBA window with the line of code highlighted, so I know the error is before this line, even if not IN this line. If there is a better way then this, please tell me. Think I'll use the code you suggest for my actual implementation though, that way I get even more information from the user when they find my 'undocumented features'..... [img]/forums/images/smilies/biggrin.gif[/img]

    Thanks

    Ian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Handling (Access 2000)

    If error 2501 occurs "unhandled" even with error handling in place, you may have put the error handling in the wrong place, or not in enough places. Check carefully that the instruction where it occurs is within the scope (reach) of an error handler.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Handling (Access 2000)

    You have to put a conditional branch into your error handling to tell it to ignore Error 2501, which is normally a bogus message anyhow. When you cancel the email from the Outlook window, Access can't continue its instructions for handling a message, so an Error 2501 occurs.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handling (Access 2000)

    Charlotte

    OK, sounds like that's my problem, where do I put the 'conditional branch' and what sort of format should it be in?

    I've tried adding an 'if Err.Number = 2501 then' do something interesting line to my ErrHandler: code, it was ignored. I assume I need to put the instruction in my code somewhere else. The SendObject instruction is used in a nest of If .... Then statements that determines several points before carrying out the send object action.

    I've been going through the help in Access and various books, but seem to be particularly dense with the attempt to solve this myself....

    Thanks for the help so far.

    Ian

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Handling (Access 2000)

    Do you happen to have a line On Error GoTo 0 somewhere after On Error GoTo ErrHandler in the same routine? That would nullify the error handling.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Handling (Access 2000)

    Hans gave you an example of a Select Case. Otherwise, you could just do this:

    ErrHandler:
    If Err.Number = 2501 Then
    ' ignore this error
    Else
    MsgBox Err.Description
    End If
    Resume ExitHandler
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handling (Access 2000)

    Sorry all, no on error goto 0 line, this I found out about for the first time while trying to work this out, an interesting option.

    I've tried adding an if ........ then to my error handling code, it's ignored the same as the standard code. I'm wondering if the problem is as simple as the fact that Access VBA can't handle problems generated by external programmes, in which case I may need to use automatiomn, unless I can work out another way of stoppin my user from causing the error.

    In case it is sometjhing really stupid here's my code, apologies for the state of it, I'm new to this and tend to get code working then go back and tidy it up so it looks right.Private Sub SaveRecBtn_Click()
    On Error GoTo ErrHandler

    'turn all windows warning messages off
    DoCmd.SetWarnings False

    Dim DbS As DAO.Database
    Dim EmailAddys As DAO.Recordset
    Dim VendorEmailAddys As DAO.Recordset
    Dim strEmailAddys As String
    Dim strEmailNames As String
    Dim strVendorEmailAddys As String
    Dim strVendorNames As String
    Dim Response As String
    Dim strEmailQry As String
    Dim EmailAddyDef As DAO.QueryDef
    Dim VendorEmailDef As DAO.QueryDef


    Set DbS = CurrentDb
    Set EmailAddyDef = DbS.QueryDefs("internalemailaddyqry")
    Set VendorEmailDef = DbS.QueryDefs("emailcontactdataqry")
    EmailAddyDef.Parameters("[Forms]![PrimaryDataTableInputFrm]![LineNumber]") = Me.LineNumber
    EmailAddyDef.Parameters("[Forms]![PrimaryDataTableInputFrm]![OrdNumber]") = Me.OrdNumber
    VendorEmailDef.Parameters("[Forms]![PrimaryDataTableInputFrm]![VendorNumber]") = Me.VendorNumber
    Set VendorEmailAddys = VendorEmailDef.OpenRecordset '("EmailContactDataQry")
    Set EmailAddys = EmailAddyDef.OpenRecordset '("InternalEmailAddyQry")

    'create the email address list for internal contacts.
    Do While Not EmailAddys.EOF
    If Not IsNull(EmailAddys!InternalContactEmail) Then
    strEmailAddys = strEmailAddys & EmailAddys!InternalContactEmail & ";"
    strEmailNames = strEmailNames & EmailAddys!InternalContactName & ","
    End If
    EmailAddys.MoveNext
    Loop

    'create the email list for the Vendor contacts.
    Do While Not VendorEmailAddys.EOF
    If Not IsNull(VendorEmailAddys!VendContEmail) Then
    strVendorEmailAddys = strVendorEmailAddys & VendorEmailAddys!VendContEmail & ";"
    strVendorNames = strVendorNames & VendorEmailAddys!VendContName & ","
    End If
    VendorEmailAddys.MoveNext
    Loop


    If IsNull(Me.SupplierAdviceNoteNumber) Then
    DoCmd.GoToControl "SupplierAdviceNoteNumber"
    MsgBox "Please enter the Advice / Delivery note number", , "Data missing error"
    Else
    If IsNull(Me.DetailRejectReason) Then
    DoCmd.GoToControl "DetailRejectReason"
    MsgBox "Please enter as much detail about the reject reason as possible in the Detail Reject Reason section", , "Data missing error"
    Else
    If IsNull(Me.ReasonRejected) Then
    DoCmd.GoToControl "combo24"
    MsgBox "Please select the correct reject code from the reason rejected drop down", , "Data missing error"
    Else
    If Me.QuantityRejected = 0 Then
    MsgBox "Quantity Rejected can not equal Zero (0)", , "Data input Error"
    Else
    If IsNull(Me.Combo22) Then
    Response = MsgBox("Is there a Child Part number for this item?", vbYesNo, "Critical Data Confirmation")
    If Response = vbYes Then
    Me.Combo22.SetFocus
    Else
    Me.DetailRejectReason.SetFocus
    End If
    End If
    'save the record and email it to the relevant people
    If IsNull(Me.Combo22) Then
    DoCmd.RunCommand acCmdSaveRecord
    'send the email to the internal contact list.
    DoCmd.SendObject acSendQuery, "RejectInformationQry", acFormatXLS, strEmailAddys, , "ian.peel@craneflow.com", "Defective Product Received", "F.A.O. " & strEmailNames & " Hello the attachement has details on product destined for Production that has been rejected. The reason is " & Me.DetailRejectReason & ". Please take the appropriate action"
    'send the email to the Vendor contact list
    If IsEmpty(strVendorEmailAddys) = False Then
    MsgBox "There is no email on the system for this supplier, please FAX the reject note to them", vbOKOnly, "Email address missing"
    Else
    DoCmd.SendObject acSendQuery, "RejectInformationQry", acFormatXLS, "ian.peel@craneflow.com", , , "Defective Product Supplied to Crane Process Flow Technologies Ltd.", "F.A.O. " & strVendorEmailAddys & " Crane Process Flow Technologies Ltd have rejected your product as described in teh attachment. Please respond to the Product Q.A. Manager within 24 hours of reciept of this message. Upon resolution of this issue you will be expected to make arrangements to collect the rejected product."
    End If
    DoCmd.SendObject acSendQuery, "RejectInformationQry", acFormatXLS, "ian.peel@craneflow.com", , , "Defective Product Supplied to Crane Process Flow Technologies Ltd.", "F.A.O. " & strVendorEmailAddys & " Crane Process Flow Technologies Ltd have rejected your product as described in teh attachment. Please respond to the Product Q.A. Manager within 24 hours of reciept of this message."
    'DoCmd.Close acForm, "primarydatatableinputfrm"
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "ChildPartDataXferQry", acViewNormal, acAdd
    'send email to the internal contacts list
    DoCmd.SendObject acSendQuery, "RejectInformationQry", acFormatXLS, strEmailAddys, , "ian.peel@craneflow.com", "Defective Product Received", "F.A.O. " & strEmailNames & " Hello the attachement has details on product destined for Production that has been rejected. The reason is " & Me.DetailRejectReason & ". Please take the appropriate action"
    'send email to the Vendor contact list
    If IsEmpty(strVendorEmailAddys) = False Then
    MsgBox "There is no email on the system for this supplier, please FAX the reject note to them", vbOKOnly, "Email address missing"
    Else
    DoCmd.SendObject acSendQuery, "RejectInformationQry", acFormatXLS, "ian.peel@craneflow.com", , , "Defective Product Supplied to Crane Process Flow Technologies Ltd.", "F.A.O. " & strVendorEmailAddys & " Crane Process Flow Technologies Ltd have rejected your product as described in teh attachment. Please respond to the Product Q.A. Manager within 24 hours of reciept of this message."
    End If
    End If
    End If
    End If
    End If
    End If


    ExitHandler:
    Set DbS = Nothing
    Set VendorEmailAddys = Nothing
    Set EmailAddyDef = Nothing
    Set VendorEmailDef = Nothing
    Exit Sub

    ErrHandler:
    If Err.Number = 2501 Then
    MsgBox "Email not sent, cancelled by user", vbExclamation, "User Interaction Warning"
    Resume ExitHandler
    Else
    MsgBox Err.Description
    Resume ExitHandler
    End If


    DoCmd.SetWarnings True

    End Sub

    You can probably see why I didn't post it with the original question, it's fairly long...... As you can see the if ..... then is in my ErrHandler: section, is this the right place for it?

    Thanks

    Ian

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Handling (Access 2000)

    It's the right place for it, but I see one glaring problem that needs to be fixed, although it probably won't help you track the source of the error. You have added a DoCmd.SetWarnings True in a place where it will never be executed. That means that you turn the warnings off but you never turn them on again. As a result, you don't really know whether Access is trying to tell you something you need to know after you leave this routine. Move the SetWarnings True to the exit handler, where it will always execute at least.

    SendObject is prone to problems like this. I think your idea about going to automation is the wisest approach.
    Charlotte

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Handling (Access 2000)

    just BTW you misspelled "the" in the text of the message . It is "teh"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •