Error Trap in VBA

[rajeevs]

Hi All
I have a click event in an Access form which basically does three subs.
They are like:

Private Sub Process_Click()
 
If MsgBox("Did you copy the data ?", vbCritical + vbYesNo, "Data") = vbNo Then
Exit Sub
Else
 
Process1
 
Process2
 
Process3
 
 
 
Me.lbl.Visible = False
DoCmd.Hourglass False
 
If IsNull(DLookup("NotinMaster", "lcmstr", "NotinMaster")) Then
DoCmd.OpenForm "FinalProcess"
Else
MsgBox "New codes found in data. Please update 'Codes' Table and process again", vbInformation + vbOKOnly, "New Codes"
DoCmd.OpenForm "lcadd"
Exit Sub
 
End If
 
End If
 
Me.lbl.Visible = False
DoCmd.Hourglass False
 
 
End Sub

I like to stop the code execution if there is an error in any of the subs and pop up a message box.
The process1 sub opens an excel template to write some data. So if error occurred then kill excel application as well.
Hope I explained well.

[twinnyfo]

rajeevs,

I always recommend adding error trapping/handling on all procedures within a project. The basics are as follows:

Private Sub ThisIsMySub()
On Error GoTo EH
 
    'Add all your Code here......
 
    Exit Sub
EH:
    MsgBox "There was an error ....!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Sub
End Sub

With this basic template, you can do all sorts of things. For example, if your Process1 is to open Excel and performs some functions there, in the error handling portion, just add code to close your Excel Application there. You could also have another call, which some prefer to have, which closes everything out. I.e.:

ExitMySub:
    xlSheet.Application.ActiveWorkbook.Close
    Set xlSheet = Nothing
    Set xlApp = Nothing
    Exit Sub

Then, at any point in your sub, if you want to exit your Sub and close everything (without an error), then you just add:

GoTo ExitMySub

Very simple, really. But it does take some skill to identify errors that will kill your project and just errors that will interrupt a Sub, but still allow you to continue.

Hope this hepps!

[rajeevs]

Thank you twinnyfo
You are so kind as always.
I will try and let you know the progress

[rajeevs]

Hi twinnyfo
Actually the error occurs always in process1. So I would like to stop the Process_Click() sub to stop and show a message to the user without proceeding to process2 and further. So could you tell me how can I put the error trap in Process_Click().

[twinnyfo]

Use the templates in Post #2 and incorporate those into your code.

Keep in mind that I use most of my error trapping in the development of my code so that I can avoid any errors ever popping up. There are some errors that one cannot avoid, such as trying to find a file that does not exist, but that can be prevented as well.

The whole point is to determine what the Error that is occurring, then, build code into your project to prevent any errors (if at all possible).

So, in your instance, find out the error that is being tripped in Process1. Then you must figure out why it is causing an error, then programmatically prevent the error from occurring.

[rajeevs]

Dear twinnyfo

I was trying the error trap in the process1 sub as well as in the button click event. Still nothing happens. How can we stop the code running further from Process1 in the click event Process_Click() if there is an error in the process1 sub. I need to stop the Process_Click() and get the warning message.

[twinnyfo]

You could have a class-specific global Boolean variable that is set to true on the event of any error. Then, if Process1 generates an error, it returns to the main sub. As soon as you return to the main sub, check the status of the variable. If it is true, then exit the sub.

Example:

Option Compare Database
Option Explicit
 
Private fErr as Boolean
 
Private Sub MainSub()
On Error GoTo EH
    Process1
    If fErr Then GoTo ExitSub
    Process2
    If fErr Then GoTo ExitSub
    Process2
    If fErr Then GoTo ExitSub
 
ExitSub:
    Exit Sub
 
EH:
    MsgBox "You had an error!" & vbcrlf & vbcrlf & _
        Err.Number & ": " & Err.Description
    Exit Sub
End Sub
 
Private Sub Process1
On Error GoTo EH:
    'Your Code
    Exit Sub
EH:
    MsgBox "You had an error!" & vbcrlf & vbcrlf & _
        Err.Number & ": " & Err.Description
    Exit Sub
End Sub

Hope this hepps!

[rajeevs]

Thank you twinnyfo
I have tried it and it is working fine now. Sorry for the delayed reply because I am still stuck with the other issue posted in a different thread. I will post the progress on the other issue in that thread

[twinnyfo]

I just noticed something in my code. A minor tweak to the Process Subs:

Private Sub Process1
On Error GoTo EH:
    'Your Code
    Exit Sub
EH:
    MsgBox "You had an error!" & vbcrlf & vbcrlf & _
        Err.Number & ": " & Err.Description
    fErr = True
    Exit Sub
End Sub

This sets the Boolean value of the Flag we created whenever there is an Error.

Also, add:

fErr = False

To the MainSub

[NeoPa]

An alternative way to detect whether or not a procedure has fallen over an error is to use a Function procedure. That way you can pass back the information however you like. A Boolean can say whether or not, while a Long can return an error number or a String can return a message.

[twinnyfo]

Ahh, nice touch, NeoPa. Again, I am reminded of another useful trick!

[rajeevs]

Thank you twinnyfo for the quick response. I will add the lines as mentioned. But the code is worked ok while tested.