AfterDelete Event Wanted

M

MikeC

I have an AXP form with a set of indicators that show, at
a quick glance, which subforms contain records. These
indicators get refreshed each time the user navigates from
one parent record to the next or whenever a new child
record is added.

The indicators also need to be refreshed whenever a child
record is *deleted*, but AXP has no "AfterDelete" event.
I've tried using the "On Delete" event, but this event is
raised *before* the record is physically deleted, so my
indicators continue to see the child record and remain
unchanged.

I would like to define my own "AfterDelete" event, but I'm
having difficulty defining a generic method for detecting
a record deletion. Does anyone have any ideas?
 
A

Allen Browne

There is an AfterDelConfirm event.
It has a Status argument, which will be acDeleteOk unless the delete was
cancelled.

There is actually a bug in Office 2002 Service Pack 3 that gives a "no
current record" error after a delete. Not sure if this will affect you.
 
D

Dirk Goldgar

MikeC said:
I have an AXP form with a set of indicators that show, at
a quick glance, which subforms contain records. These
indicators get refreshed each time the user navigates from
one parent record to the next or whenever a new child
record is added.

The indicators also need to be refreshed whenever a child
record is *deleted*, but AXP has no "AfterDelete" event.
I've tried using the "On Delete" event, but this event is
raised *before* the record is physically deleted, so my
indicators continue to see the child record and remain
unchanged.

I would like to define my own "AfterDelete" event, but I'm
having difficulty defining a generic method for detecting
a record deletion. Does anyone have any ideas?
Click to expand...

Though Allen Browne has answered your question by suggesting the
AfterDelConfirm event (and you can use the Delete event to count the number
of records being confirmed), I wonder if you really need to do this at all.
What's wrong with putting a text box on your main form with a controlsource
like this:

=[SubformName].[Form].[Recordset].[RecordCount]

(where "SubformName" is the name of the subform control on the main form).
In my tests, it keeps track of the number of records on the subform without
any code. I haven't used it extensively in practice, but it seems worth
pursuing.
 
M

MikeC

Thanks. That's also a good idea, but...

I should have also mentioned that I had thought of that
idea too, but this event is triggered only if the "Confirm
Document Deletions" setting is checked in Options. I was
trying to avoid having to control the Options settings on
the various users' machines. I know I can do this
programmatically, but I would rather leave their settings
alone.
 
M

MikeC

Actually I'm doing something very similar, but when you
delete a record, something has to tell the indicator to
refresh itself so that it notices the change in
[SubformName].[Form].[Recordset].[RecordCount] or
whatever. There can be up to one child record per
subform, so I'm checking the child record ID as opposed to
the record count.

Perhaps some code will better illustrate the scenario:

I have been running this small code fragment in the Main
form's *Current* event:

With Me
!lblGIS.BackColor = fnIndicatorColor
(.GISsubform.Form.GIS_PRJ_ID)
!lblAdmin.BackColor = fnIndicatorColor
(.AdminSubform.Form.ADM_PRJ_ID)
!lblGPS.BackColor = fnIndicatorColor
(.GPSsubform.Form.GPS_PRJ_ID)
!lblTOPO.BackColor = fnIndicatorColor
(.TOPOsubform.Form.TOP_PRJ_ID)
!lblCADD.BackColor = fnIndicatorColor
(.CADDsubform.Form.CAD_PRJ_ID)
!lblROW.BackColor = fnIndicatorColor
(.ROWsubform.Form.ROW_PRJ_ID)
End With

Here's the *fnIndicatorColor* function code:

Public Function fnIndicatorColor(varChildID As Variant) As
Long

If Not IsNull(varChildID) Then
fnIndicatorColor = 65280 'Green
Else
fnIndicatorColor = 0 'Black
End If

The above code works fine, except when a child record is
deleted, I have no reliable trigger to requery the
indicators *after* the deletion.

The best I have been able to do is "Me.Parent.Requery" in
the subforms' *AfterInsert* events to detect additions.

Obviously, I'm missing something if I can't detect a
simple record deletion. I suppose I could use the timer
event, but I hate to do so.

As another kindof hokey resort, I'm also considering
adding an invisible record ID textbox control on each of
the subforms. I believe I can make the indicators refresh
in real time if the value in these controls changes to
null or not null. I'd rather not, but I'm running out of
ideas.

-----Original Message-----
I have an AXP form with a set of indicators that show, at
a quick glance, which subforms contain records. These
indicators get refreshed each time the user navigates from
one parent record to the next or whenever a new child
record is added.

The indicators also need to be refreshed whenever a child
record is *deleted*, but AXP has no "AfterDelete" event.
I've tried using the "On Delete" event, but this event is
raised *before* the record is physically deleted, so my
indicators continue to see the child record and remain
unchanged.

I would like to define my own "AfterDelete" event, but I'm
having difficulty defining a generic method for detecting
a record deletion. Does anyone have any ideas?
Click to expand...

Though Allen Browne has answered your question by suggesting the
AfterDelConfirm event (and you can use the Delete event to count the number
of records being confirmed), I wonder if you really need to do this at all.
What's wrong with putting a text box on your main form with a controlsource
like this:

=[SubformName].[Form].[Recordset].[RecordCount]

