On Error Handling

[mikejaytlabustro]

Hello access-programmers! As I moved on, vba codes seems so interesting. I have no formal education or training in microsoft access, just did self-study. And I'm so thankful that I've found and joined this site.

*** Here's my VBA code ***

Private Sub CommandButton_Click()

On Error GoTo ErrorHandler1
My codes here...
ErrorHandler1: MsgBox "Error on Procedure No. 1"
Exit Sub

On Error GoTo ErrorHandler2
My codes here...
ErrorHandler2: MsgBox "Error on Procedure No. 2"
Exit Sub

End Sub

==============================================

What I wanted to do is proceed to the Next Procedure and so on, once an error has occurred. In this code, if Procedure 1 encountered an error, it just stops there. I tried other other ways like adding Resume Next and the result, MsgBox in Errorhandler1 keeps on coming out (endlessly). Hope that I made this very clear. Thank you!

 

[Galaxiom]

Exit Sub does just what it says so processing cannot get to anywhere beyond that line after the first error occurs

You could take the line out but it would still be an unconventional way to deal with errors.

 

[JHB]

...Hope that I made this very clear.

Sorry, but not really.
I think you are mixing something up, how error handling works.
An error handling is not like an If -Then structure, where you can exclude some code line from execution when some conditioner is meet.

On Error GoTo ErrorHandler1

The above tells the compiler to jump to the place in the code where there is a "label" with "the name" ErrorHandler1 if some error occurs and continue the execution from there.
If no error occurs in the code, all code lines are executed also the code line in the error handling part, (if there is no "Exit Sub/Function" before the error handling part).

[B]Private Sub CommandButton_Click() [/B]

On Error GoTo ErrorHandler1
My codes here...
ErrorHandler1: MsgBox "Error on Procedure No. 1"
[B]Exit Sub[/B]

On Error GoTo ErrorHandler2
My codes here...
ErrorHandler2: MsgBox "Error on Procedure No. 2"
[B]Exit Sub[/B]

[B]End Sub[/B]

So in you example:
If an error occurs in your code, jump to ErrorHandler1: and show and messagebox with the text "Error on Procedure No. 1" and then Exit the sub, (because the next code line is "Exit Sub")
If no error occurs in your code, all code are executed and at last show a messagebox with the text "Error on Procedure No. 1" and then Exit the sub, (because the next code line is "Exit Sub").
So you'll never get to the code line "On Error GoTo ErrorHandler2", because you exit the sub just before.

 

[mikejaytlabustro]

The actual application of my code is to copy and paste various access database from different locations into one back up folder. Kindly see attached screenshot. I made this so that i can automate file back up in Task Scheduler . I just want to figure out which from the 12 pairs of File paths is/are incorrect or causes the error. What should be the right code for this one?
===============================================

Private Sub CommandButton_Click()

On Error GoTo ErrorHandler1

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile [Source1], [Destination1] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
Set fso = Nothing

ErrorHandler1: MsgBox "Error on Source 1"

Exit Sub


On Error GoTo ErrorHandler2

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile [Source2], [Destination2] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
Set fso = Nothing

ErrorHandler2: MsgBox "Error on Source 2"

Exit Sub

And so on.....

End Sub

 

[JHB]

What should be the right code for this one?

It could be something like the below, (not tested):

Private Sub CommandButton_Click()

On Error GoTo ErrorHandler1
[COLOR=Red]Dim ErrorMessage as String, CodeBlock as String [/COLOR]
[COLOR=Red]CodeBlock="1"[/COLOR]
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile [Source1], [Destination1] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
Set fso = Nothing

[COLOR=Red]CodeBlock="2"[/COLOR]
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile [Source2], [Destination2] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
Set fso = Nothing

[COLOR=Red]CodeBlock="3"[/COLOR]
Set fso = CreateObject("Scripting.FileSystemObject")
...
And so on.....

[COLOR=Red]If  ErrorMessage<>"" then
Msgbox("Error in codeblok =" &  ErrorMessage)
Endif
Exit Sub[/COLOR]

ErrorHandler1: 
[COLOR=Red]ErrorMessage = ErrorMessage & CodeBlock & ", "
Resume Next[/COLOR]
End Sub

 

[MarkK]

I would loop, so you just need one error handler, one FileSystemObject, and put your sources and destinations in an array or a collection or a recordset or something you can enumerate.

Private Sub CommandButton_Click()
On Error GoTo handler
    Dim i As Integer
    Dim vData(1)    [COLOR="Green"]'an array of arrays[/COLOR]

    vData(0) = Array([Source1], [Source2]) [COLOR="Green"]'source array in array(0)[/COLOR]
    vData(1) = Array([Destination1], [Destination2]) [COLOR="Green"]'destination array in array(1)[/COLOR]

    With CreateObject("Scripting.FileSystemObject")
        For i = 0 To 1
            .CopyFile vData(0)(i), vData(1)(i) & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
        Next
    End With
    Exit Sub
    
handler:
    MsgBox err.Description & " in loop " & i
    Resume Next
End Sub

 

[mikejaytlabustro]

It could be something like the below, (not tested):

It works! I reconstruct my vba code to make it shorter/simple and this is it;
===============================================

Private Sub Command_Click()
Dim CodeBlock As Integer
On Error GoTo ErrorHandler

Set fso = CreateObject("Scripting.FileSystemObject")
CodeBlock = 1
fso.CopyFile [Source1], [Destination1] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
CodeBlock = 2
fso.CopyFile [Source2], [Destination2] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
CodeBlock = 3
fso.CopyFile [Source3], [Destination3] & " - " & Format(Now(), "yyyy-mm-dd hh\.nn\.ss AM/PM") & ".accdb"
Set fso = Nothing
Exit Sub

ErrorHandler: MsgBox "Error in CodeBlock " & [CodeBlock]
Resume Next

End Sub

===============================================

I intentionally alter File Paths in different CodeBlocks, and Errorhandler message got it right. I think problem was solved.

Thank you and more blessings to come!