Microsoft Access - Time stamp and user stamp a table field

Asked By Sheri Havens on 14-May-14 04:26 PM

I know how to format a table field and put in the =Now() for a current date and time to automatically go in that field.

On the "Default Value" line in the Design View for the "current date" field I have the formula:   =Now()


I need to do the same type of function to automatically put in the user name – the person logged into the system. Is there a way to pull that “system information” and just get “username”?

I would need to put what formula on the "user name" field on the "Default Value" line?

Pat Hartman replied to Sheri Havens on 30-Oct-14 11:10 AM

There is no function you can use to default to a user name.  When setting defaults on a table, those defaults are applied by the database engine which is completely separate from Access and VBA and so only valid SQL functions can be used.  Remember the Jet/ACE tables can be used by any other application that supports ODBC and Access doesn't even need to be installed since Jet/ACE are actually separate products.

In my applications, all tables have three additional columns.  ChangeBy, ChangeDT, CreateDT

CreateDT defaults to Now() but the other two are set in the BeforeUpdate event of each form that updates the table.

Me.ChangeBy = Environ("User Name")

Me.ChangeDT = Now()

The Environ("User Name") gets you the name of the person logged in to the computer.  Or, if you have some security in your application, you would use the application login rather than the Environ() variable.