(where "SubformName" is the name of the subform control on the main form).
In my tests, it keeps track of the number of records on the subform without
any code. I haven't used it extensively in practice, but it seems worth
pursuing.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)



.
Click to expand...
 
D

Dirk Goldgar

MikeC said:
Actually I'm doing something very similar, but when you
delete a record, something has to tell the indicator to
refresh itself so that it notices the change in
[SubformName].[Form].[Recordset].[RecordCount] or
whatever. There can be up to one child record per
subform, so I'm checking the child record ID as opposed to
the record count.

Perhaps some code will better illustrate the scenario:

I have been running this small code fragment in the Main
form's *Current* event:

With Me
!lblGIS.BackColor = fnIndicatorColor
(.GISsubform.Form.GIS_PRJ_ID)
!lblAdmin.BackColor = fnIndicatorColor
(.AdminSubform.Form.ADM_PRJ_ID)
!lblGPS.BackColor = fnIndicatorColor
(.GPSsubform.Form.GPS_PRJ_ID)
!lblTOPO.BackColor = fnIndicatorColor
(.TOPOsubform.Form.TOP_PRJ_ID)
!lblCADD.BackColor = fnIndicatorColor
(.CADDsubform.Form.CAD_PRJ_ID)
!lblROW.BackColor = fnIndicatorColor
(.ROWsubform.Form.ROW_PRJ_ID)
End With

Here's the *fnIndicatorColor* function code:

Public Function fnIndicatorColor(varChildID As Variant) As
Long

If Not IsNull(varChildID) Then
fnIndicatorColor = 65280 'Green
Else
fnIndicatorColor = 0 'Black
End If

The above code works fine, except when a child record is
deleted, I have no reliable trigger to requery the
indicators *after* the deletion.

The best I have been able to do is "Me.Parent.Requery" in
the subforms' *AfterInsert* events to detect additions.

Obviously, I'm missing something if I can't detect a
simple record deletion. I suppose I could use the timer
event, but I hate to do so.

As another kindof hokey resort, I'm also considering
adding an invisible record ID textbox control on each of
the subforms. I believe I can make the indicators refresh
in real time if the value in these controls changes to
null or not null. I'd rather not, but I'm running out of
ideas.
Click to expand...

Thanks for the explanation; I see now what you're trying to do. Is there
any reason you aren't using Conditional Formatting for this? You can use
the "Expression Is" option, specify sn expression that tests the subform for
no records or a null ID field, and set the control's back color
appropriately.
 
M

MikeC

Thank you so much Dirk. That method works absolutely
perfectly! I didn't realize that Conditional Formatting
supported expressions. Now I do.

-----Original Message-----
Actually I'm doing something very similar, but when you
delete a record, something has to tell the indicator to
refresh itself so that it notices the change in
[SubformName].[Form].[Recordset].[RecordCount] or
whatever. There can be up to one child record per
subform, so I'm checking the child record ID as opposed to
the record count.

Perhaps some code will better illustrate the scenario:

I have been running this small code fragment in the Main
form's *Current* event:

With Me
!lblGIS.BackColor = fnIndicatorColor
(.GISsubform.Form.GIS_PRJ_ID)
!lblAdmin.BackColor = fnIndicatorColor
(.AdminSubform.Form.ADM_PRJ_ID)
!lblGPS.BackColor = fnIndicatorColor
(.GPSsubform.Form.GPS_PRJ_ID)
!lblTOPO.BackColor = fnIndicatorColor
(.TOPOsubform.Form.TOP_PRJ_ID)
!lblCADD.BackColor = fnIndicatorColor
(.CADDsubform.Form.CAD_PRJ_ID)
!lblROW.BackColor = fnIndicatorColor
(.ROWsubform.Form.ROW_PRJ_ID)
End With

Here's the *fnIndicatorColor* function code:

Public Function fnIndicatorColor(varChildID As Variant) As
Long

If Not IsNull(varChildID) Then
fnIndicatorColor = 65280 'Green
Else
fnIndicatorColor = 0 'Black
End If

The above code works fine, except when a child record is
deleted, I have no reliable trigger to requery the
indicators *after* the deletion.

The best I have been able to do is "Me.Parent.Requery" in
the subforms' *AfterInsert* events to detect additions.

Obviously, I'm missing something if I can't detect a
simple record deletion. I suppose I could use the timer
event, but I hate to do so.

As another kindof hokey resort, I'm also considering
adding an invisible record ID textbox control on each of
the subforms. I believe I can make the indicators refresh
in real time if the value in these controls changes to
null or not null. I'd rather not, but I'm running out of
ideas.
Click to expand...

Thanks for the explanation; I see now what you're trying to do. Is there
any reason you aren't using Conditional Formatting for this? You can use
the "Expression Is" option, specify sn expression that tests the subform for
no records or a null ID field, and set the control's back color
appropriately.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)



.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2007 conversion subforms don't display linked records 2
Form-Subform keep in sync with out Child/Master linked 3
Using a subform to go to a record on the parent form 2
Form_Current event and Me.NewRecord 6
Proper Event - Leaving Record? 3
Basing a subform on a selection from either of two subforms 3
'AfterDelete' event ?? 3
How to color boxes in continuous subform? 3

Top