Results 1 to 7 of 7
  1. #1
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295

    Recording the date after an update

    Good Day All,Would be grateful to find code that can enter the date and time of any update that is done to a table, if such a thing is practical.Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i have a log table to write to for events like this.
    usage:
    Post2Log "tEmployees", "update","user changed data", "Phone#", txtPhone


    Code:
    Public Sub Post2Log(pvEvent, pvSubEvent, pvDescr, ByVal pvField, byval pvVal )
    dim vUser
    
    vUser = Environ("Username")
    sSql = "INSERT INTO tLog ([Event],[subEvent],[USER],[EntryDate],[FieldName],[NewVal] ) values ('" &  pvEvent &  "','" & pvSubEvent & "','" & vUser & "',#" & Now() & "#,'" & pvField & "','" & pvNew & "',)"
    DoCmd.RunSQL sSql
    end sub

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    if you are looking for changes to tables from direct interaction, queries, forms etc then check out this use of data macros post 21 for sample.

  4. #4
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Thanks.
    I am seeking something simpler and similar to the automatic date created for a new record. This is done at the Table Design View Level.

    And this is not with reference to the update of any specific field. Once any field is altered in the record an updated date/time is recorded.

    But your solution is something that spurs my interest because that is more specific.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I used to have this in my relevant forms
    However that just shows the last person to update, which was all I needed.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.CreatedDate = Now()
        Me.CreatedBy = Environ("username")
    Else
        Me.AmendedDate = Now()
        Me.AmendedBy = Environ("username")
    End If
    End Sub
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Isn't that exactly what I was doing, just with VBA, as I hardly ever used macros.?

    The data macro also sounds a better idea, just they are not available in 2007.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Recording field value to a table
    By Raj Padhiar in forum Database Design
    Replies: 7
    Last Post: 10-15-2020, 05:37 PM
  2. Replies: 7
    Last Post: 09-02-2014, 08:18 AM
  3. Recording your own Macro
    By sabre1 in forum Access
    Replies: 1
    Last Post: 03-02-2011, 06:43 AM
  4. Recording Username
    By Juan4412 in forum Forms
    Replies: 9
    Last Post: 02-01-2011, 11:15 PM
  5. time recording
    By Back2Basics in forum Access
    Replies: 3
    Last Post: 02-02-2010, 08:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums