Delete child records with parent record

[jpkeller55]

I have a form that has parent records (tblQuestions) with a subform of child records (tblQuestResponseSets) that has a button to allow the user to delete a parent record (a question) and write a copy of the delted record to another table. Each question has related child records (responses). I am trying to write code that will delete the child records along with the parent record when the delete command button is selected by the user. The attached code is working fine in that it is deleting the parent record and copying the parent record to the appropriate table and it is also copying the child records appropriately. What it is not doing is deleting the child records. I know I have something wrong in my code but not sure how to get it to work. Thanks for any help you may lend.

Private Sub cmdDelete_Click()
'Deletes current record from tblQuestions and saves a copy in tblDeleteQuestions
'Deletes child records from tblQuestResponseSets and saves copy in tblDeleteQuestResponseSets
On Error GoTo Err_cmdDelete_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
 
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [tblDeleteQuestions] SELECT * FROM [tblQuestions] WHERE [QuestID] = QuestIDfrm"
DoCmd.RunSQL "INSERT INTO [tblDeleteQuestResponseSets] SELECT * FROM [tblQuestResponseSets] WHERE [QuestID] = QuestIDfrm"
DoCmd.SetWarnings True
DoCmd.RunSQL "DELETE FROM [tblQuestions] WHERE [QuestID] = QuestIDfrm"
DoCmd.RunSQL "DELETE FROM [tblQuestResponseSets] WHERE [QuestID] = QuestIDfrm"
DoCmd.Close acForm, "frmQuestionDetail"
    stDocName = "frmQuestionSummary"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
'DoCmd.Requery
 
Exit_cmdDelete_Click:
    Exit Sub
 
Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
 
End Sub

[ajetrumpet]

if you're on a form where there is a field called "QuestIDfrm" then I'm guessing the actual record that you want deleted is being displayed on the form? If so, why not just utilize the CASCADE option for parent-child relate's and simply use the RUNCOMMAND and delete the form record? is that possible? you'll have to requery (or repaint) the form afterwards of course.

[jpkeller55]

Yes, you are correct. There is a field called "QuestIDfrm" on the form and the form is displaying the question (parent) with the responses on the subform (child records). I am not familiar with the CASCADE option but that sounds like what I need. I will do some research on CASCADE and will probably have to post again. Thanks for your suggestion.

[pbaldy]

You might try deleting the child records first. The parent record is gone at the point the child delete runs, which may be the source of your problem (no value on the form).

[ajetrumpet]

Yes, you are correct. There is a field called "QuestIDfrm" on the form and the form is displaying the question (parent) with the responses on the subform (child records). I am not familiar with the CASCADE option but that sounds like what I need. I will do some research on CASCADE and will probably have to post again. Thanks for your suggestion.

To set cascading deletes through the interface, go to relationships > right click on the one-to-many you want to change > CASCADE DELETE RELATED RECORDS

Paul is probably right too.

[jpkeller55]

OK, you are both right! I found the cascading deletes option on the one to many relationships and turned that on and took out the line

DoCmd.RunSQL "DELETE FROM [tblQuestResponseSets] WHERE [QuestID] = QuestIDfrm"

and tried the delete and it did what I needed it to do.

Then, I tried Paul's suggestion to delete the children first by turning the cascading deletes off and moving the code around as such:

DoCmd.RunSQL "DELETE FROM [tblQuestResponseSets] WHERE [QuestID] = QuestIDfrm"
DoCmd.RunSQL "DELETE FROM [tblQuestions] WHERE [QuestID] = QuestIDfrm"

They both worked fine with the only difference being that I got two messages from Paul's suggestion that I was deleting records and only one message with the cascade delete method.

Is either of these ways more acceptable? Thanks so much for your help. I have learned something today!

[ajetrumpet]

Is either of these ways more acceptable? Thanks so much for your help. I have learned something today!

the method that's acceptable is the one that works, ALWAYS. good luck to you.

[pbaldy]

The messages are due to the fact that you turned warnings back on before the deletes.

I'm personally not a fan of cascade deletes, but people smarter than me use them. I feel it makes it too easy for a user to accidentally delete something they shouldn't. Granted you can code around it, but I like the (perceived) safety of not setting it, and manually deleting everything when I want to. Users don't think about related records. They want to delete customer 123 because they don't use them anymore, not realizing that deleting the customer will delete all the related sales invoices (with cascade deletes on). Maybe I'm just paranoid.

[jpkeller55]

Thanks guys...I appreciate your insights. Take it easy.