Cascading delete reports?

[Craig Ugoretz]

Hello,

It is certainly possible in Microsoft Access to specify that delete
operations be cascaded, i.e. if there is a relationship between tables, a
record is deleted from a table, and records in other tables depend on the
deleted record, then those records get deleted too. What if I want to report
to my database's user which records would be involved in a cascaded delete
before the delete takes place to give them a choice to continue or not? Is
there a way to do this automatically through Mircosoft Access or by "hooking
in" to internals of the program instead of having to figure out the SQL for
cascading deletes myself in order to display the affected records in a form
or report to give the user a choice?

 

[Allen Browne]

No. You do have to figure out which records and related records are being
wiped out yourself.

Part of the issue is that a user can select multiple records (typically in a
Datasheet or Continuous form), so there could be multiple sets of related
records being deleted in the cascading delete. Consequently, you need to use
both the Delete and BeforeDelConfirm events. The Delete event fires for each
record, and you have info about the record being deleted, so you can figure
out the related records at this time. The BeforeDelConfirm event fires once
for the entire process, so use it for the confirmation, but the details are
no longer available at this time.

1. In the General Declarations section of the form's module (top, with the
Option statements), declare a string variable to use for the deletion
message:
Private mstrDelMsg As String
Private mlngDelCount as Long

2. In the Delete event of the form, build up the string, e.g.:
Dim lngCount As Long
mlngDelCount = mlngDelCount + 1
If mlngDelCount < 20 Then
lngCount = DCount("*", "MyRelatedTable", "MyForeignKey = " & Me.ID))
mstrDelMsg = mstrDelMsg & "ID " & Me.[ID] & " and " & _
lngCount & " related record(s)." & vbCrLf
ElseIf mlngDelCount = 20 Then
mstrDelMsg = mstrDelMsg & "and others." & vbCrLf
End If

3. In Form_BeforeDelConfirm, use mstrDelMsg in your confirmation MsgBox, and
reset the variables (regardless of the response.) Set the Response argument
to suppress the built-in message.

(The point of mlngDelCount is to avoid a dialog that runs off the bottom of
the screen if the user selects hundreds of records to delete.)