Posted on 2012-09-14
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 47

    Accepted Solution

    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

    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 - Access MVP)
    " 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
    LVL 84
    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

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

    Did not receive adequate response.   Found answer online.
    LVL 84
    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now