Posted on 2012-09-14
Medium Priority
Last Modified: 2012-12-22
How do I obtain or setup a column to capture the date time a record was last modified?
Question by:BOEING39
LVL 49

Accepted Solution

Dale Fye earned 1000 total points
ID: 38400945
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.

Author Comment

ID: 38401007
I am using MS access 2003.    Using the "Form_BeforeUpdate"  in access table Default Value?  
With it set to Date?/Time?
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 38401050
" 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 85
ID: 38401461
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.

Author Comment

ID: 38697500
I've requested that this question be deleted for the following reason:

Did not receive adequate response.   Found answer online.
LVL 85
ID: 38697501
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 ...

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question