Cascade Delete

[tjnichols]

Hello - I have a form with several sub forms and when one record is deleted from the main form, I would like the records under it to be deleted as well.

This is what I have but most of it comes up with errors as in it shows up red. I got this from a book and have manipulated it to suit my needs but obviously it doesn't work. I have attached my database so you can see what I want to do.

Thanks!

Private Sub cmdDelete_Click()
Dim inAnswer As Integer
Dim strSQL As String
If Me.NewRecord Then
Me.Undo
Else
intAnswer = MsgBox("Are you sure you want to delete this record? This will delete the contact as well as all of its sub records.", _vbQuestion + vbYesNo, "Delete Record")
If intAnswer = vbYes Then
'Delete Children for this record:
strSQL = "DELETE * FROM tblChildren " & _
WHERE Children.ID = " & Me.ID
CurrentProject.Connection.Executre strSQL
'Delete Classes for this record:
strSQL = "DELETE * FROM tblClasses " & _
WHERE Classes.ID = " & Me.ID"
CurrentProject.Connection.Executre strSQL
'Delete Donations for this record:
strSQL = "DELETE * FROM tblDonations " & _
WHERE Donations.ID = " & Me.ID
CurrentProject.Connection.Executre strSQL
'Delete VolunteerInfo for this record:
strSQL = "DELETE * FROM tblVolunteerInfo " & _
WHERE Volunteer.ID = " & Me.ID
CurrentProject.Connection.Executre strSQL

 

[RainLover]

You can set Cascade Delete in your Relationships.

No need for code.

Be careful using this as you may delete the wrong thing. I never use it.

 

[tjnichols]

You can set Cascade Delete in your Relationships.

No need for code.

Be careful using this as you may delete the wrong thing. I never use it.

This would have been more helpful if you told me what you do use.

 

[RainLover]

Go to your Relationship Window.

When you create your relationships there is an option for Cascade Delete.

Click on the Join Line to see it.

I assume you have set up your Relationships. If not you need to do this.

Personally I do not see a need to delete ALL the records belonging to a the Primary Table.

As far as what I do. I don't do anything. I keep the records.

Why do you feel that you have the need to delete? Most database do not have this option because you are changing history.

 

[tjnichols]

Thank you!!!!

 

[RainLover]

One thing you might want to try is to create an extra Field called Status.

Then you can mark it as Active or Inactive.

It is easy then to only show the records that are active. If you make a mistake then it could be easily reversed.