Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update query (Access 2000)

    I am trying to update 2 queries, orders and order details.The relationship between them is one to many.
    They are connected through the field OrderID. OrderId has an autonumber in the table Orders and number in the table order details.
    The tables are updated to the tables orders1 and order details1.
    My code for updating the table orders works perfect:
    Public Function UpdateOrders()
    Dim sql As String
    sql = " UPDATE orders1 INNER JOIN orders ON [orders1].[orderid]=[orders].[orderid] SET" & _
    " orders.customerid = [orders1].[customerid] " & _
    " , orders.orderdate = [orders1].[orderdate] " & _
    " , orders.paymentid = [orders1].[paymentid] " & _
    " , orders.PaymentMethodID = [orders1].[PaymentMethodID] " & _
    " , orders.bankid = [orders1].[bankid] " & _
    " , orders.invoicedate = [orders1].[invoicedate] "
    CurrentDb.Execute sql
    End Function

    However i cannot update the table order details.I receive a mesage Error in the join operation.


    Public Function UpdateOrderDetails()
    Dim strsql As String
    strsql = " UPDATE orders details1 INNER JOIN orders details ON [orders details1].[orderid]=[orders details].[orderid] SET " & _
    " orders details.unitprice = [orders details1].[unitprice]"
    CurrentDb.Execute strsql
    End Function

    Can i have some help?

  2. #2
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: update query (Access 2000)

    Put square brackets round [orders details1] and [orders details] wherever the names occur. I think it's the spaces in the names it doesn't like.

    Simon

Posting Permissions

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