Which event triggers (if any) when you paste data in a MS-Access datasheet-subform?

TicketMan
TicketMan used Ask the Experts™
on
Hi Experts,

Is there any event that triggers when you paste data (records from an Excel spreadsheet) into a MS-Access datasheet-subform? Currently the user has to click  a button (in MS-Access) to process the data after he it has been pasted into a subform. I would like to eliminate that button (as it can be forgotten) and have the code behind that button work from the moment data has been pasted into the subform.

As far as I know, a subform has only 2 event triggers: 'On-Enter' and 'On-Exit'. Neither one of them trigger when I paste data in the subform.

Any ideas?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Assuming your sub form is actually a form and not a query embedded as a sub-form..

I think you would have to put the event handler code into the sub-form itself, not in the parent form that contains the sub-form.

If you open the sub-form in design mode (you have to close the parent form first),  then you should see a bigger range of events.

Author

Commented:
Thanks Paul.
Yes, the subform is actually a form on itself. I looked into its own range of events (good thinking!), and tried the "AfterUpdate" of the subform but that doesn't do it.

Author

Commented:
The event "DataChange" doesn't trigger either when I paste data into the subform....
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
neither does the event "DataSetChange" trigger...
Perhaps these events don't trigger because the subform is in datasheet view?
Most of the events will work on a per-record basis.
The problem with a paste is that it can copy several records and the events will fire for each of them.

Is this the problem you encountered with AfterUpdate?

If so, your problem is in knowing when a paste has completed i.e. all records in the paste buffer have been transferred.

I am not aware of a method for doing this - sorry - maybe someone else on this forum has an idea?
Hi - found the following link
http://www.pcreview.co.uk/forums/after-update-event-does-not-fire-pasting-multiple-rows-intosubform-datasheet-view-t3915108.html

This suggests that events do not fire when pasting in data which sounds like a show stopper for you.

Author

Commented:
Just realised, the subform in question isn't really a subform (my mistake not spotting this). Although it was created once in this project,  the Source Object of the so called subform-datasheet within the main form is set directly to the underlying table instead (not via another subform), hence the lack of event triggers to use here...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial