Solved How to trigger code by pasting records into a subform

[Bennet]

Hi there,

Does anyone have a reliable method of getting Access to run code in response to the user pasting some records into a subform?

After_update and After_insert do technically work, but they cause the code to run after the insertion of each individual record. So it runs 100 times if you paste 100 records. This is not desirable.

I have also tried some messing around with timers and counting the number of records once every second to look for changes. I do not like this approach much. It sort of works, but it causes the code to run immediately after the paste but BEFORE the user has clicked the little confirmation message that Access automatically generates which prompts the user to confirm the paste. It needs to only run once they've committed to the paste!

I also tried hiding a textbox in the header of the form which counts the records and looking for changes in that number. This works fine for detecting deletion of records, but doesn't detect new ones being pasted in.....

I'm sure there must be a simple (probably well known) solution for this, but I haven't found it yet.

Many thanks!

 

[Gasman]

Never pasted any records myself, seems laborious.
I would just import them in some way.

 

[GPGeorge]

Hi there,

Does anyone have a reliable method of getting Access to run code in response to the user pasting some records into a subform?

After_update and After_insert do technically work, but they cause the code to run after the insertion of each individual record. So it runs 100 times if you paste 100 records. This is not desirable.

I have also tried some messing around with timers and counting the number of records once every second to look for changes. I do not like this approach much. It sort of works, but it causes the code to run immediately after the paste but BEFORE the user has clicked the little confirmation message that Access automatically generates which prompts the user to confirm the paste. It needs to only run once they've committed to the paste!

I also tried hiding a textbox in the header of the form which counts the records and looking for changes in that number. This works fine for detecting deletion of records, but doesn't detect new ones being pasted in.....

I'm sure there must be a simple (probably well known) solution for this, but I haven't found it yet.

Many thanks!

One of the most frustrating tasks I was ever asked to do was run code when pasting records into a subform. Depending on how the paste was implemented, it worked as required in some instances, but not in some instances.

After billing a lot of hours to the client, we got it to work more or less as they wanted. I would not want to repeat that effort, even if it did pay pretty well.

Given that memory, I would first ask if being able to copy and paste blocks of records from an external source (i.e. a csv or xlsx file) is integral to the work flow. Is the ROI enough to justify the effort? Can you not import the records another way?

 

[Bennet]

Is the ROI enough to justify the effort? Can you not import the records another way?

It's not that there is no other possible means of getting the data in, but a quick copy paste is surely the most obvious and easy way from a user's point of view.

Never pasted any records myself, seems laborious.
I would just import them in some way.

I'm surprised. I would estimate that 80% of the Access applications I've written in the last 10 years involved some functionality for the user to quickly get data into the database by copying it from Excel into a subform. Is that not an extremely common thing? We are not talking about large data volumes. In this particular case, it's for small datasets that the application is going to do a bit of analysis on.

I've sometimes wished I could get the interface to react immediately following a paste. Just to show some stats immediately on the main form about what the user just put in.

TBH, I assumed this would be a common-ish question and that some smart implementation of the right events would do the trick.

 

[GPGeorge]

I should probably add that the way you select the target for pasting matters. It's been too long to recall the details, but what I do remember is that, if the user starts by selecting all of the columns in the datasheet in the subform, one paste behavior occurs. If the user starts by selecting a row, the paste behavior is different. How you control that or allow for it is the secret to getting it to work, IIRC.

 

[GPGeorge]

Spoiler:

It's not that there is no other possible means of getting the data in, but a quick copy paste is surely the most obvious and easy way from a user's point of view.



I'm surprised. I would estimate that 80% of the Access applications I've written in the last 10 years involved some functionality for the user to quickly get data into the database by copying it from Excel into a subform. Is that not an extremely common thing? We are not talking about large data volumes. In this particular case, it's for small datasets that the application is going to do a bit of analysis on.

I've often wished I could get the interface to provide some response for whatever reason immediately following a paste.

TBH, I assumed this would be a common-ish question and that

some smart implementation of the right events would do the trick.

And that's probably why it's not all that common. It usually requires coding to get it to work consistently. Instead of being an out-of-the-box thing, it requires clever coding to make it smooth and consistent. If the convenience to your users is worth the effort, it's possible.

 

[LarryE]

What is normal is to use an APPEND insert action query to add records all at one time, not copy them.

 

[GPGeorge]

I've sometimes wished I could get the interface to react immediately following a paste. Just to show some stats immediately on the main form about what the user just put in.

TBH, I assumed this would be a common-ish question and that some smart implementation of the right events would do the trick.

Thanks, Larry. You've actually stimulated me to think about that long ago process in more detail. I eliminated all old client data and code three years after I retired, so I can't refer to specifics.

What I recall is that I ended up pasting data into a datasheet bound to a temp table, not the destination table. That allowed the paste action to be bog simple, with no events triggered. Then, I could apply the transform code to manipulate the data in the temp table as required to get the end result the client wanted. It also allowed a do over if the original paste action happened not to go as planned.

 

[sonic8]

