Posted on 2012-08-19
Last Modified: 2012-08-21
I need to incoprorate an update function in my access data base anytime an individual record is modified.   It will require time and date.   Any suggestions?
Question by:BOEING39
    LVL 65

    Assisted Solution

    by:Jim Horn
    I usually have four fields in every table that users enter data:


    For the two date fields I'll have a default value of GetDate(), and for the txt_by's I'll call a function that grabs the windows login id of the user.

    If you want any more details let me know.

    Good luck.
    LVL 77

    Expert Comment

    In an Access table Jim uses Now() rather than GetDate()  (which only works in SQL Server).
    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    As stated above, add a dtCreated or dtModified field to your table.  

    In the dtCreated, set the default value to: = Now()

    In the BeforeUpdate event of the form you are using to edit the table, add a line of code that updates the dtModified field.
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Me.txt_dtModified = Now()
    End Sub

    Open in new window

    LVL 65

    Expert Comment

    by:Jim Horn
    Both of the above comments are correct.

    Also, fyi, below is the function (although somewhat old) to get the Windows login user

    Put this in the declaraction section of any code module

    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Open in new window

    Then add this function

    Public Function fn_get_user_name() As String
    'Returns the network ID of the person that logged into the machine that is running this Access application:
    'This is not the same as the Access security user.
    'Visual Basic Source Code Library, The Waite group, Chapter 12.5
    On Error GoTo err_fn_get_user_name
    Dim str_user_name As String, lng_buffer_size As Long, lng_ret_code As Long, lng_null_character_position As Long
    str_user_name = Space(80)
    lng_buffer_size = Len(str_user_name)
    lng_ret_code = GetUserName(str_user_name, lng_buffer_size)
    lng_null_character_position = InStr(str_user_name, Chr(0))
    If lng_null_character_position > 0 Then
        str_user_name = Left(str_user_name, lng_null_character_position - 1)
        str_user_name = " "
    End If
    fn_get_user_name = UCase(str_user_name)
        Exit Function
        fn_get_user_name = "Not able to get user name."
        Resume exit_fn_get_user_name
    End Function

    Open in new window

    Then building off of fyed's code above, also set txt_modified_by in the BeforeUpdate event.
    Also add a BeforeInsert block, and set txt_created_by with ... me.txt_created_by = fn_get_user_name.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    No points wanted...

    In case your concern is "Auditing", ...see these popular links:

    Author Comment

    Thx everyone for the responses.   Ok I created a new column in my Data base "DatetimeMod" which is where I want to store the date time stamp @ update and set the default value to "Now().    This particular page is a Details view that is hyperlinked off my main page.   Can someone advise on the placement of the above code on the attached "Home3.aspx".  

    I did not use code behind on this page as it is just a details page.
    LVL 47

    Accepted Solution

    I've got no experience with asp, your original post indicates Access database, not ASP.

    Author Closing Comment

    The two responses listed were closest answers too the category I selected.   Moving question to C# as I need assistance with code behind to fully resolve.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    Suggested Solutions

    Title # Comments Views Activity
    Concatination in Sample DB 14 48
    Access DLookup 18 22
    Microsoft Access Table name 3 20
    64 bit vba timer module 2 21
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    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…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now