Error Trap Deleting Records with Child Records? 1

[misscrf]

I am having some confusion about how to ensure I have my application set up normalized and that referential integrity is set right.

I was testing my application and I have a main entry form with sub form on a tab control. For a given subform record, there may be popup forms that allow entry of sub-child records (one to many records for a main form sub-record).

If I try to hit the custom delete button, to delete the sub-form record on the main entry form, I get the messsage "The DoMenuItem action was canceled."

I can figure out that if I go to the pop-up forms and delete those records, then I can go back to the subform and the delete will work.

I saw this thread thread702-1041032 but I want to do something a little different.

If there are child records, I would like to either identify those, or offer to delete them and then the record.

This is my current code:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

So the options I am looking at are

A) Trap the DoMenuItem was Canceled message, and say this record has child records that must be deleted first

or

B) Trap that error, say there are sub-records, offer to delete them with a yes/no message and if yes, delete those and then delete the child record

or

C) Should I be setting my referential integrity differently so that the deletes just happen?

I am not sure which way is best to go, or how to accomplish them. Any ideas are appreciated!!!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[jrbarnett]

You can set up cascade deletes on your referential integrity, so that deleting the master will also delete the child records. Or an alternative is to use something like:

If DCount ("*", "ChildTable", "parentID=" & me!txtID) > 0 Then
  If Msgbox ("Warning! Child records exist. Delete child as well as master?", vbYesNo) = vbYes Then

  CurrentDb.Execute "delete from childtable where parentid = " & me!txtID"
  CurrentDb.Execute "delete from mastertable where id = " & me!txtID
End If

John

 

[misscrf]

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I checked the cascade deletes and now it deletes the subrecords too!!! I think I am going to add a message to make sure they know that subrecords will get deleted as a warning, but if they hit yes, it will go through with it.

Thanks again!!!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

So I took this code:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

and changed it to this:

Private Sub cmdDelete_Click()
On Error GoTo ErrorHandler
DoCmd.SetWarnings False

If MsgBox("Deleting this record will delete any related records tied to this one.  Are you sure you want to delete this record?", vbYesNo, "WARNING - DELETION CANNOT BE UNDONE") = vbNo Then
DoCmd.CancelEvent
Else
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

ExitProcedure:
Exit Sub
ErrorHandler:

If Err = 2046 Then 'Command not available
MsgBox "No Record to Delete, canceling delete action."
ElseIf Err = 2501 Then ' Action was cancelled
Resume Next
Resume ExitProcedure
End If

    
End Sub

This traps the message that says "you are about to delete 1 record" from access, which shows up after my message. Now it doesn't.

So what I want to do now, is do a find and replace of my entire app for the first code, to replace with the second code. Any way to do this?

I would love a tip on how to do that!!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[jrbarnett]

You can turn off the "You are about to delete 1 record" with

DoCmd.SetWarnings False

and turn them back on again with

DoCmd.SetWarnings True

If you do all of your deletes via VBA you can locate the code by using Find across the entire project (rather than just current modules) looking for your DoCmd.DoMenuItem line
and paste a copy of the body in to the appropriate event handler.
You will know where the functionality is called from in your application.

John

 

[misscrf]

Thanks for the response. I ended up just doing a find for the term and jumping to each place to put in my new code. I wanted to do a bulk find and replace of the whole event, but you can only find and replace a single string. No line breaks. Anyway, it got done.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot