Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deletion problems (Access 2000)

    I have 2 tables, Orders and Order Details, the same as with the database Nortwindtraders shipped with Access. The table order is linked with the table Order
    Details through the filed OrderID. It is autonumber in the table orders and number in the table Order Details.I have a very good function deleting a chosen order from a list box called ListOrders. This function is the following
    Public Function CancelOrders()

    Dim StrSQL As String
    strSQL = "DELETE * FROM [order details] WHERE orderID = " & frm!ListOrders & ";"
    DoCmd.RunSQL strSQL

    strSQL = "DELETE * FROM Orders WHERE orderID = " & frm!ListOrders & ";"
    DoCmd.RunSQL strSQL
    End Function

    I want to copy this function and accomodate it to other needs.The table order contains also the field PaymentID.I have made a new list boc called ListInvoices.
    I have built the following function, based on the field PaymentID :

    Public Function CancelInvoices()
    Dim StrSQL As String
    strSQL = "DELETE * FROM Orders WHERE PaymentID = " & frm!ListInvoices & ";"
    DoCmd.RunSQL strSQL
    strSQL = "DELETE * FROM [order details] WHERE orderID = " & frm!ListInvoices & ";"
    DoCmd.RunSQL strSQL
    End Function

    This function does not work, it does not delete. I can see that the reason is that do not refer to the field OrderID, because this is the linking field.Also,the field
    PaymentID does not exist in the table Order Details and therefore my second line makes no sense to me.If i can find a way to refer to this field also, may be my function might work.
    I will be very grateful if somebody will help me.

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

    Re: Deletion problems (Access 2000)

    Since records in Order Details belong to records in the Orders table, you must delete records in Order Details before you delete records in Orders. You could use something like

    strSQL = "DELETE * FROM [Order Details] WHERE OrderID In (SELECT OrderID FROM Orders WHERE PaymentID = " & frm!ListInvoices & ")"

    to delete the records from Order Details whose OrderID corresponds to a record in Orders with the selected PaymentID.

Posting Permissions

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