Results 1 to 7 of 7

Thread: Access: Timestamp new records
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,559
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access: Timestamp new records

    I have a table where I put a field in named "_TIME_STAMP"
    Whenever a new record is entered into the table I want to capture the date and time in this field

    Note the user is entering data into the table via a Form (Splitform).

    What is the best way to do this?


    Accesstbl_VendorQuoteData - Table
    frm_VendorQuoteData - Form
    Last edited by gheyman; May 24th, 2019 at 01:49 PM.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,346
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access: Timestamp new records

    I would try using a default value in the table. It is also possible to do it with a before save event on the FORM. But the former doesn't require any code and always works - you would just leave the field out of the form (or leave it hidden in the form if you so desire). The value then gets populated automatically when the record is inserted.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    591
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: Timestamp new records

    I would say the same as xenou re table default. Use Now() for the default.
    However I found when I wanted to do that, I also wanted to know who created it and when it was last amended and who amended it.
    Something to consider perhaps?
    Office 2007
    Access novice. Sometimes trying to give something back

  4. #4
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,559
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: Timestamp new records

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim sUserName As String
    
        UserNameID = (Environ$("Username"))
    
      If Me.NewRecord = True Then
                 Me![_TIME_STAMP] = Date
                 Me![_USERIDSTAMP] = UserNameID
             Else
                 Me![_TIME_STAMP] = Date
                 Me![_USERIDSTAMP] = UserNameID
             End If
             
    End Sub
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,346
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Access: Timestamp new records

    Hi,
    Note that since we are doing the same update for both new records (inserts) and non-new records (updates) the above is the same as:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim sUserName As String
    
        UserNameID = (Environ$("Username"))
    
    	 Me![_TIME_STAMP] = Date
    	 Me![_USERIDSTAMP] = UserNameID
             
    End Sub
    If you want to have some difference between inserts and updates, you can have two fields, one for CreatedDate and another for LastModifiedDate. Both can be handled with form code or table defaults. The former should, of course, never change, while the latter would, in such a setup, always show the timestamp of the last change (but not really a history of changes over time, so of limited but not necessarily inconsequential use).

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular
    Join Date
    May 2013
    Posts
    591
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: Timestamp new records

    If that is what you are doing then you might just as well use
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me![_TIME_STAMP] = Date
    Me![_USERIDSTAMP] = (Environ$("Username"))        
    End Sub
    You really should have Option Explicit at the top of every module.
    https://docs.microsoft.com/en-us/dot...icit-statement
    Office 2007
    Access novice. Sometimes trying to give something back

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    591
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: Timestamp new records

    Were you perhaps trying to do something like this?
    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
    For that I have separate fields on the table.
    Office 2007
    Access novice. Sometimes trying to give something back

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •