Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi to all. I am trying to get a delete query to delete all records in an order whose Backordered value in tblProducts is True. The sticker right now is I can't seem to get the syntax to work right so it deletes it only for the current order. Can someone help me? This is on a command button that I want to delete the items that are backordered, then print the order and labels. I have only included the delete part. The rest is all working correctly.

    strsql = "DELETE tblOrderDetails.OrderID, tblOrderDetails.ProductID, tblOrderDetails.Quantity, tblProducts.Backordered FROM tblProducts INNER JOIN tblOrderDetails ON tblProducts.ProductID = tblOrderDetails.ProductID WHERE (((tblProducts.Backordered)=True) OrderID= & Me!OrderID"

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this:

    strsql = "DELETE tblOrderDetails.OrderID, tblOrderDetails.ProductID, " & _
    "tblOrderDetails.Quantity, tblProducts.Backordered FROM tblProducts " & _
    "INNER JOIN tblOrderDetails " & _
    "ON tblProducts.ProductID = tblOrderDetails.ProductID " & _
    "WHERE (((tblProducts.Backordered)=True) AND ((tblOrderDetails.OrderID)=" & Me!OrderID & "));"

    If orderID is not a number field then the last bit needs to be either

    ... AND ((tblOrderDetails.OrderID)='" & Me!OrderID & "'));"

    Or

    ... AND ((tblOrderDetails.OrderID)=" & Chr(34) & " & Me!OrderID & Chr(34) &" ));"

    If you look closely in the first one there is a single quote after the = sign and before the second last bracket. Chr(34) is the same thing and easier to see, but more time to type!

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It is a number field. I don't know if what I'm trying to do is possible or not, but that didn't work.

    Does a delete query have to be updateable?

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='79schultz' post='792011' date='03-Sep-2009 15:01']It is a number field. I don't know if what I'm trying to do is possible or not, but that didn't work.

    Does a delete query have to be updateable?[/quote]
    Someone with a little better knowledge will have to answer the updatable question. However, if you run this query manually through the query window does it work properly? For this kind of query, that's how I usually get the syntax and then I add in all the quotes and line breaks to run it in VBA, which is essentially what the edits I gave you do. It definitely would fail the way you posted it. You wouldn't need to edit what I gave you if the field is a number field unless I made a typo. One of the clever people on the board will hopefully notice if I did.

    If so, after strsql, are you actually running the query? the bit you posted is only the actual syntax for the query. You'd also have to have a line following that
    Docmd.RunSQL strsql

    I assumed since you said everything else was working fine that you knew about that bit. Perhaps I shouldn't have assumed that.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK. I didn't have the DoCmd.RunSQL in there. Now that I put that in there, I get the following message. Specify the table containing the records you want to delete.


    [quote name='PeterN' post='792025' date='03-Sep-2009 16:02']Someone with a little better knowledge will have to answer the updatable question. However, if you run this query manually through the query window does it work properly? For this kind of query, that's how I usually get the syntax and then I add in all the quotes and line breaks to run it in VBA, which is essentially what the edits I gave you do. It definitely would fail the way you posted it. You wouldn't need to edit what I gave you if the field is a number field unless I made a typo. One of the clever people on the board will hopefully notice if I did.

    If so, after strsql, are you actually running the query? the bit you posted is only the actual syntax for the query. You'd also have to have a line following that
    Docmd.RunSQL strsql

    I assumed since you said everything else was working fine that you knew about that bit. Perhaps I shouldn't have assumed that.[/quote]

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Now, I can insert this into a query and if I input an orderID it will find the Backordered items in that order. It works like I need it, except I don't want to always input the OrderID.

    DELETE tblOrderDetails.OrderID, tblOrderDetails.ProductID, tblOrderDetails.Quantity, tblProducts.Backordered
    FROM tblProducts INNER JOIN tblOrderDetails ON tblProducts.ProductID = tblOrderDetails.ProductID
    WHERE (((tblOrderDetails.OrderID)=[Me]![OrderID]) AND ((tblProducts.Backordered)=True));

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='79schultz' post='792031' date='03-Sep-2009 17:23']Now, I can insert this into a query and if I input an orderID it will find the Backordered items in that order. It works like I need it, except I don't want to always input the OrderID.

    DELETE tblOrderDetails.OrderID, tblOrderDetails.ProductID, tblOrderDetails.Quantity, tblProducts.Backordered
    FROM tblProducts INNER JOIN tblOrderDetails ON tblProducts.ProductID = tblOrderDetails.ProductID
    WHERE (((tblOrderDetails.OrderID)=[Me]![OrderID]) AND ((tblProducts.Backordered)=True));
    [/quote]
    One other thing I forgot as I am looking at this. As Hans would say, VBA doesn't understand the [Me]... syntax. You should have a variable to pass that value.

    Code:
    Dim intOrd as integer
    intOrd = [Forms]![YourFormName]![OrderID]   
    
    strsql = "DELETE tblOrderDetails.OrderID, tblOrderDetails.ProductID, " & _
    "tblOrderDetails.Quantity, tblProducts.Backordered FROM tblProducts " & _
    "INNER JOIN tblOrderDetails " & _
    "ON tblProducts.ProductID = tblOrderDetails.ProductID " & _
    "WHERE (((tblOrderDetails.OrderID)=" & intOrd & ") AND ((tblProducts.Backordered)=True));"
    
    DoCmd.RunSQL strsql
    You could also play with [Me]![OrderID] if you are calling this from a command button on a form, but I prefer to be specific. Depending on how long your order numbers are, you may need to declare intOrd as Long. Integers only go up to 32,000 and a bit.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK. I finally got it figured out. At least I think so. Combining with what Peter N told me and this information Re: Delete Query - 'Specify the table containing the records you want to delete'
    Make sure your query doesn't just say DELETE * FROM ....

    Change it to DELETE tblOSSMN117.* FROM ...
    from this website http://www.access-programmers.co.uk/forums...ad.php?t=156123 , it is working. Thanks for your perseverance in helping me!

    Here is what I am using to get it to work. The trick that seems to have done it was tblOrderDetails.* instead of naming each column.

    intOrd = [Forms]![frmInternationalOrders]![OrderID]
    'Dim stDocName As String


    strsql = "DELETE tblOrderDetails.*" & _
    "FROM tblProducts INNER JOIN tblOrderDetails ON tblProducts.ProductID = tblOrderDetails.ProductID " & _
    "WHERE (((tblProducts.Backordered)=True) AND (tblOrderDetails.OrderID)=" & intOrd & ");"
    DoCmd.RunSQL strsql

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I guess the frosting on this cake would be if it is possible to have a window pop up telling me which Products are on backorder before deleting it. If someone has an easy solution for that it would be nice, but as they say in Portuguese in Brazil, "Não esquenta a cabeça" Don't make your head hot trying to figure it out.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That should be possible to do - you would create a query to find products that have a backorder, and then do a right join from the Delete query to the Backorder queryand make sure the Product ID in the BackOrder query is null when you run the delete.
    Wendell

Posting Permissions

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