Error Handling (Access 2000)

Topic

New Reply

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’.

Reply | Quote

Replies

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.

Reply | Quote

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’…..

Thanks

Ian

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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’…..

Thanks

Ian

Reply | Quote

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.

Reply | Quote