How to Create an Auto-Updated "LastEdited" Field (1 Viewer)

Papa_Bear1

Member
Local time
Today, 03:50
Joined
Feb 28, 2020
Messages
32
With the introduction of Data Macros (apparently 2010 and later), Access now supports something close to a database trigger.
I was very excited to finally be able to set up a Date/Time field that would automatically update to reflect the most recent edit of any field in a table.
For this to work, you will need a key field (or fields), so that the update to the field can be applied to the right record/row.

We'll call the key field fldKey, the last-edited field fldLastEdited, and assume there are any number of other fields in the table.
  • Open the design view of the table of interest
  • Look for the "Design" menu item
  • Click the "Create Data Macros" option
  • For this example, we will use the "After Update" option
  • First, we need to store they key value of the record just edited, so select "SetLocalVar" in the drop-down
    • We'll give the variable the name varKey, and we'll set the Expression to [fldKey] since that is the name of the key field in this example. (If you're typing a valid fieldname, you will see the intellisense choices appear if you pause long enough while typing...)
  • Now choose "LookUpRecord In" option in the drop-down
    • Enter [fldKey] = [varKey] - this instructs it to find the record we want to update.
  • Now choose "EditRecord" in the drop-down
  • And immediately under EditRecord, choose "SetField" in the drop-down
    • In Name, enter fldLastEdited
    • In Value, enter Now()
  • Save it and close it.
That should be it!

Now, you do not need to write a bunch of code to track when the last edit was made to any field in any record in that table, whether by direct edits in the table, or by queries, or by forms, etc. If you have more key fields, it becomes a little lengthier/messier, but the principles remain the same.

I hope this helps and that I've put this in the right place... :)
 

arnelgp

error reading drive A:
Local time
Today, 15:50
Joined
May 7, 2009
Messages
12,333
you can Skip the "LookUpRecord" portion since you are in the record being edited/added.
and use BeforeChange datamacro event.
 

Papa_Bear1

Member
Local time
Today, 03:50
Joined
Feb 28, 2020
Messages
32
you can Skip the "LookUpRecord" portion since you are in the record being edited/added.
and use BeforeChange datamacro event.
That’s cool, because I was indeed wondering why the system was designed to require the lookup for after-update. (...according to he documentation I found at least...) Obviously, it knows what record it’s on or we couldn’t store the key. I first tried this simply by setting the date field, and no joy. So the before-event doesn’t need the lookup, but the after-event does? Strange, if so. I’ll try it. Thanks!
 

Isaac

Lifelong Learner
Local time
Today, 00:50
Joined
Mar 14, 2017
Messages
4,750
Thanks for posting this, @Papa_Bear1 , it's very nice and helpful. If there was any Macro I might use, I've always thought it might be a data macro for update timestamps etc.
 

Users who are viewing this thread

Top Bottom