So Confused on an Exists code

[It_thug]

OKay, this code has been working for a while now. Today, I open up the database and test it before moving on to another project and it is no longer working. And I don't know why.

On Error Resume Next
Exists = IsObject(CurrentDb.QueryDefs("NameofQuery"))
If Exists Then
DoCmd.DeleteObject acQuery, "NameofQuery"
End If

Name of Query is actually a variable that passes through. Which works fine.

It was, for reasons I'm not sure of, using CurrentDB.TableDefs and acTable... which worked..and now doesn't. So I switched it to Querydefs.

Now, if the query Exists, then it does what it should do. It deletes the Query.

however, if it doesn't exist, it pops up with an error saying "item not found in this collection".

Before, it would just move on, now it doesn't.

And I don't know why. No code was changed since the last time I opened this database up.

 

[It_thug]

oh.. so my question is twofold

1- why isn't it working now?
2- Is there another way to determine if a query exists? and if so, delete it?

The purpose of this is to create ad-hoc queries for someone. And this part of the code is in there so that if the person tries to create the same table twice, it will not error on them.

 

[boblarson]

Well, first of all you need to read the help file about IsObject and understand it isn't checking to see if it exists in the database. It only checks to see if the item you are checking is an instantiated object (variable) of the item.

IsObject Function


Returns a Boolean value indicating whether an identifier represents an object variable.

Syntax

IsObject(identifier)

The required identifier argument is a variable name.

Remarks

IsObject is useful only in determining whether a Variant is of VarType vbObject. This could occur if the Variant actually references (or once referenced) an object, or if it contains Nothing.

IsObject returns True if identifier is a variable declared with Object type or any valid class type, or if identifier is a Variant of VarType vbObject, or a user-defined object; otherwise, it returns False. IsObject returns True even if the variable has been set to Nothing.

Use error trapping to be sure that an object reference is valid.

 

[It_thug]

Well, first of all you need to read the help file about IsObject and understand it isn't checking to see if it exists in the database. It only checks to see if the item you are checking is an instantiated object (variable) of the item.

okay, fair enough on the RTFM comment.

Still not sure how it was working and now doesn't. Isn't the line for On Error Resume Next suppose to skip it? or is what is coming back not considered an error?

Anyway, I removed that check and made it the following, which works as I need it to.

On Error Resume Next
DoCmd.DeleteObject acQuery, "NameofQuery"

I do thank you for your time.

 

[boblarson]

Well, I have to disagree that it was working. It may have APPEARED to be working but yet in the end it is not being used correctly. So, the question I have is just this -

are you trying to find whether something exists and, if so, delete it?

If that is the case then you should not be using this code but instead something else. (I'll wait for your response before posting any code)

 

[It_thug]

Well, I have to disagree that it was working. It may have APPEARED to be working but yet in the end it is not being used correctly. So, the question I have is just this -

are you trying to find whether something exists and, if so, delete it?

If that is the case then you should not be using this code but instead something else. (I'll wait for your response before posting any code)

Agreed on your statement. (I edited the above, hoping to get it before you got back... oops)

But yes, I need to check to see if something is there and delete it if it is and if it isn't move on to the next part.

The following does it. But I'm not sure it's the best option.

Anyway, I do thank you for your time.

On Error Resume Next
DoCmd.DeleteObject acQuery, "NameofQuery"

 

[boblarson]

I would really not use On Error Resume Next because that can obscure some real errors. But what I would do is perhaps use this:

Function MyFunction(strQueryName As String)
On Error GoTo Err_MyFunction

DoCmd.DeleteObject acQuery, strQueryName 

Exit_MyFunction:
   Exit Function

Err_MyFunction:
  If Err.Number <> 7874 Then
      MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number
  End If
  Resume Exit_MyFunction
End Function

 

[It_thug]

I would really not use On Error Resume Next because that can obscure some real errors. But what I would do is perhaps use this:

Function MyFunction(strQueryName As String)
On Error GoTo Err_MyFunction

DoCmd.DeleteObject acQuery, strQueryName 

Exit_MyFunction:
   Exit Function

Err_MyFunction:
  If Err.Number <> 7874 Then
      MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number
  End If
  Resume Exit_MyFunction
End Function

Very nice... thank you very much for your time on this. I'm still learning as I go. I very much appreciate it.

 

[It_thug]

Again, thank you for your time.

But the problem with the above is that I don't want it to give an error if the object doesn't exist. If it doesn't exist, it just needs to move to the next step and create it from scratch.

 

[Banana]

That's what the error handler should do. That's why Bob had this line:

If Err.Number <> 7874 Then

which basically says "ignore the error if it's 7874. But maybe you got a different error that you also want to ignore. In such cases, you need to add it to the error handler. Typically, I'd use this structure to make it easier:

Err_MyFunction:
  Select Case Err.Number 
      Case 7874, XXXX, YYYY, ZZZZ
          'Do nothing
      Case Else
          MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number
  End If
  Resume Exit_MyFunction
End Function

Either replace or omit the "XXXX, YYYY, ZZZZ" with the other error numbers you want to ignore. That way you know you won't miss out other crucial error that points to much deeper problem than simply an nonexistent object.

HTH.

 

[It_thug]

That's what the error handler should do. That's why Bob had this line:

If Err.Number <> 7874 Then

which basically says "ignore the error if it's 7874. But maybe you got a different error that you also want to ignore. In such cases, you need to add it to the error handler. Typically, I'd use this structure to make it easier:

Err_MyFunction:
  Select Case Err.Number 
      Case 7874, XXXX, YYYY, ZZZZ
          'Do nothing
      Case Else
          MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number
  End If
  Resume Exit_MyFunction
End Function

Either replace or omit the "XXXX, YYYY, ZZZZ" with the other error numbers you want to ignore. That way you know you won't miss out other crucial error that points to much deeper problem than simply an nonexistent object.

HTH.

Ah... awesome.

I love this forum. I hope that someday I'll be proficient enough with this to help others like I have been helped.

 

[It_thug]

Something is really odd. It's not doing the On Error part. It's just getting hung up on the error it's self.

(which is 7874, so the original code should work. But it doesn't seem to.)

Is there something that could make the On Error just not work as it should?

I think I might be confusing myself.

 

[Banana]

I'm a bit unclear on what is meant by "It's just getting hung up on the error it's self."

Are you saying the execution just hangs when you try to do "DoCmd.Delete...." and never enters the error handler or does it enters the error handler and not correctly process the error or?? More info, please...

 

[It_thug]

FOUND IT!

The Options were set to break on All errors. Changing it to "Break on Unhandled Errors" resolved the issue.

I must have made that change for some unknown reason, or maybe a patch. I don't know. But it is now working as I need it.

Thank you all again for your help. I have most definitely learned something.

 

[It_thug]

I'm a bit unclear on what is meant by "It's just getting hung up on the error it's self."

Are you saying the execution just hangs when you try to do "DoCmd.Delete...." and never enters the error handler or does it enters the error handler and not correctly process the error or?? More info, please...

see previous post.

It was still giving the error. But I found the setting that was causing the problem. Now it is working as it should. Thank you again.

 

[Banana]

Great. I'm glad you found the problem. I've had times where I've forgotten about the break on all errors (which is handy in debugging for problematic errors that I don't want to get handled away).

Best of luck!