Problem Deleting records from VBA code

MikeAngelastro

Registered User.
Local time
Today, 16:01
Joined
Mar 3, 2000
Messages
254
Hi,

I am having a problem deleting records from a detail or line-item table. I have a PO table and a PO_Detail table. There are times when I must remove a PO from the tables and I am using the following code:

Private Sub RemovePO(PO_No As String)
'Remove this PO from the tables.
DoCmd.SetWarnings False

SQL = "DELETE * FROM PurchaseOrderDetail WHERE PO_Number = " & QUOTES & PO_No & QUOTES & ";"
DoCmd.RunSQL (SQL)

SQL = "DELETE * FROM PurchaseOrder WHERE PO_Number = " & QUOTES & PO_No & QUOTES & ";"
DoCmd.RunSQL (SQL)

DoCmd.SetWarnings True
End Sub

QUOTES = """" (This is my way of handling quotation marks in code generated SQL and it has always worked.)


The resulting SQL statements are per the immediate window:

?sql
DELETE * FROM PurchaseOrder WHERE PO_Number = "888880009";
DELETE * FROM PurchaseOrderDetail WHERE PO_Number = "888880009";

Problem: The first one works but the second one doesn't. The PurchaseOrder table has one record per PO.
The PurchaseOrderDetail table has one or more records per PO.

I am using Access 2002 with Windows XP.

Any ideas?

Thanks,

Mike
 
Thanks Rich,

No, I have not set the cascade deletes. I thought of it and I may have to do that as a quick solution in this case. But I do not like to use cascade deletes because I think they are very risky. If I relate the tables, I will cascade updates but never deletes. But this would then make it necessary only to delete children before parents.

At any rate, we can't escape the fact that the code I am using should work. Is there anythng that anyone knows that could cause it to not work?

Mike
 
You have two SQL statements with the same name and one DoCmd.
Try DoCmd.RunSQL SQL1
DoCmd.RunSQL SQL2
then look up the dbExecute method, I believe it's more efficient
 
Thanks again Rich,

I tried using the SQL1 and SQL2 and the result did not change.

I also tried to look up "dbExecute" in Access help, but Access help could not find it. I couldn't find it in the object browser either. Is it one of those undocumented items? If so, how do I find it?

Thanks again,

Mike
 
Update.

I went ahead and related the two tables but without cascading deletes. My code then worked. Anyone know why? It seems to me that the code should work either way. Is this MS's subtle way of making developers relate tables. I know of many Access developers who never relate tables because they think it is a pain.

Thanks,

Mike
 

Users who are viewing this thread

Back
Top Bottom