How do I obtain or setup a column to capture the date time a record was last modified?
Who is Participating?
Dale FyeConnect With a Mentor Commented:
What version of Access?

Prior to 2010, you would have to add a column to your table structure, then use the Form_BeforeUpdate event to set the value of that column.

Private Sub Form_BeforeUpdate(Cancel as Integer)

    me.txtLastModified = Now()

End Sub

I believe I saw something about 2010 having a macro action similar to a trigger that would allow you to do that update via a trigger type action, but I do not have any experience with that.
BOEING39Author Commented:
I am using MS access 2003.    Using the "Form_BeforeUpdate"  in access table Default Value?  
With it set to Date?/Time?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
" setup a column to capture the date time a record was last modified? "
You can't do this at the Table/Column level.  It needs to be done at the Form level as showed by fyed.

I suggest you have two columns, DateCreated and DateLastModified.  The code for that is:

Private Sub Form_BeforeUpdate(Cancel as Integer)
    If Me.NewRecord = True Then
         Me![DateCreated] = Now
         Me![DateLastModified] = Now()
    End If

End Sub
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note that you can use a Default Value of "=Now()" (without the quotes) to set the Created date, but that would not have any impact on the Modified date. You'd have to do as fyed and mx suggest for that. A Default Value is set when a record is created.
BOEING39Author Commented:
I've requested that this question be deleted for the following reason:

Did not receive adequate response.   Found answer online.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I object - the author was told this was not a built-in Access method and that they would have to add one or more columns and handle this through code or macros. The two comments here:


provide a valid solution to this issue. If the author came up with a different solution, they should post that solution here.

Also, I assume this was done to remove the automated lock from the author's account ...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.