Loop

[David]

I would like to have the ability to delete a customer and
all his records from the customer list Form.
Can anyone help me finding out what's wrong with this code.
-------------------------------------------------------
Option Compare Database

------------
Private Sub DeleteCustomer(strCustomerID As String)

Dim rs As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset

strSQL = "SELECT * FROM tblCustomer WHERE CustomerID = '"
& strCustomerID & "'"
rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly

Do While Not rs.EOF
cnn.Execute "Delete * from tblCustomerOrdetDetail where
CustomerID = '" & rs!CustomerID
cnn.Execute "DELETE * from tblCustomerOrder WHERE OrderID
= " & rs!OrderID
rs.MoveNext
Loop

cnn.Execute "DELETE * from tblCustomerOrder WHERE
CustomerID = '" & rs!CustomerID

cnn.Execute "DELETE * from tblCustomer WHERE CustomerID
= '" & rs!CustomerID & "'"

clearFields
MsgBox "Costomer Deleted"

End Sub
-----------------------------------
Private Sub cmdDeleteButton_Click()

If Nz(CustomerID) = "" Then
MsgBox "You have to select a Customer first."
Exit Sub
End If

strMSG = "If you delete the Customers Record, all the
Order Records" & vbCrLf & _
"will also be deleted." & vbCrLf & vbCrLf & _
"Are you sure you want to delete the Customers Record?"
If MsgBox(strMSG, vbYesNo + vbDefaultButton2, "Delete
Customer Record") = vbYes Then
DeleteCustomer Nz(CustomerID)
End If
End Sub

 

[Douglas J. Steele]

What's it doing (or not doing)?

Why not set up referential integrity, and let Cascade Deletes take care of
it for you?

 

[David]

Can you please help me with this, and give me an example
how to do it?
Or can you tell me where I can look up this?

Thanks

-----Original Message-----
What's it doing (or not doing)?

Why not set up referential integrity, and let Cascade Deletes take care of
it for you?

code.

 

[Douglas J. Steele]

See whether the information at
http://msdn.microsoft.com/library/en-us/off2000/html/acconwhatisreferentiali
ntegritys.asp

is enough to get you going. (Watch for word-wrap in the URL: it should be a
single line, ending with .asp)