Microsoft Access - Track who and when records are created and updated

I'm using Microsoft Access for a project for the first time in over a decade.  The functionality is not all that different from the 2007 version I used then.  Creating tables, queries, forms and reports came back to me quickly.

The devil is, as they say, in the details.


There are a couple things I wanted my Access application to have :

• Track each records date and time created, and who created it
• Track each records date and time updated, and who updated it

Figuring out how to do each of those things took some research and effort to figure out. 

I wanted to share in make research for others a bit easier.

This post assumes a basic understanding of Access and assumes you know how to create a table, use the Form Wizard to create a form, switch between design and standard views.

We'll also delve some into Visual Basic for Applications some, but you do need to know how to program in it, beyond typing in some short code bits.

I may have some other Access tips in the near future. Check back.
First, we create a basic table.

The fields to note here are :

• DateTimeCreated
• DateTimeUpdated
• Created by
• Updated by

and note too their data types.

I switch to DataSheet View for the table, and choose from the Table menu, Before Change.

This lets me create a macro invoked before each change of a record in this table.

I choose SetField as the first action, the field to DateTimeUpdated and the value to the function Now().

Then I do an IF test to see if DateTimeCreated has a date in it already.

If it does not, I know it is a newly created record, and I put today's date in it again using SetField the Now() function.

I make sure to Save and Close the macro when finished.

In DataSheet view I create a new record.

A few seconds later I went in and changed the phone number. note the different times.







That takes care of the Date and Time recorded for when the record is created and again each time it is updated.

Now on to Created by and Updated by.

We need to create a simple function in Visual Basic.

Once in Visual Basic, I create a new Module to hold the function we are gong to write.

Type in the function above. Basically, it returns the currently logged in user's name.

For those wanting to cut and paste :

Function getUser()
     getUser = Environ("username")
End Function

If you are the only person running the Access application tracking who created and updated a record is not that useful, But if you are using the application at the office, where several people are updating the same backend database, this is of great value.

Afterwards, be sure to Save the code.

Use the Form Wizard to create a basic form from Contacts table.

Select the Created by field on the form in Design View.  In the Properties section on the right of the screen, click on the Event tab, and then on the line for Default Value, click on the button on the right.

From the dialog box that appears, choose Functions to expand the tree, then the name of the Database (in this case Database 12).  Notice in the Expressions Category there is only one, Module1.  And in Expression Values one only, GetUser(), our function from Visual Basic.  Double click GetUser so it appears in the top box, and click the OK button.

Now the default value on the form for Created by for a new record is the currently logged in user.

Next, right click somewhere on the form and choose Form Properties from the menu.

Click on the Event tab, and on the On Dirty line, click the button on the right.

In the dialog box that appears, click Code Builder.

In the On Dirty sub, type the code below :

Now each time data changes on the form, the field Updated By is updated to be the currently logged in user.

Below is a record created and updated (albeit by the same person since I'm not doing this in a multi-user environment).

One last note: select the four fields, and set their Enabled field to No, and their Locked field to Yes.  That way only the VBA code can update the fields, a user cannot.