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]

Removing a field from the form will probably have no effect one way or the other.
What is your current code you are using?

 

[zach9208]

Here is the newest code I tried.
https://ibb.co/jsotnk

Removing a field from the form will probably have no effect one way or the other.
What is your current code you are using?

 

[xenou]

It works for me the way it is suggested in this link:
Let Access keep track of the date and time of the last record update - TechRepublic

my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.MOD_DT.Value = Now
End Sub

I would not do that the way you have it now. You simply create a FORM before update event to set the date modified in that field. That field doesn't need to be visible on the form, or even on the form at all, as long as it's in the table and the form is bound to the table.

Note that in Post #9 where you say the tech republic method did not work - you are *still* not doing it the way recommended in that post. You have a picture of a control before update event, for the MOD_DT control. You need to create a FORM before update event.

 

[zach9208]

Thanks so much xenou! Your code worked! Appreciate your patience!

I know I did not mention this in the earlier post, but is there a way to also record the current user who last modified a record.

It works for me the way it is suggested in this link:
Let Access keep track of the date and time of the last record update - TechRepublic

my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.MOD_DT.Value = Now
End Sub

I would not do that the way you have it now. You simply create a FORM before update event to set the date modified in that field. That field doesn't need to be visible on the form, or even on the form at all, as long as it's in the table and the form is bound to the table.

Note that in Post #9 where you say the tech republic method did not work - you are *still* not doing it the way recommended in that post. You have a picture of a control before update event, for the MOD_DT control. You need to create a FORM before update event.

 

[xenou]

You do the same thing using this code:

environ("username")

for example, if you have a field called MOD_USER:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.MOD_DT.Value = Now
    Me.MOD_USER.Value = environ("username")
End Sub

That gives you the login name of the current user.

 

[zach9208]

Worked. Thanks again!

You do the same thing using this code:

environ("username")

for example, if you have a field called MOD_USER:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.MOD_DT.Value = Now
    Me.MOD_USER.Value = environ("username")
End Sub

That gives you the login name of the current user.