Solved

MS Access: On Error GoTo not working

Posted on 2011-09-14
8
503 Views
Last Modified: 2012-05-12
Access 11

Have a Public Sub in my Main module that is designed to read field descriptions from fields in one database and copy them over to field descriptions in the local database.  In it, I use On Error GoTo to handle the errors (mainly intended for a table or the description property for a field not being found).  The compiler seems to be totally ignoring the On Error GoTo directives.  It breaks the code and displays the error as if there was no On Error there, at all.

I've used breaks to step through the code one line at a time.  It's never branching to the named line following GoTo, even though it's immediately before the line raising the error.

I've checked to be sure that "Break on All Errors" is not selected in the VBA editor (Tools -> Options -> General).  It's set to "Break on Unhandled Errors".

Note that NONE of the On Error statements in this sub appear to be working.  I've tried both calling it from the Immediate window as well as making it an OnClick sub on a form.  Same results.

Any ideas?

Thanks,
Bruce
Public Sub LoadDescriptions()
On Error GoTo ShowError

' Reads field descriptions from one database and writes them to the next.
' List of tables to update comes from ExportList.

'From top of Main module:
'Public dbLinked As DAO.Database
'Public dbLocal As DAO.Database

'Public rstLinked As DAO.Recordset
'Public rstLocal As DAO.Recordset

'Public tblsLinked As TableDefs
'Public tblsLocal As TableDefs

'Public tdfLinked As TableDef
'Public tdfLocal As TableDef
'Public tdfNew As TableDef
'Public fld As Field

Dim rstList As DAO.Recordset
Dim xList As Integer    'For iterating ExportList.
Dim yList As Integer
Dim tblName As String

Dim xTbls As Integer    'For iterating Fields in source table.
Dim yTbls As Integer
Dim fldName As String
Dim fldDesc As String
Dim strConnect As String
Dim dbPath As String
Dim flds As Fields

Dim tstLocal As String
Dim tstLinked As String

Dim prp As Property


dbPath = "C:\Documents and Settings\Bruce\Desktop\New AIS\Backups\NewAIS 3-22-11.accdb"
strConnect = ";DATABASE=" & dbPath

Set rstList = CurrentDb().OpenRecordset("ExportList")
rstList.MoveLast
rstList.MoveFirst
yList = rstList.RecordCount

yList = 3  'done for testing

For xList = 1 To yList
    'Get name of table.
    tblName = rstList!tblName
    
    'Delete old linked table, if any.
    If IsTable("linked") Then DoCmd.DeleteObject acTable, "linked"
    
    'Attempt to connect to remote table.  If it's not there, go to next one.
    On Error GoTo AroundTable
    
    Set tdfLinked = CurrentDb.CreateTableDef("linked")
    tdfLinked.Connect = strConnect
    tdfLinked.SourceTableName = tblName
    CurrentDb.TableDefs.Append tdfLinked
        
    'Verify table is in local database.
    If Not IsTable(tblName) Then
        MsgBox "No table named " & tblName & " in this database."
        GoTo AroundTable
    End If
    
'    tblName = tblName & "x"
'    Used for forcing a bad name.  On Error GoTo does not work if used.
    
    'Open the two tables.
    On Error GoTo BadOpenLocal
    Set rstLocal = CurrentDb().OpenRecordset(tblName)
    
    On Error GoTo BadOpenLinked
    Set rstLinked = CurrentDb().OpenRecordset("linked")
    Set flds = rstLinked.Fields
    
    
    'Scan through remote table's fields and get description.  If not
    ' there, an error will be raised when we try to fetch it.
    
    For Each fld In flds
        tstLocal = "test"
        tstLinked = "test"
        fldName = fld.Name
        
On Error GoTo ShowError
        tstLinked = fld.Properties("Description").Value
        'See if the property exists in the local table.
        tstLocal = CurrentDb().TableDefs(tblName).Fields(fldName).Properties("Description")
        CurrentDb().TableDefs(tblName).Fields(fldName).Properties("Description") = tstLinked
        
AroundField:
    Next fld
    
    rstLocal.Close
    rstLinked.Close
    
AroundTable:
    If xList < yList Then rstList.MoveNext
    
Next xList

rstList.Close

MsgBox "All Done!"

ExitPoint:

Exit Sub

NoDesc:
    If tstLinked = "test" Then      'Could not find description in linked table.
        Err.Clear
        Resume AroundField
    Else
        If tstLocal = "test" Then
            If Err.Number = 3270 Then       'The property did not exist.  Add it.
                Err.Clear
                Set prp = CurrentDb().TableDefs(tblName).Fields(fldName).CreateProperty("Description", dbText, "x")
                Resume Next
            Else
                GoTo ShowError
            End If
        Else
            'There was a problem updating the property.
            GoTo ShowError
        End If
    End If
    MsgBox "Fell through NoDesc error trap."
    GoTo ShowError
    
BadOpenLocal:
    MsgBox "Could not open local table " & tblName & "."
    Err.Clear
    Resume AroundTable
    
BadOpenLinked:
    MsgBox "Could not open linked table " & tblName & "."
    rstLocal.Close
    Err.Clear
    Resume AroundTable
    
BadDesc:
    MsgBox "Could not add description '" & tstLinked & "' to field '" & fldName & "' in table '" & tblName & "'."
    Err.Clear
    Resume AroundField
    
ShowError:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    Resume ExitPoint
    
End Sub

Open in new window

0
Comment
Question by:springthorpeSoftware
  • 4
  • 3
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Change from 'break on unhandled errors' to 'break in class module'.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
What happens if you Force an error like so:

Public Sub LoadDescriptions()
On Error GoTo ShowError
Err.Raise 3051

Does it go to your error handler.

Also ... in the VBA Editor >> Menu>>Debug>>Compile ... do you get any compile errors?

mx
0
 

Author Comment

by:springthorpeSoftware
Comment Utility
Guys:
Thanks for replying!

matthewspatrick:  No change.  Still errors immediately.  I changed back.

DatabaseMX:
We may be on to something...  If I put that at the very top, my routine handles the error.  If I put near the top of the "For xList = 1 to yList" loop, immediately after On Error GoTo AroundTable, my routine handles the error the first time through the loop.  On the second pass, however, the error is raised immediately by Access.  It never branches to AroundTable like it did on the first pass.

I tried adding an Err.Clear immediately after the AroundTable: line marker, but got the same results.

Could this be related to the On Error being inside a loop?  Related to the error stack?  Caused by pushing the same "goto" onto the stack as the previous one?  Can't be.  I've know I've done that before.

Bruce
0
 

Author Comment

by:springthorpeSoftware
Comment Utility
DatabaseMX:  Forgot to add to earlier post -  No compile errors on manual compile.
Bruce
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I have a meeting for an hour or so ... back then.
Keep messing with it ... sounds like you are close ...

mx
0
 

Accepted Solution

by:
springthorpeSoftware earned 0 total points
Comment Utility
Found the problem.  Each On Error GoTo must evenutally encounter a RESUME or the stack quickly looses track of where to go, thus defaulting to the Access default error handling/display.  Amazed that I haven't run into this before.

Reworked the code accordingly and that problem goes away.

Thanks for the responses and efforts.

Bruce
0
 

Author Closing Comment

by:springthorpeSoftware
Comment Utility
Found suggestion elsewhere, then resolved by trial and error.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"We may be on to something..."

:-)
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now