Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Burgas, Bulgaria
    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

    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 =

    CurrentDb.Execute sqlUpdateDDU
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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