Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    False Update (Access 2000)

    I need to make an update statement with the following parameters.
    I have three tables orders - order details - products
    I need to update the field branch1 from the table products, i.e. products.branch1 to the following:
    products.branch1 = [products].[branch1]+[order details].[cartons]

    Also, i need to put another Whhere clause, since i am clicking on a list box called ListInvoices.It is difficult for me.
    My function doesnt work and i cannot find out where i am wrong, i supoose i am wrong at several places.
    Could someone help me out?

    My function as i constructed it, is shown below, and i would like very much to hav it working.

    Dim frm As Form
    Set frm = Forms![FOrderInformation]

    Dim strSQL1 As String
    Dim varRow As Variant
    For Each varRow In frm.ListInvoices.ItemsSelected
    'update the invoice before deleting it
    Application.Echo True
    DoCmd.SetWarnings False
    strSQL1 = " UPDATE orders INNER JOIN (products INNER JOIN [order details] ON ([products].[Productid]=[order details].[ProductID]) AND

    ([products].[Productid]=[order details].[ProductID])) ON [orders].[orderid]=[order details].[OrderID] SET products.branch1 = [products].[branch1]+[order

    details].[cartons]" & _
    " WHERE ((([orders].[paymentid])=True)) And [Orders].PaymentID= " & frm.ListInvoices.ItemData(varRow) & ";"

    DoCmd.RunSQL strSQL1
    Next varRow
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: False Update (Access 2000)

    My mind got stuck on this comment
    <hr>'update the invoice before deleting it<hr>
    I'm sure there's some logic behind it, but I'm afraid it escapes me.

    When you say your function doesn't work, what do you mean? Are you getting an error, is the wrong value being inserted, is the wrong record being updated, or what?

Posting Permissions

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