A2000: On Error Handling Not Working 1

[maxhugen]

In a new A2000 mdb, I've added MS's code to turn off SubDataSheets.

However, the On Error statement is not executed, and Access just throws its default error message.

Function TurnOffSubDataSheets()
'
' See [URL unfurl="true"]http://support.microsoft.com/Default.aspx?kbid=261000[/URL]
' "BUG: Slower performance on linked tables in Access 2000"
'
On Error GoTo tagError
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i%
Dim intCount As Integer

    Set MyDB = CurrentDb
    propName = "SubDataSheetName"
    propType = 10
    propVal = "[None]"
    rplpropValue = "[Auto]"
    intCount = 0
    
    For i = 0 To MyDB.TableDefs.Count - 1
        If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
            If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
                 MyDB.TableDefs(i).Properties(propName).Value = propVal
                 intCount = intCount + 1
            End If
        End If
        
tagFromErrorHandling:
    Next i
    MyDB.Close
    
    If intCount > 0 Then
        MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
    End If
    
    Exit Function
    
tagError:
    If Err.Number = 3270 Then
        Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
        MyProperty.Type = propType
        MyProperty.Value = propVal
        MyDB.TableDefs(i).Properties.Append MyProperty
        intCount = intCount + 1
        Resume tagFromErrorHandling
    Else
        MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets routine."
    End If
    
End Function

Anyone have any suggestions pls?

MTIA

Max Hugen
Australia

 

[HarleyQuinn]

What error message does it throw? And where in the code does it appear?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[maxhugen]

In the code, the error handler is "On Error GoTo tagError".

The 'tagError' checks if the error is 3270 (the property doesn't exist), and proceeds to add the property. Then it resumes.

Max Hugen
Australia

 

[ck1999]

Have you put a code break at if err.number = 3270 line to see what the err.number value is?

and as Harleyquinn asked what was the error

ck1999

 

[maxhugen]

Sorry if it wasn't clear...

The error is 3270. The problem is that it doesn't go to the error handler at all.

Access stops at the line that causes the error, which is:

If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then...

Access displays the correct error, but uses its default error handler, instead of observing the 'On Error GoTo tagError' statement.

I've tried this on a simple function that just uses the Error.Raise method to test it, and it seems to be an issue with Access not recognising the 'On Error' directive at all.

FWIW, I have previously applied SP3 for Office 2000, so it should be up to date.

I'll try re-installing A2000 and SP3, and post to this thread if that fixes the issue. I also have A97 installed, and Office 2003, in separate directories, but I haven't had problems with this in the past.

Max Hugen
Australia

 

[Ed2020]

From your VBA window:

Tools > Options > General tab.

Check that Break on Unhandled Errors is selected as your Error Trapping option.

Ed Metcalfe.

Please do not feed the trolls.....

 

[HarleyQuinn]

Good spot there Ed

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[maxhugen]

EXECELLENT spot there, Ed!

After all the years I've spent programming in Access, and I didn't know that!!!!

All fixed, many thanks mate.


Cheers, Max

Max Hugen
Australia

 

[Ed2020]

You're welcome Max, glad it solved your problem. Thanks for the star.

Ed Metcalfe.

Please do not feed the trolls.....

 

[irishjoe]

Cheers Ed, thought I was going crazy for a while! That fixed the problem nicely...