Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access: On Error GoTo not working

Posted on 2011-09-14
8
Medium Priority
?
515 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36538937
Change from 'break on unhandled errors' to 'break in class module'.
0
 
LVL 75
ID: 36539071
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
ID: 36539335
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:springthorpeSoftware
ID: 36539356
DatabaseMX:  Forgot to add to earlier post -  No compile errors on manual compile.
Bruce
0
 
LVL 75
ID: 36539378
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
ID: 36544394
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
ID: 36565460
Found suggestion elsewhere, then resolved by trial and error.
0
 
LVL 75
ID: 36545067
"We may be on to something..."

:-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

722 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