Recording when Edits are made in Microsoft Access

/ Uncategorized / By

A common question we get from people working with Microsoft Access databases is

How can I record & audit when changes were made?

To create a detailed audit trail you would need to set up a one-to-many relationship for each table involved so that each table has a related table holding a history of edits.  Most commonly these history tables are sorted so that the most recently edited records appear at the top.

This can involve a lot of stored values if data is updated frequently, so careful consideration should be given to an archiving process.

To add the date/time stamp to a new record is actually quite easy to do in Microsoft Access using the Now() function.

  1. Open the Microsoft Access database containing the table to which you wish to add a date or time stamp.
  2. In the left window pane, double-click on the table where you would like to add a date or time stamp.
  3. Switch the table into design view by selecting Design View from the View drop-down menu in the upper left corner of the Office Ribbon.
  4. Click on the cell in the Field Name column of the first blank row of your table. Type a name for the column (such as “Record Added Date”) in that cell.
  5. Click the arrow next to the word Text in the Data Type column of the same row and choose Date/Time from the drop-down menu.
  6. In the Field Properties window pane at the bottom of the screen, type “Now()” (without the quotes) into the Default Value box.
  7. Also in the Field Properties pane, click the arrow in the cell corresponding to the Show Date Picker property and select Never from the drop-down menu.
  8. Save your database by pressing the disk icon in the upper left corner of the Access window.
  9. Verify that the new field works properly by creating a new record. Access should automatically add a timestamp to the Record Added Date field.

Tip:

The Now() function adds the current date and time to the field. Alternatively, you may use the Date() function to add the date without the time.

How to Store the date/time a record is altered in Microsoft Access Databases

That’s the easy bit! To store the modified date or time is a bit more involved.

To record the date and time that the records in a table are changed, you need to consider the following steps:

Step 1: Add fields to a table    You need a place to store the date and time before you can record it. The first step is to create fields in the table that has the records that you want to track. To capture the date and the time, create one field for the date and one field for the time. If you only want to capture one or the other, you can create just the field that you require.

Step 2: Create a macro that records the date and time    Depending on whether you want to record the date, the time, or both, the macro will have one or two actions. When you write the macro, specify only the fields that will store the date and time, not the table that contains those fields. That way, you can easily reuse the macro with other tables and forms.

Step 3: Add the macro to a data-entry form    You add the macro to the Before Update form property of the form that you use to edit records in the table. If you use other forms to edit records in the table, you can add the macro to each of them as well. That way, you can capture the date and time regardless of which form you use to edit the record.

Step 1: Add timestamp fields to a table

Create fields to store the information.Microsoft Access Table Design View

  1. Open the table in Design view.
  2. Do one or both of the following:
    • Create a field to store the date.    In the Field Name column of the design window, type Date Modified in a new row, and then select Date/Time in the Data Type column.
    • Create a field to store the time.    In the Field Name column of the design window, type Time Modified in a new row, and then select Date/Time in the Data Type column.
  3. Press CTRL+S to save your changes.

Step 2: Create a macro that records the date and time

After you create the fields, create a macro to time-stamp those fields. To make the macro reusable with other tables, refer only to the field names, not the table names.

Note: If you only want to record the date or the time, omit the macro action that you do not need.

  1. On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.
  2. On the Design tab, in the Show/Hide group, click Show All Actions.
  3. In the first empty row of the Action column, select SetValue.
  4. At the bottom of the design window, under Action Arguments, in the Item box, type [Date Modified].
  5. In the Expression box, type Date().
  6. In the first empty row of the Action column, select SetValue.
  7. At the bottom of the design window, under Action Arguments, in the Item box, type [Time Modified].
  8. In the Expression box, type Time().
  9. Press CTRL+S, and then in the Save As dialog box, type Last Modified.

Step 3: Add the fields and the macro to a data-entry form

After you create the macro, add it to each data-entry form that users will use to enter data in the relevant table.

  1. Open the data-entry form in Design view.
  2. On the Design tab, in the Tools group, click Add Existing Fields.
  3. In the Field List, under Fields available for this view, drag the Date Modified and the Time Modified fields to the form. Adjust the size and placement of the fields on the form as needed.
  4. If the property sheet is not already displayed, press F4 to display it.
  5. On the property sheet, make sure that the Selection Type is set to Form.Tip: To change the Selection Type, click the selection type you want in the drop-down list.
  6. On the property sheet, click the Event tab.
  7. On the Event tab, click the arrow in the Before Update box, and then click Last Modified.
  8. If you use multiple forms to edit records, repeat this procedure for each such form.
  9. To verify that the macro is working correctly, open the form in Form view, edit a record, and then press SHIFT+F9. The date and time that you edited the record should be displayed.

Our regular Microsoft Access Training Courses in Glasgow and Edinburgh are led by expert trainers who can give more detailed explanations on Microsoft Access features, with hands-on exercises you can try for yourself.  Closed courses can be arranged at our venues in Glasgow or Edinburgh, or at your company premises if preferred.  See the full range of training course topics at

www.eident.co.uk/training-courses/microsoft-office-training-courses/