422,727 Members | 2,138 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,727 IT Pros & Developers. It's quick & easy.

"on error resume next" does not work!

P: 3
Hello guys, I stumbled upon this forum recently and am hoping someone here can help me with this problem.

This is probably very stupid, but I can't get past this easy problem / glitch.

All I need to do is test if a table exists, and then delete it if it does (so I can create a new one).

Of course, you can't delete a table if it doesn't exist, and this returns an error.

Every source I've looked at says an easy way to solve this is by using the on error resume next statement. to skip the error.

my code looks like this (simplified)

Private Sub Command0_Click()

On Error Resume Next
DoCmd.DeleteObject acTable, "testtable"

End Sub
this doesn't return an error if "testtable" exists, but always returns the error if "testtable" does not exist.

to me it seems like I have a glitch in VBA somewhere. Anyone have any suggestions?

Thanks!
(microsoft access 2000)
Jul 14 '06 #1
Share this Question
Share on Google+
7 Replies


100+
P: 179
Try this code instead (assuming, of course, that this is for the OnClick event of a button with the name "DeleteTestTable"):
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Err_DeleteTestTable_Click
  2.  
  3.     DoCmd.DeleteObject acTable, "testtable"
  4.  
  5. Exit_DeleteTestTable_Click:
  6.     Exit Sub
  7.  
  8. Err_DeleteTestTable_Click:
  9.     MsgBox Err.Description
  10.     Resume Exit_DeleteTestTable_Click
Good Luck.
comteck
Jul 14 '06 #2

P: 3
Thanks Comteck for your reply.

That code looks interesting, I see what you're trying to do there.

Unfortunately, though I belive your code is good (as is mine), I get the same error.

The error I get here (access specific because it can't find the table) is the following:

Run Time Error '7874'
Microsoft Access cant find the object 'testtable'
I should mention that "on error resume next" does not work in any level, public or private functions, or in module functions that I've tried.

I am wondering if something is hosed on my version of access, or i need to add another library? The debugger highlights the command (blue) appropriately, but the "Object Browser" is not able to find me any combination of the "on error resume next" wording in the command reference.

I have the following libraries enabled:
Visual Basic for applications
Microsoft Access 9.0 object library
OLE Automation
Microsoft active x data objects 2.1 library

I can't see that a command as basic as "on error resume next" wouldn't be included in the visual basic for applications libary..but i wanted to throw it out there
Jul 14 '06 #3

P: 3
OK>>> I FOUND the solution

IMO..the problem and solution to this is extremely microsoft'd up.

Under Tools>Options>General you must set the error trapping to "Break on Unhandled Errors". I had it to "break on all errors". Evidently, "on error resume next" doesn't override the "break on all errors" setting..... DAHHHHH

Anyway, there's the solution for the archives..... I'm suprised it was so hard to find this solution and that it took until now for me to figure it out, but i guess that's the way it goes. I hate to think of the hours I spent chasing this stupid problem
Jul 14 '06 #4

P: 1
thanx man you are my lifesaver!! very kind of you to post this solution. it works!! thou one has to restart VB 6...
Aug 9 '06 #5

P: 2
Thank you so much for posting this. It solved my problem too!

It took me a while, though: I first looked in Access (for Tools-->Options-->General). The option is actually in the visual basic editor (in Tools-->Options--General). :cool:

Thanks again.
Aug 14 '06 #6

P: 1
Hi guys
Thank you for posting this solution.
I think Microsoft have changed this in an update or Service Pack because I had code that was working six months ago.
Regards
Stephen
Nov 16 '06 #7

NeoPa
Expert Mod 15k+
P: 30,736
I had a colleague ask about this just earlier today.
In the VBA window, look at Tools \ Options \ General \ Error Trapping.
If you have it set to Break on All Errors, then the Error Trapping will never work.
Sorry to take so long to reply - I've been too busy.
I could have saved you the aggro :(.

Error Trapping

Determines how errors are handled in the Visual Basic development environment. Setting this option affects all instances of Visual Basic started after you change the setting.

Break on All Errors Any error causes the project to enter break mode, whether or not an error handler is active and whether or not the code is in a class module.


Break in Class Module Any unhandled error produced in a class module causes the project to enter break mode at the line of code in the class module which produced the error.


Break on Unhandled Errors If an error handler is active, the error is trapped without entering break mode. If there is no active error handler, the error causes the project to enter break mode. An unhandled error in a class module, however, causes the project to enter break mode on the line of code that invoked the offending procedure of the class.
Nov 16 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.