After_update and After_insert do technically work, but they cause the code to run after the insertion of each individual record. So it runs 100 times if you paste 100 records. This is not desirable.

I would use the After_Insert event to store the time of the insert and then start a timer. The timer proc checks whether there was an insert and if at least 1 second passed since the last insert. Then run the actual worker code you want to execute after the pasting of the records.

 

[The_Doc_Man]

After_update and After_insert do technically work, but they cause the code to run after the insertion of each individual record. So it runs 100 times if you paste 100 records. This is not desirable.

This is because Access HAS to insert records one at a time, particularly if ANY of the fields are indexed. The graphic interface does nearly EVERYTHING piecemeal because bound forms are oriented towards individual record operations. You don't see it this way, but this behavior IS desirable. Your immediate problem is that you are going through a form which has events that - because they are on a form - are ALSO oriented towards individual record operations. That form has a single underlying recordset. It has to do whatever it does through the "funnel" represented by that singular recordset, and recordset operations are done by individual .AddNew/.Update operations one record at a time.

GPGeorge's suggestion is one solution - import to a different table (implying a different form that doesn't have active events). Then process the records into the table via code or query.

Another thought is that you can provide a method of importing a spreadsheet. Just copy the selected input to a blank spreadsheet and import that. Yes, it sounds like it will be clunky. (Yes, it will be.) But the code exists to perform the implied loop without events because you would not be going through a form to do it.

The issue on my mind here is data quality assurance. You usually want to be able to screen out bad data when doing a bulk import. Doing a bulk copy/paste pretty much guarantees that quality control is done before it even starts UNLESS your event code fires before each record insertion so that you can impose quality control that way.

Note also that using George's suggestion has a minor drawback relating to potential bloating, because repeated imports to a table followed by erasing the table's content will lead to eventual bloating. There are solutions for this problem but I'd rather not go into that until you decide how you want to approach the problem. The only thing I'll say is that letting users do copy/paste operations is probably the LEAST secure and controlled method you could POSSIBLY pick.

 

[Bennet]

What is normal is to use an APPEND insert action query to add records all at one time, not copy them.

Does this action follow on from the user copying the data out of Excel? As in, copy from Excel, then programmatically read the contents of the clipboard into a recordset, then append them into the target table?

 

[Bennet]

Thanks for all the input.

For the benefit of anyone reading this in the future, What I have gone with (and it seems to be doing the job absolutely fine) is this:

On the subform:

Private Sub Form_AfterDelConfirm(Status As Integer)
Forms!FrmMainForm.Form.TimerInterval = 1000
End Sub


Private Sub Form_AfterUpdate()
DoCmd.SetWarnings False
If Me.Dirty Then Me.Dirty = False
Forms!FrmMainForm.Form.TimerInterval = 1000

End Sub

On the main form:

Private Sub Form_Timer()

    Do something here - whatever you want to happen after the paste
    Me.Form.TimerInterval = 0
    DoCmd.SetWarnings True

End Sub

The key to unlocking this is DoCmd.SetWarnings False in the AfterUpdate event which takes effect just in time to prevent the usual confirmation message popping up when the user pastes their data. One second later, warnings are turned back on by the Form_Timer event.

If the confirmation message comes up, it ruins everything because your event starts firing and the changes haven't actually been committed to the underlying table.

Lastly, doing this on AfterUpdate instead of After_Insert means the code also reacts to changes caused by any other manual interaction the user may make such as directly typing data into the subform.

Lastly, data quality was mentioned. Not a massive issue in my case since all my users are Excel savvy, and coachable with that sort of thing.

Thanks again for all input.

 

[The_Doc_Man]

OK, Bennet, but I'll place a warning to benefit other potential new members with a similar problem.

Bennet's choice is based on his belief that data quality is not a problem and he wants to suppress warnings for this putative action. Be aware that during program development, this solution will make you miss crucial information that could tell you why your program isn't working as planned.

This works for Bennet but please understand that this is a singular solution and unique to his situation.

 

[Bennet]

If it helps at all, despite disabling warnings, (surprisingly, but happily) it still pops up the data compatibility warnings if you try to paste a string into a date field for example and tells you that it has dumped the results to the paste errors table

 

[LarryE]

Does this action follow on from the user copying the data out of Excel? As in, copy from Excel, then programmatically read the contents of the clipboard into a recordset, then append them into the target table?

Yes, but I use the DoCmd.TransferSpreadsheet command. Depending on your requirements, it may give you a better option than copy/paste.
You should see if it will work for you:
DoCmd.TransferSpreadsheet method (Access) | Microsoft Learn

 

[Pat Hartman]

After_update and After_insert do technically work, but they cause the code to run after the insertion of each individual record. So it runs 100 times if you paste 100 records. This is not desirable.

That is the point. But if the code is validation code, it needs to run in the BeforeUpdate event.

Is that not an extremely common thing?

No. Your users seem to be collecting data using Excel and only then importing it into the Access app. I have lots of applications that import files from other sources but they NEVER use cut/paste? Why? Because using the link to the file and use an append query, I get to validate and convert data as needed on the way in. Cut/paste doesn't allow you to do that.