Solved Macro to Copy Record Before Edit

[TKaisierBHG]

Hey All,

I am an intern working in risk modeling. I have been tasked with setting up a database in access for all of our models. We need a record of all past models, and edits of current models for legal purposes. My boss asked if I could set up a way for a record to be copied to a seperate table BEFORE it is edited? Would I use a macro to do this? If so, how? Currently, I have a form set up for record entry. The idea was that the end user would use one form for both data entry and to update the records. Would I find more success in setting up a separate form? If that is the case, how would I set that up?

TLDR: I need to keep every update to a record, but for simplicity sake would like to have one table with all the current models. How do I do this?

 

[June7]

See if this is what you want http://allenbrowne.com/AppAudit.html

 

[The_Doc_Man]

First, as that was your post #1 forum-wide, hello and welcome to our forum.

Your language confuses me a little. Do you really have just a single record that somehow encapsulates a risk model in some way?

To answer the question, though, there is this issue that you would probably make copies of every record every time you merely viewed it unless you had a setup that required use of a "prepare to edit" button or something like that. The reason I am saying that is because with a form tied to a specific record set (a.k.a. "bound form"), Access is already normally in "edit" mode. Someone could just step in and start editing.

One approach is an "audit" table that could be generated when you are about to update a record. This would involve code underneath the form's "BeforeUpdate" event to show what changes were made.

Another approach is to have a form that comes up automatically with the .AllowEdits property set to FALSE. Put a button on it to COPY the current record to a NEW record. And you include a hidden (or perhaps merely highly protected) date field and maybe a "person editing" field that are not visible on the form or are otherwise locked and thus cannot themselves be edited.

Inherent in ANY scheme like this is the assumption that you don't want someone going "behind the scenes" so that means you have to secure the database. You NEVER want folks stepping around your forms if they are your protection.

To answer your more direct question, if you want to copy a record AND want to keep old records around, copy the record you are about to edit and make that data source the OLD record, then allow folks to edit the copy which becomes the NEW record.

Think about this because I don't want to confuse you until you know which way you want to go.

 

[CJ_London]

Another option is a data macro, but use with care otherwise there is a risk of creating a cascade

whichever method you use, take into account how often users will consult the history and how often it changes - that will impact whether you keep all in the same table or keep the current record in one table and the history in another.

 

[jdraw]

For info on using data macros to create an AuditLog, I submitted a sample database to demo the feature in this thread in 2017. This could be applicable in an all Access scenario.

 

[theDBguy]

Hi. Welcome to AWF!

Good luck. What they are asking you is not an easy task to do, but you came to the right place for help.

 

[TKaisierBHG]

I got it figured out. For simplicity I convinced my boss to let us keep the old models in the same table. Thank you all!