Access VBA: Is it possible to reset error handling?

Question

I am using in the first part of my program

On Error GoTo start

Suppose in my second part I am again using

On Error Resume Next

This second error trap will not get activated as the first one will still be active. Is there any way to de-activate the first error handler after it has been used?

Set objexcel = CreateObject("excel.Application")
                     objexcel.Visible = True
                     On Error GoTo Openwb
                     wbExists = False
                     Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
                     Set objSht = wbexcel.Worksheets("Sheet1")
                     objSht.Activate
                     wbExists = True
Openwb:
                     
                     On Error GoTo 0
                     If Not wbExists Then
                     objexcel.Workbooks.Add
                     Set wbexcel = objexcel.ActiveWorkbook
                     Set objSht = wbexcel.Worksheets("Sheet1")

                     End If
                     
                     On Error GoTo 0
                                         
Set db = DBEngine.opendatabase("C:\book.mdb")
Set rs = db.OpenRecordset("records")

Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection


For Each tdf In CurrentDb.TableDefs
   
   If Left(tdf.Name, 4) <> "MSys" Then
        rs.MoveFirst
        strsql = "SELECT * From [" & tdf.Name & "] WHERE s=15 "

        Do While Not rs.EOF
            On Error Resume Next
            
            rs2.Open strsql 

Upon execution of the last statement I want to ignore the error and move on to the next table but error handling does not seem to work.

Interrogation

Answer 1

It is nearly always better to avoid errors, rather than handling them. For example:

Set objexcel = CreateObject("excel.Application")
objexcel.Visible = True

'On Error GoTo Openwb '
'wbExists = False '

If Dir("C:\REPORT3.xls") = "" Then
    objexcel.Workbooks.Add
    Set wbexcel = objexcel.ActiveWorkbook
    Set objSht = wbexcel.Worksheets("Sheet1")
Else
    Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
    Set objSht = wbexcel.Worksheets("Sheet1")
End If

objSht.Activate
'wbExists = True '

Dervish

Thanks that did the job. But as a doubt isnt it possible anyway to cancel out an error handling after it is used before proceeding further in the program. – Interrogation

Quibble: in VBA it's more efficient when testing for a zero-length string to usee vbNullString instead of "" because that Access constant already has its memory allocated. It doesn't matter in this constant, but it's good to get in the habit so you always use vbNullString inside loops. – Sot

@Interrogation : "On Error GoTo 0" does exactly that. It returns VBA to its usual error handling. It doesn't mean "On Error GoTo Start" which i think your question implies. – Alcine

Your comment is true, but there are some areas where this can't be done. Anyway where one can avoid this "Error" handling one should try to use it – Know

This is technically correct but doesn't answer the question. It's a simple question, really: "is there a way to reset error handling" – Heiress

Answer 2

On error goto 0 give hand to visual basic for error treatment (in general message box)

On error goto label will redirect your code to label:

On error resume next will ignore the error and continue

Resume next redirect the code to the next line after the error is raised

it means that combinations of instructions such as

    On Error goto 0
    ...
    On Error goto 0

do not make sense

And if you want to redirect an "on error" instruction you'll have to do it this way:

    Do While Not rs.EOF
        
        On Error Resume Next
        rs2.Open strsql
        On error Goto 0

        rs2.moveNext

    Loop

If you want to redirect an error to a label (for treatment or whatever) and then go back to the code where the error occured, you have to write something like:

    On error goto label
    ...
    ...
    On error goto 0
    exit sub (or function)

    label:
    ....
    resume next
    end function

But I really advise you to be more rigorous on your error management. You should first be able to do something like that:

    Set objexcel = CreateObject("excel.Application")
    objexcel.Visible = True

    On Error GoTo error_Treatment
    wbExists = False
    Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
    Set objSht = wbexcel.Worksheets("Sheet1")
    objSht.Activate
    wbExists = True
    On error GoTo 0

    Set db = DBEngine.opendatabase("C:\book.mdb")
    Set rs = db.OpenRecordset("records")

    Set rs2 = CreateObject("ADODB.Recordset")
    rs2.ActiveConnection = CurrentProject.Connection

    For Each tdf In CurrentDb.TableDefs
        ....
        'there are a number of potential errors here in your code'
        'you should make sure that rs2 is closed before reopening it with a new instruction'
        'etc.'
    Next tdf

    Exit sub

    error_treatment:
    SELECT Case err.number
       Case **** '(the err.number raised when the file is not found)'
           objexcel.Workbooks.Add
           Set wbexcel = objexcel.ActiveWorkbook
           Set objSht = wbexcel.Worksheets("Sheet1")
           Resume next 'go back to the code'
       Case **** '(the recordset cannot be opened)'
           ....
           ....
           Resume next 'go back to the code'
       Case **** '(whatever other error to treat)'
           ....
           ....
           Resume next 'go back to the code'
       Case Else
           debug.print err.number, err.description '(check if .description is a property of the error object)'
           'your error will be displayed in the immediate windows of VBA.' 
           'You can understand it and correct your code until it runs'
       End select
    End sub

The next step will be to anticipate the errors in your code so that the err object will not be raised. You can for example write a generic function like this one:

    Public function fileExists (myFileName) as Boolean

You can then take advantage of this function in your code by testing the existence of your xls file:

    if fileExists("C:\REPORT3.xls") Then
        Set wbexcel = objexcel.Workbooks.Open("C:\REPORT3.xls")
    Else
       objexcel.Workbooks.Add
       Set wbexcel = objexcel.ActiveWorkbook
    Endif        
    Set objSht = wbexcel.Worksheets("Sheet1")
    objSht.Activate

You do not need your wbExist variable anymore...

In the same way, you should anticipate the case where your recordset has no records. Writing down rs.MoveFirst before testing it could raise an error. You should then write

    If rs.EOF and rs.BOF then
    Else
        rs.moveFirst
        Do while not rs.EOF
             rs.moveNext
        Loop
    End If

Bigeye

Answer 3

You need to clear the error. Try putting this code in:

If Err.Number > 0 Then
    Err.Clear
End If

You can also use Err.Number to handle specific error cases.

Ptolemy

This doesnt seem to wwork either. THis code works if the first part of excel is put in a seperate sub but i want it like this as of now. – Interrogation

I don't think it's ever a good idea to just discard all errors. What if you discard an error you're not expecting? – Sot

David, good point. That is why I said you can use Err.Number to handle specific error cases. Rather than doing a generic clear all, just clear if the error is one you were expecting. – Ptolemy

Answer 4

try

On Error Goto 0

For further help look here: http://msdn.microsoft.com/en-us/library/bb258159.aspx

Rile

Could you post your source code here? Maybe you are using the on error statements incorrectly. – Rile

Answer 5

There is a difference between an answer and a solution. Sometimes we just need an answer, warnings appreciated, and let us learn by experience that it is not actually a good solution. That being said, I found this:

You need to use On Error GoTo -1 or Err.Clear to reset error trapping.

Check this answer I posted a few months ago for a more detailed explanation.

Electroform