Date, Time, and Users

By Dan Blank


The question always seems to keep reappearing -how to get a date to automatically fill in a date field when a record is created, along with how to get a date to change when a record is changed.

Here is what I do with almost all of my tables. In almost every table I create, I add six fields to the table.

User_E, (for user entered)
User_C, (for user changed)
Date_E, (for date entered)
Date_C (for date changed)
Time_E (for time entered)
and Time_C (for time changed)

This gives me a traceable event of who created the record, and when they created the record. It also lets me know who last changed the record and when they changed the record.

To implement this I have everyone log on through a log on window. There logon name is then stored in a variable called vLogin. I set all of this up with field rules.

For User E in field rules click on the Date Entry tab. Go to Default mode. Select Simple default expression. Enter the variable var->vLogin. Check the box Set default value at start of data entry.

For User_C in Field Rules click on the Field Types tab. Make this a calculated field. In the calculated field expression type var->vLogin.

For Date_E in Field Rules click on Data Entry. Go to Default mode. Select Simple default expression. Enter date(). Check the box Set default value at start of data entry.

For Date_C in Field Rules click on the Field Types tab. Make this a calculated field. In the calculated field expression type date().

For Time_E in Field Rules click on Data Entry. Go to Default mode. Select Simple default expression. Enter Time(). Check the box Set default value at start of data entry.

For Time_C in Field Rules click on the Field Types tab. Make this a calculated field. In the calculated field expression type time().

Save the Field Rules. Now I have a traceable event that will record the Login name, date and time, every time a new record is created. The Login name, date, and time will also reflect the last time the record was changed.

Of course there are other ways of doing this, I just find this a simple yet effective way to see who is creating and changing records.

See the attached demo file for a working example. Login with your own name. You must have Alpha Five => v5 or v6 to use this demo.

Back To Tips, Guidelines, Articles