Error Handling

[Snowflake68]

I am new at error handling and am trying to incorporate a little into my code. The issue I have is that I need to stop code running if there is an error in either of the two subroutines after they are called.

I am trapping errors in the two sub routines and all is working perfectly there but how do I stop the code from continuing when an error is dealt with smoothly.

I have the some very basic error handling on the code below and further error handling on the two subroutines that this code calls.

Private Sub cmdPrintReport_Click()

On Error GoTo ErrorHandling

Dim strCheckNoOfRecords As Variant
Dim strPrint As Boolean

strCheckNoOfRecords = DLookup("[RowID]", "[DSPNote]")
strPrint = DLookup("[Print]", "LookupEmailAddress", "ID = 1")

    If strCheckNoOfRecords > 0 Then
    
            If strPrint = True Then           
               DoCmd.RunMacro "PrintReport"

            End If
' I want to stop the code running if I trap an error in either of these two subroutine 
        Call CreatePDF 
        Call SendEmailPDF
' I want to stop the code below from running if I get an error in the above routines.
        DoCmd.RunMacro "DSPStatusChange"
        
        'Reset RowID in DSP Notes Table
''            DoCmd.OpenQuery "DSPDeleteLines" ' no longer required since introducing the 'Reset' module code
            DoCmd.Close
            DoCmd.OpenForm "frmReset", , , , , acHidden
        
    Else
    
        MsgBox "There are no DSP Notes to Print." & Chr(13) & Chr(10) & Chr(10) & "Please enter at least 1 DSP Note and try again.", vbInformation, "Error No Data"
    
    End If

ErrorHandling:
        
    MsgBox Err.Description & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Something went wrong. Please contact your system administrator.", vbCritical, "Error #" & Err.Number
Exit Sub
   

End Sub

This is a sample of the error handling code I have on the two sub routines.

On Error GoTo ErrorHandling

'then all my code here 

ErrorHandling:
    If Err.Number = 2501 Then
            MsgBox Err.Description & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Please check the directory " & strPath & "\" & strDIR & " exists and then try again", vbCritical, "Error #" & Err.Number
    
    Else:   MsgBox Err.Description & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Something went wrong. Please contact your system administrator.", vbCritical, "Error #" & Err.Number
    
   
    End If

    Exit Sub

 

[isladogs]

The way I do error handling is as follows:

Private Sub YourProcName()

On Error GoTo Err_Handler

    'All code goes here

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in YourProcName procedure: " & Err.Description, vbCritical, "Application error"
    Resume Exit_Handler

End Sub

The exit handler section allows the code to exit gracefully if no errors occur.
Whereas the error handler section stops the code if an error does occur

At times there will be known errors that it will be OK to ignore
So for example a particular procedure may have these exceptions:

Err_Handler:
    If Err = 94 Or Err = 7874 Then Resume Next '94=invalid use of null, 7874 = table doesn't exist
    MsgBox "Error " & Err.Number & " in AnotherProcName procedure : " & Err.Description, vbOKOnly + vbCritical
    Resume Exit_Handler

Resume Next means the code ignores the error and continues where it left off

NOTE:
You can also get Access to 'read' the name of the procedure in use
To do so,
a) add the VBA reference "Microsoft Visual Basic for Applications Extensibility 5.3"
b) Declare a Public variable in a standard module

Public strProc As String

c) Change the Err_Handler section to:

Err_Handler:
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    MsgBox "Error " & Err.Number & " in " & strProc & " procedure : " & Err.Description, vbCritical, "Application error"
    Resume Exit_Handler

The complex strProc line of code extracts the procedure name
This means you can effectively use the same code in almost every procedure in your application

HTH

 

[pbaldy]

One option is to have the function return a Boolean, true if successful false if not. Then test that in your main code:

If CreatePDF = False Then Exit Sub

Normally you'd have an exit handler too:

http://www.baldyweb.com/ErrorTrap.htm

 

