Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Unanswered: Trying to delete queries based on whether or not they exist in querydefs

    Hello, I'm using the following code to check if a certain query exists in the database and then delete it if it does. The code is not fully executing as it gets errors and then stops, therefore some of the queries that I want deleted are being deleted while others aren't. I think there's a problem with how I'm structuring this. Can anyone help?
    Thanks

    For x = 0 To 16
    strProduct = aProduct(x)
    For Each qdfLoop In CurrentDb.QueryDefs
    If qdfLoop.Name = "" & strProduct & " Customer Count By Month" Then
    exists = 1
    Else
    End If
    Next
    If exists = 1 Then
    DoCmd.DeleteObject acQuery, "" & strProduct & " Customer Count By Month"
    Else
    End If
    Next x

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, wrapping code in the code tags really aids readability
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Error messages and locations of the error help!

    However, I suspect it is because you never reset exists to 0.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We seem to have a problem on the site - post #2 was actually posted AFTER #3.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2009
    Posts
    6
    You hit the nail on the head. Resetting exists to 0 solved the problem...rookie mistake! Thanks a lot for your help.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Yes, there's a major flaw in your loop structure. Try this instead:
    Code:
    For x = 0 To 16
       strProduct = aProduct(x)
       For Each qdfLoop In CurrentDb.QueryDefs
          If qdfLoop.Name = "" & strProduct & " Customer Count By Month" Then
             exists = 1
          Else
             exists = 0
          End If
          If exists = 1 Then
             DoCmd.DeleteObject acQuery, "" & strProduct & " Customer Count By Month"
             exists = 0
          End If
       Next qdfLoop
    Next x
    Not only were you not resetting exists to 0 at any point, but you were only checking the status of exists once per loop through the query definitions. The above code checks each iteration of the querydef loop, and then resets exists. (As an aside, I find it helps to explicitly state when FOR...NEXT loop counter is incremented at each NEXT statement - I was writing a four-level loop a while ago, and would have been horribly lost without it!)

    If you want to simplify it further, you could replace the line "exists = 1" with the line beginning "DoCmd.DeleteObject...", and remove the code between there and "Next qdfLoop".

    In case you're wondering, I've managed both of the above flaws in the past!

  7. #7
    Join Date
    Jun 2009
    Posts
    6
    This is very helpful! Thank you.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

    Remember - while it's great to learn from your own mistakes, it's even better to learn from someone else's...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •