Results 1 to 9 of 9

Thread: Delete child records with parent record

  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    163

    Delete child records with parent record

    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.
    Code:
    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

  2. #2
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    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.

  3. #3
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    163
    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.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    11,426
    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).
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    Quote Originally Posted by jpkeller55 View Post
    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.

  6. #6
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    163
    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
    Code:
    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:
    Code:
    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!

  7. #7
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,696
    Quote Originally Posted by jpkeller55 View Post
    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.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    11,426
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    163
    Thanks guys...I appreciate your insights. Take it easy.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Child/Parent relationship
    By ErnieS in forum Forms
    Replies: 1
    Last Post: 08-20-2010, 12:22 PM
  2. Calculation using Child and Parent
    By SamanthaSamuels in forum Reports
    Replies: 3
    Last Post: 08-17-2010, 10:07 AM
  3. Replies: 0
    Last Post: 05-26-2010, 04:46 PM
  4. Parent/Child Append Question
    By davidson12 in forum Access
    Replies: 5
    Last Post: 02-16-2010, 09:09 AM
  5. Parent/Child subform
    By Jerry8989 in forum Forms
    Replies: 1
    Last Post: 09-18-2009, 06:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums