Store the date and time when a record is modified

You can keep track of when records in a table are last modified, by using a form with a macro. When the form is used to modify a record, the macro stores the date and time in the table. Only the most recent modification date and time are stored.

 Tip   If you want to keep track of when records are created but do not care when they are modified, just create a field in the table to track and use the field's Default Value property to timestamp each record when it is created. For more information, see the See Also section.

In this article


Overview

To record the date and time that the records in a table are modified, you must complete 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.

Top of Page Top of Page

Before you begin

Before you begin, consider the following:

For more information, see the See Also section.

  • If your database file has an older file format and uses user-level security, you must have permissions to change the design of the table and the forms that are used to edit its records.

Top of Page Top of Page

Step 1: Add timestamp fields to a table

Create fields to store the information.

  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.

Top of Page Top of Page

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.
  1. On the Design tab, in the Show/Hide group, click Show All Actions.
  2. In the first empty row of the Action column, select SetValue.
  3. At the bottom of the design window, under Action Arguments, in the Item box, type [Date Modified].
  4. In the Expression box, type Date().
  5. In the first empty row of the Action column, select SetValue.
  6. At the bottom of the design window, under Action Arguments, in the Item box, type [Time Modified].
  7. In the Expression box, type Time().
  8. Press CTRL+S, and then in the Save As dialog box, type Last Modified.

Top of Page Top of Page

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.

  1. On the property sheet, click the Event tab.
  2. On the Event tab, click the arrow in the Before Update box, and then click Last Modified.
  3. If you use multiple forms to edit records, repeat this procedure for each such form.
  4. 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.

Top of Page Top of Page

 
 
Applies to:
Access 2007