Papa_Bear1
Member
- Local time
- Today, 10:19
- Joined
- Feb 28, 2020
- Messages
- 53
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.
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...
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.
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...