on error goto not working 1

[bradmaunsell]

I have recently converted from Access 97 to Access 2000 and Access 2003. In both appliactions, the ON ERROR GOTO no longer works. I have tried different libraries without success.

Her is one (of many) examples where the system bombs out. It seems the system does not recognize the On Error GoTo NoTableExists. The code stops and the editor is high-lighted yellow on the Docmd.OpenTable....

So, the routine knows the tables does not exist but the code will not branch the GoTo.... code

I have this same problem in many places in my applications. I don't think there is any routine that works the On Error correctly. All of these routines have worked fine for years in Access 97.

Any thoughts?

Thanks,
Brad
South Burlington, VT

Private Sub cmdBoudereau_Click()

ValidateDates
If VALID <> True Then Exit Sub

On Error GoTo NoTableExists
DoCmd.OpenTable "tblBordereau_bsm"

SetupBordereauTable
ClearBordereau

AppendBordereauPremiums Me.txtBegDate, Me.txtEndDate
AppendBordereauEndorsements Me.txtBegDate, Me.txtEndDate
DoCmd.OpenReport "rptBordereau", acViewPreview

Exit Sub
NoTableExists:
MsgBox "No Table Exists"
End Sub

 

[KenReay]

Hi

"the system bombs out", if it stops in the dubugger does it give an error message, if yes what is it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[BuGlen]

Hi Brad,

There doesn't appear to be anything wrong with the code, and it should work in Access 2000 / 2003 just as it did before. A couple of questions might help shed some light:

Did you convert the project from the 97 format to the 2000 format, or are you running the 97 format project in Access 2000/2003?

Is this problem on multiple PCs or only one? What OS(s) on the affected machine(s)?

- Glen

 

[bradmaunsell]

Glen,

I thought tah the tables were converted to 2000 format as part of the conversion prosess. But now that you bring it up, how can I verify that the files did convert?

 

[KenReay]

Hi

Are the tables in the same mdb as the code or do you have a split mdb with linked tables ?

Is there an error message?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bradmaunsell]

One more bit of info...

The original 97 appliactions were all DAO and after running the conversion routines, I re-wrote "work-around" style changes to handle the stuff that would no longer run.

The swith to ADo is expected to be a complete application renovation and re-write.

Brad

 

[BuGlen]

Hi Brad,

When you open the project in Access, the DB window will show the version of the DB in the Database window in parenthesis. Or, if you go to the Tools -> Database Utilities -> Convert Database menu, the option that is disabled will also indicate the current version of the database.

- Glen

Hope this helps.

- Glen

Know thy data.

 

[bradmaunsell]

The tables were split and linked before and after the conversion. I was able to verify that the converted apps are using the 2000 format.

 

[KenReay]

OK so you have answered one question, IS THERE AN ERROR MESSAGE AND IF YES WHAT IS IT

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bradmaunsell]

Oops! I wrote a msg about the error msg and must have exited without sending.

Anyway, there is a msg saying:

run time error 7874

Microsoft Office Access can't find the object 'tblBordereau_bsm"

This is correct because the table dosn't always exist. When it dosen't exit a routine runs to create the table.

So, it appears the system recognizes an error condition has happened but does not branck to the GoTo label.

Brad

 

[KenReay]

Hi

Have just tried the following in Access 2000

Option Compare Database
Option Explicit

Private Sub Command0_Click()

On Error GoTo NoTableExists
DoCmd.OpenTable "tblBordereau_bsm"

Exit Sub
NoTableExists:
MsgBox "No Table Exists"
End Sub

it works, ie get msgbox

have you tried retyping the onerror statement

have you compiled the code, is it clean ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Golom]

You have probably checked this but, from the "Tools / Options / General" menu from a Code window (i.e. Form/ Module) check that "Break on Unhandled Errors" is selected. If "Break on ALL Errors" is selected then all your error trapping is ignored.

 

[BuGlen]

Hi Brad,

It sounds like something in the VBA libraries or in the project itself may be a bit off. Try this and see if you get the same results:

Reboot your PC to clear the memory and then create a new Access project. Add a module to the project with one simple sub routine:

Sub Test()

    On Error GoTo NoTableExists
    
    DoCmd.OpenTable "SomeTable"
    
    On Error GoTo 0
    Exit Sub
    
NoTableExists:
    MsgBox "Boom!"

End Sub

Save and run the subroutine from the IDE and see if you get the messagebox. If not, then there may be something wrong with the Access installation and VBA library references on the PC.

Hope this helps.

- Glen

Know thy data.

 

[BuGlen]

Golom,

OK, this definately gets the award for simple things that get overlooked. I completely forgot about that option, and I'm willing to bet that this is what Brad is experiencing.

Simply brilliant!

- Glen

 

[bradmaunsell]

Both code examples produce the same results as my appliaction.

One of the first things I did was to set for "break on all errors" but do not find it in Access 2003. Am I overlooking something?

Brad

 

[bradmaunsell]

Another "Oops!"

I do see the "Break on all errors" in tools from the code section.

It is (and has been) set to break on all options.

Brad

 

[bradmaunsell]

I have the following library selections:

Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Office Object Library
Microsoft Word 11.0 Object Library

I run all of the updates for both Office and XP
I compile and compress often
The Word library because I do some Word interaction

I have also tried various pecking orders with the above.

 

[BuGlen]

As Golom pointed out, having the "Break on all errors" option set will ignore any error traps in your code. If you change this option to "Break on unhandled errors", your problem should be resolved.

Please try this and post back to let us know if it fixes your issues.

Hope this helps.

- Glen

Know thy data.

 

[bradmaunsell]

I CANNOT BELEIVE MY EYES!!!!

That works!!!

I misunderstood "Break on all errors" I thought this forced any error to break.

Thanks to everybody for the responses and help!!

Brad