By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

deletion problems

P: n/a

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.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
"Peter yeshew" <fr*******@abv.bg> wrote in message
news:3f***********************@news.frii.net...

In addition to what Alan said you might consider wrapping all your deletes
in a transaction - so that of one fails you don't leave the database in an
incistent state.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.