Are you aware that you can use cascading deletes for this purpose?
1. Open the Relationships window (Tools menu).
2. If there is no relationship between these 2 tables, create one by
dragging Orders.OrderID onto [Order Details].OrderID. If there is a
relationship, double-click the line joining the 2 tables.
3. Check the boxes for Referential Integrity, and Cascading Deletes.
Now when you delete an order, the details are automatically deleted also.
In general, use cascading deletes with caution. For example, you do not want
cascading deletes on the relation between Customers and Orders. That would
be too dangerous: some user thinks they are helping by deleting an old
customer, but you would then also lose the history of sales.
If you don't like the idea of cascading deletes, you could genericize your
function so it deletes the desired records:
Public Function KillRecords(strTable As String, _
strKeyField As String, lngKeyValue As Long) As Long
Dim strSql As String
strSQL = "DELETE FROM " & strTable & _
" WHERE " & strKeyField & " = " & lngKeyValue & ";"
With dbEngine(0)(0)
.Execute strSQL, dbFailOnError
KillRecords = .RecordsAffected
End With
End Function
Then call it like this:
Call KillRecords("[order details]", "OrderID", frm!ListOrders)
Call KillRecords("Orders]", "OrderID", frm!ListOrders)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
"Peter yeshew" <fr*******@abv.bg> wrote in message
news:3f***********************@news.frii.net...
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.
