Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    runtime error (access 2000)

    I get a runtime error 3072 , as could be seen form the attachement, when i want to update not only one but all the fields from
    one table to another.Could i get some help? Especially, i want to update the table products from the table tblworms.In this
    table the product number 1 has 10 items and products number 2 has also 10 items.I want to update these figures into table
    products.However i have an error message that my syntax is wrong

    My code with the error is :
    Dim StrItems As String

    StrItems = " UPDATE TblWorms INNER JOIN products ON " & _
    " TblWorms.ProductID" & _
    " = products.Productid SET products.items1 = TblWorms.items WHERE ProductID In (SELECT ProductID FROM TblWorms)"
    CurrentDb.Execute StrItems

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: runtime error (access 2000)

    I'm not sure what you want to do. You say that you want to update "all the fields", but Products has only two fields: ProductID and Items1. You need ProductID to link the tables, so you can only update one field: Items1.

    Your SQL statement contains a double link between the tables: one in the INNER JOIN part, and one in the WHERE part. You don't need the latter, this will work:

    StrItems = "UPDATE TblWorms INNER JOIN products ON " & _
    " TblWorms.ProductID" & _
    " = products.Productid SET products.items1 = TblWorms.items"

Posting Permissions

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