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

    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
    8,795
    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 offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,459
    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
    286
    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
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    622
    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, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



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

  7. #7
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    622
    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, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+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 - Senior Forums