Logging changes when user copies / pastes multiple records into the form gives error

[jjsande1492]

Hi, everyone.

I'm using Access 2016 and building a datasheet form where users can update the details of products they are bringing into the business. I have a simple change log / audit function built that works perfectly when a user changes one field at a time. However, if the user copies multiple fields from another source (notepad, Excel, etc.) and pastes them into the data sheet, I get an error before the audit function fires that says:

Run-time error '2474':


The expression you entered requires the control to be in the active window.

The code is:

Private Sub ACE_Part_Description_Line_3_AfterUpdate()

Call LogPartChange(Forms!frm_Main!fsub_MainSubform.Form.ActiveControl)


End Sub

I thought it might be because of the pop-up asking if the user is sure they want to paste the records, but disabling that didn't help. How can I navigate my way around this so that each pasted field gets a record in my change log table?

[jjsande1492]

Bump. Anyone have any ideas?

[Gicu]

I assume your back-end is Access. Have you looked into using data macros for your auditing needs:

https://scottgem.wordpress.com/2012/...data-macros-2/
http://www.utteraccess.com/forum/Cre...-t1991182.html

Cheers,
Vlad

[orange]

Another thread, comments and sample data macro usage here.

[jjsande1492]

Thank you for the responses. It would seem that data macros are a viable option. I'm learning about them now. I have an issue with using them though. I understand a data macro cannot have more than 999 actions in it. My table has 122 fields (please no normalisation arguments on the number of fields in my table - I'm well aware). Each field would need 10 actions to update my change log table. Some of these actions are static data (the date/time, the user making the change, etc.) and are entered regardless of the field being changed.

Can I use a data macro to look for a change in any field and input this common data to cut down on my number of actions? I tried "If Updated(*)", but it is looking for a particular field so it gave me an error. Or...

Can a data macro have variable fields? In other words, can the data macro automatically detect the changed field and insert the old and new values without me having to create actions for each individual field?