Stamp Date Modified After Record in Form is Modified

[zach9208]

I am using Access 2013 and have tried everything to get this to work. I just want a record to be stamped with a modified date after any part of the record is changed. I cannot figure out why this isn't working.

I created a new field called Modified On with Date/Time format in table1. I then created a form based on table1. I then used the property sheet to set both an Event Procedure/Macro to the Before Update and neither worked.

Any ideas or is there something I am missing. Is this a bug in Access 2013?


https://support.office.com/en-us/ar...modified-0c46efc5-5b43-4751-b3a8-c246505af66d
https://www.techrepublic.com/blog/m...-the-date-and-time-of-the-last-record-update/

 

[xenou]

Hi, that should work. Unless you have a particular reason to use macros, the event procedure method is simpler but does require that vba code be allowed to run (it is possible for vba code to be disabled or enabled).

Perhaps you could be more specific about what you mean by "didn't work". Did nothing happen? Did you get an error message?

 

[zach9208]

So when I have the form open, I can run the macro manually and it will populate the date on the current record I am viewing. However, the Before Update part is not working. In other words, when I change a field to a new value on a record the macro does not run/update the MOD_DT field.

Macro (ie. Last Modified)

SetValue
Item = [Forms]![4-tblCurrentSalesForecastAdjustments]![MOD_DT]
Expression = Date()


Before Update: Last Modified

Hi, that should work. Unless you have a particular reason to use macros, the event procedure method is simpler but does require that vba code be allowed to run (it is possible for vba code to be disabled or enabled).

Perhaps you could be more specific about what you mean by "didn't work". Did nothing happen? Did you get an error message?

 

[james_lankford]

.. when I change a field to a new value on a record the macro does not run..

I'm not experienced with macros, so I don't know if that should even work

I would just get rid of the macro and set the field's value in the BeforeUpdate event with plain VBA code

 

[zach9208]

I have tried doing that but it doesn't seem to work. See images here.

https://ibb.co/jENXCk
https://ibb.co/mqa2Ck

I'm not experienced with macros, so I don't know if that should even work

I would just get rid of the macro and set the field's value in the BeforeUpdate event with plain VBA code

 

[james_lankford]

try
me.MOD_DT.value = today()

 

[xenou]

Yes, you need to use Today() or actually I think Now() to get a timestamp - that is, a DATE with a TIME.
Nothing will change when you change a field only. You need to commit the entire record to the database. You really shouldn't see anything at all unless you go back to the record after it is committed.

Also looking at your images shows you did not do as the URL link showed you to do:
http://www.techrepublic.com/blog/mi...-the-date-and-time-of-the-last-record-update/

You created a field before update event, not a form before update event. It doesn't really help to have a field before update event, since no one should even be trying to update a field that is supposed to be automatically updated. You don't even need to have this field visible on the form during adds/edits - no reason for the user to see it since it's not something the user will enter data into.

 

[zach9208]

Didn't work either.

try
me.MOD_DT.value = today()

 

[zach9208]

I have followed this techrepublic article already on an earlier attempt and it did not work. My screenshots were from my other experimental attempts not from when I followed the tech republic article.

What do you mean committed? After I make a change to a record (ie. change the name for a field in a record), I always click the next record button and return back and see no changes to the MOD_DT.

Tried Techrepublic's suggestion again and it still does nothing after I change a record's name.
https://ibb.co/jsotnk

Yes, you need to use Today() or actually I think Now() to get a timestamp - that is, a DATE with a TIME.
Nothing will change when you change a field only. You need to commit the entire record to the database. You really shouldn't see anything at all unless you go back to the record after it is committed.

Also looking at your images shows you did not do as the URL link showed you to do:
Let Access keep track of the date and time of the last record update - TechRepublic

You created a field before update event, not a form before update event. It doesn't really help to have a field before update event, since no one should even be trying to update a field that is supposed to be automatically updated. You don't even need to have this field visible on the form during adds/edits - no reason for the user to see it since it's not something the user will enter data into.

 

[zach9208]

In addition, I tried removing the Mod_DT field from the form... but it still does not update the MOD_DT in the actual data table.