Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append during updating (Access 2000)

    I have to update the field DDU in the table Products from the field DDU in the table Preisliste.
    Both tables contain the field Code.
    The function works very good and updates all the prices in the table Products with the new prices from the table
    Preisliste.

    However, there are some new code numbers in the table Preisliste.Can i add on some line in my function
    to append all the codes that do not exist in the tables Products,and also append their DDU price.
    Also,is it possible to append the whole row, with the ProductName etx for the non existing products?
    Below is my function


    Public Function FncUpdateDDU()
    'This function copies the DDU prices from the table Preisliste into the DDU prices of the table Products
    ' and by dividing to 100 converts the price to price for liter instead of price for 100 ltrs.
    Dim sqlUpdateDDU As String
    sqlUpdateDDU = " UPDATE products INNER JOIN Preisliste ON products.code = Preisliste.Code SET products.ddu =

    Preisliste.Code/100;"
    CurrentDb.Execute sqlUpdateDDU
    End Function

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

    Re: Append during updating (Access 2000)

    Try the following, first on a copy of the database.
    - Activate the Queries section of the database window.
    - Click New.
    - Select "Find Unmatched Wizard" and click OK.
    - In the first step of the Wizard, select Preisliste, then click Next.
    - In the second step, select Products, then click Next.
    - In the third step, select the field Code on both sides, then click the <=> button, and click Next.
    - In the fourth step, add all other fields you want to append, but not an AutoNumber field if there is one. Then click Next.
    - In the fifth and final step, specify a name for the query, then click Finish.
    - Check that the query returns the correct records.
    - Switch to design view.
    - Select Query | Append Query, and specify Products as table to append to, then click OK.
    - Make sure that the fields from Preisliste are appended to the correct fields in Products.
    - Edit the DDU field to put / 100 after it (the division by 100 you require.)
    - Clear "Append to" for the field from Products, with the Is Null criteria.

    If you still want to do this in code, select View | SQL. You will see the SQL instruction you need. You will have to remove the line ends, then you can copy the SQL statement into the Visual Basic Editor.

Posting Permissions

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