473,249 Members | 1,783 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,249 software developers and data experts.

"on error resume next" does not work!

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?

(microsoft access 2000)
Jul 14 '06 #1
7 17158
179 100+
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
  3.     DoCmd.DeleteObject acTable, "testtable"
  5. Exit_DeleteTestTable_Click:
  6.     Exit Sub
  8. Err_DeleteTestTable_Click:
  9.     MsgBox Err.Description
  10.     Resume Exit_DeleteTestTable_Click
Good Luck.
Jul 14 '06 #2
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
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
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
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
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.
Nov 16 '06 #7
32,554 Expert Mod 16PB
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

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

Similar topics

by: jason | last post by:
Is there a way to avoid On Error Resume Next for: cnn.Open strCon SQL = "EXEC Customer @txtEmail='" & email_address & "'" set rs = cnn.execute(SQL) 'On error resume next rs("email_address")...
by: Laphan | last post by:
Hi Everybody I put the On Error Resume Next command as the very first line in my ASP pages, which may contain various inc files and sub-routines/functions, so that I can try and stop the dreaded...
by: tom blower | last post by:
Access 2k (10.6501.6714) SP3 For YEARS the code line "On Error Resume Next" has worked without any problems. It is an essential tool. A couple of days ago, I put in another hard drive, mentioned...
by: itsupport1 | last post by:
Hi, I am importing some records from one table to another table, and due to Constraints in the destination table, it Throws an Exception and Skip the Whole Rest of records. So I did implement...
by: bob.needler | last post by:
I know On Error Resume Next is generally considered lazy. But can someone tell me why the resume next in Exit_Handler does not seem to work? It generates the typical unhandled runtime error...
by: Neo | last post by:
I found on error resume next doesn't work in for each... e.g. on error resume next for each x in y 'do stuff next if you have an error in for each loop, it falls in infinite loop... it...
by: Maxwell2006 | last post by:
Hi, I know that this is not a good practice, but I wonder do we have "on error resume next" in C#? Thanks,
by: esimond | last post by:
Hi All ! Just joined this big community, and a BIG Swiss Hello in there ! Having recently switched from VB to C#, I indeed still have to discover all the powerful sides of that great...
by: julietbrown | last post by:
Can you boost my understanding of Access? I don't like things I can 'cure' but don't understand why! I've converted the Save macro on my form to VBA. It looks like this ... as I'm sure you...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.