Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete function (Access 2000)

    I have a very efficient function that increases the fields Branch1 and items1 from the table Products with the values from the table ProductsTemp for a given product.Can somebody help me reconstruct the function so that it deleted instead of updating the values in the
    fields Branch1 and Items1 for a given product ?
    My working update function is the following :

    Dim STrProducts As String

    strProducts = " UPDATE products INNER JOIN productsTemp ON products.Productid =

    productsTemp.ProductID SET " & _
    " products.branch1 = [products].[branch1]+[productsTemp].[cartons]," & _
    " products.items1= [products].[items1]+[productsTemp].[quantity]"
    CurrentDb.Execute strProducts


    I will be grateful for any help

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete function (Access 2000)

    I don't think you want a delete query. That would delete the entire record, not just remove the value in certain fields. Just replace the field names you're using to set the values with the word Null (i.e., MyField = Null) in the query. However, be careful and make sure you are setting the correct table's values to Null. As your query stands now, you would be setting the fields in the products table to Null where the ProductID was also in productTemp. You also need to add the DISTINCTROW keyword after UPDATE so that the query only uses unique records and doesn't repeat itself over and over.
    Charlotte

Posting Permissions

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