Access: Timestamp new records

[gheyman]

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

 

[xenou]

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.

 

[welshgasman]

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?

 

[gheyman]

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

 

[xenou]

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:

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).

 

[welshgasman]

If that is what you are doing then you might just as well use

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/do...eference/statements/option-explicit-statement

 

[welshgasman]

Were you perhaps trying to do something like this?

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.