[Snowflake68]

Thanks for both of these suggestions. Hopefully I will find a solution from these.

 

[Snowflake68]

One option is to have the function return a Boolean, true if successful false if not. Then test that in your main code:

If CreatePDF = False Then Exit Sub

Normally you'd have an exit handler too:

http://www.baldyweb.com/ErrorTrap.htm

How do I get my procedure to return a Boolean value? I have tried Googling it but the results have just confused me even more.

Can you help me with the vba please?

This is one of my procedures that is being called

Public Sub CreatePDF()

On Error GoTo ErrorHandling

''' 'Create PDF Dispatch Note


    Dim strCustomer As String
    Dim strCustomerName As String
    Dim strCustNo As String
    Dim strRef As String
    Dim strDate As String
    Dim strShortText As String
    
    Dim strQueryName As String
    Dim strDIR As String
    Dim strPath As String
    Dim strFilePath As String
        
    strCustomer = CharDel(DLookup("[CustomerName]", "DispatchedReport"))
    strCustomerName = DLookup("[CustomerName]", "DispatchedReport")
    strCustNo = DLookup("[Customer]", "DispatchedReport")
    strRef = DLookup("[UID]", "LookupUID")
    strDate = Format(Date, "yyyymmdd")
    strShortText = "Manifest"
    
DoCmd.SetWarnings False

''' FileName concatenate the filename and path
    strQueryName = strCustomer & "_" & strShortText & "_" & strDate & "_" & strRef
    strDIR = DLookup("[DIR]", "LookupManifestDIR", "ID = 1")
    strPath = DLookup("[Filepath]", "LookupManifestDIR", "ID = 1")
    strFilePath = strPath & "\" & strDIR & "\" & strQueryName & ".pdf" 'Save PDF files

'''         Check to see if file already exists and then delete it
    CheckFileExists = Dir(strFilePath) <> vbNulString
    If CheckFileExists Then
'''        MsgBox "file exists and will be deleted" ' message used for testing purposes only
        Kill strFilePath
    Else
'''        MsgBox ("File does not exist") ' message used for testing purposes only
    End If

    DoCmd.CopyObject , strQueryName, acQuery, "DispatchedReport" ' DispatchedReport is the name of the query to copy
   
    DoCmd.OutputTo acOutputReport, "DispatchedReport", acFormatPDF, strFilePath
           
    DoCmd.DeleteObject acQuery, strQueryName

'''        MsgBox "Dispatch Note created", vbInformation, "Dispatch Note" 'message used for testing purposes only

DoCmd.SetWarnings True

Exit Sub


ErrorHandling:
    If Err.Number = 2501 Then
            MsgBox Err.Description & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Please check the directory " & strPath & "\" & strDIR & " exists and then try again", vbCritical, "Error #" & Err.Number

    Else:   MsgBox Err.Description & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Something went wrong. Please contact your system administrator.", vbCritical, "Error #" & Err.Number

    End If
    Exit Sub
End Sub

 

[pbaldy]

Try declaring it:

Public Sub CreatePDF() As Boolean

At the beginning set it False

CreatePDF = False

Right before the exit sub, where it will only get if it succeeds, set it True:

CreatePDF = True

 

[pbaldy]

Oh, and you'll need to make it a function rather than a sub.

 

[Snowflake68]

Try declaring it:

Public Sub CreatePDF() As Boolean

At the beginning set it False

CreatePDF = False

Right before the exit sub, where it will only get if it succeeds, set it True:

CreatePDF = True

Got this working perfectly, thank you so much. I just need to incorporate it into all my other routines after changing them to functions now.

 

[pbaldy]

Happy to help!

 

[Galaxiom]

Try declaring it:

Public Sub CreatePDF() As Boolean

At the beginning set it False

CreatePDF = False

Booleans instantiate as False so it is not necessary to explicitly do it.

 

[pbaldy]

I knew a declared variable did, wasn't sure the same happened with function declaration.