Access: Timestamp new records

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,720
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:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,630
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
May 25, 2013
Messages
779
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

Well-known Member
Joined
Nov 14, 2005
Messages
1,720
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
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,630
Office Version
2013
Platform
Windows
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).
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
779
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,328
Messages
5,449,739
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top