Capturing delete on Cascading Delete

[Darrell]

I must say right up front that this seems like an absurdly easy task.
But, I'm thoroughly stymied by what seems like a gaping hole in Access's
events. I have a form and subform, and on the form I am calculating and
displaying the number of records on the subform which meet certain
criteria. I need this number to refresh whenever a record on the subform
is changed to meet the criteria, is inserted, or deleted.

The deletion will often occur when the user deletes the parent record on
the form, triggering Cascading Delete on the subform records. Here is
where the rub comes. I thought I would simply execute the simple
function I'm using to count the qualifying records on the
AfterDelConfirm event of either the form or the subform. BUT, neither
event fires when Cascading Deletes are in effect, leaving me with
nothing to "hook" my recount to. The Delete event is too early since the
records have not yet actually been deleted and... the AfterDelConfirm
event never fires!

Can anybody help?

p.s. I'm using Windows XP and Access 2003.

Thanks in advance,

Darrell

 

[Allen Browne]

The main form's AfterDelConfirm event should fire.

If it's not check what settings you have under:
Tools | Options | Edit/Find \ Confirm

 

[Darrell]

The main form's AfterDelConfirm event should fire.

If it's not check what settings you have under:
Tools | Options | Edit/Find \ Confirm

Thank you, Allen, for the response. Perth Australia, eh? I never
realized that's where you hailed from! That's cool! You'll have to tell
me about it sometime. Never been there but my grandmother was born in
New Zealand (I know, a long ways away) and I've had several
acquaintances from Australia. Okay, well, I digress.

I have checked the options (which I didn't know existed, so thanks for
that) and they are all checked (Record changes, Document changes, Action
queries). One thing I did not mention because I didn't want to make it
too complicated, but maybe should have, was that this is actually a
subform and, in turn, sub subform. The actual main form is a level up
from what I called the "main" form. Would this matter?

Darrell

 

[Allen Browne]

Using a sub-subform should not be an issue.

The form where you delete the record is bound to a table or query (otherwise
you could not be deleting a record), so its AfterDelConfirm event should
fire if you have the option turned on.

 

[Darrell]

Using a sub-subform should not be an issue.

The form where you delete the record is bound to a table or query
(otherwise you could not be deleting a record), so its AfterDelConfirm
event should fire if you have the option turned on.

Allen,

I SWEAR the following code was JUST copied out of the subform's
procedure (evidenced by the fact that I had not yet moved the last two
lines into the If block yet). I got no printout in the immediate window
after deleting a record from the form and no debug when I placed a break
on the procedure line. Yet, when I went to the Properties sheet for the
form just now and checked the AfterDelConfirm event, there was no [Event
Procedure] on the line. When I clicked on the ellipses, I had to select
the Code Builder, rather than having it go directly to the procedure.
When I clicked "Code Builder," THEN it took me to the procedure and the
[Event Procedure] showed up on the property sheet. NOW it fires.

Allen, I must admit I have seen this before and should have checked it.
But, it sneaks up on me because I never know when to expect it. So, I
guess the question is now a different one. Do you have any insight into
when and why this happens?

Thank you again,

Darrell

"Private Sub Form_AfterDelConfirm(Status As Integer)
Debug.Print "PR AfterDelConfirm fired " & Status
If Status = acDeleteOK Then

End If
Call modGeneral.FindMembershipYears(Me.Parent.txtID)
Me.txtDuesCount = modGeneral.MembershipYears
End Sub"

 

[Allen Browne]

... checked the AfterDelConfirm event, there was no [Event Procedure]

Yep: we've all been caught by that one, Darrell.

Glad you got it sorted.

 

[Darrell]

... checked the AfterDelConfirm event, there was no [Event Procedure]

Yep: we've all been caught by that one, Darrell.

Glad you got it sorted.

Thanks, Allen. I appreciate it.

Darrell