Deleting a record on a subform datasheet--no Events play?!?

Question

(Acc2016 .mdb on Win10Pro x64)

I have a subform datasheet and I want to update a control on the parent form after everything that changes the records the subform is displaying. Form opened, added record, changed contents of record, deleted record, datasheet contents change to track a record change in the linked parent form.

Code in the Load and AfterUpdate events seems to handle the form opened, added record, and changed contents of record cases.

The only way to react to the datasheet contents change to track a record change in the linked parent form case seems to be the Current event. (That's sub-optimal for sure because what I need to do is relatively costly--a sum query--and I don't have any need to do it whenever the user just navigates about in records in the subform datasheet. But I will if I have to.

What I can't seem to find any events that fire when I select an entire record on the datasheet subform and delete it--clicking in the record selector and pressing Del. Not AfterUpdate, not Current, not AfterDelete, not BeforeDelConfirm, not Delete. And not a bunch of others. Can't remember all the events I've tried. They don't fire. My test is to load up the code with all kinds of constructs like this:

            

Private Sub Form_Delete(Cancel As Integer)

    Const MyEvent = "Delete"
    Static x As Long

    Debug.Print MyEvent & " fired " & x

    x = x + 1

End Sub

And then look in the output.

What am I missing? Are events in a subform datasheet just broken? Or am I just clueless? Thanks in advance for any advice!

Answer 1

Figured out where I was going wrong on most of this. I was creating the event code in VBA from the pulldowns, but forgot to set the form properties to find it. Sigh.

Still would like to know if there's a better event to catch the data sheet filter changes driven from the parent linked field than Current. I tried ApplyFilter, but it fires before the filter changes not after. (And is documented that way.)

Answer 2

>>>I tried ApplyFilter, but it fires before the filter changes not after.

According to your description, the actions in the ApplyFilter macro or event procedure occur before the filter is applied or removed; or after the Advanced Filter/Sort, Filter By Form, or Server Filter By Form window is closed, but before the form is redisplayed. The criteria you've entered in the newly created filter are available to the ApplyFilter macro or event procedure as the setting of the Filter or ServerFilter property.

For more information, click here to refer about Form.ApplyFilter Event (Access)

In addition could you provide screenshot, that will help us reproduce and resolve it.

Thanks for your understanding.

Answer 3

I think you are missing the point of what's left of my original question here.

It's a simple case. 

A form with data, linked parent field(s) to child field(s), to related data showing in a subform. When you navigate records on the parent form, the subform records successively filter according to the defined parent:child field relationship.

With me so far? If not, you might want to read https://support.office.com/en-us/article/Create-a-form-that-contains-a-subform-a-one-to-many-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b.

So, now that this is all working, I need to add an event handler to the subform that fires reliably when these changes of records displayed in the subform happen because the parent form has changed its linked parent field contents. I can't find any event besides the much too frequent OnCurrent event.

What subform event can I use to call my code when these "subform records displayed" changes occur?

Answer 4

Hi Dick Watson,

According to you description, I have made a sample, and reproduced this issue, unfortunately I am not able to find good solution to overcome this issue through my searching. 

So I suggest that you could submit any feedback to Access UserVoice:

https://access.uservoice.com/

Thanks for your understanding.