ACC2000: How to Use a Macro to Record the Date When a Record Is Modified

This article shows you how to write a macro that places the current date into a date field in a table each time that a record is modified. You can use this method when you are editing records on a form.

When you use a form to edit records, you can date-stamp the record by attaching a macro to the BeforeUpdate property of the form. To create a field that automatically records the date each time the record is modified, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. In the Database window, click Tables, select the Customers table, and then click Design to open the table in Design view. Add the following field to the Customers table:
   Field Name: DateModified
   Data Type: Date/Time
3. Save the Customers table and close it.
4. In the Database window, click Macros, and then click New to create the following macro:
   Action: SetValue
   Item: [DateModified]
   Expression: Date()
   NOTE: There is no equal sign (=) in front of the Date() function.
5. Save the macro as LastModified, and then close it.
6. In the Database window, click Forms, and then click New. Create a new form based on the Customers table. Add the following fields to the form by dragging them from the Customers field list box onto the form:
   • DateModified
   • CustomerID
   • CompanyName
   • Address
   
   
7. Save the form as ModifyCustomers.
8. Click the square to the left of the ruler on the form in Design view to select the form.
9. If the property sheet is not visible, click Properties on the View menu.
10. In the BeforeUpdate property of the ModifyCustomers form, select the LastModified macro.
11. On the View menu, click Form View.
12. Modify the address for the first record by typing any characters in the Address box. Go to the next record, and then return to the record that you just modified.
    
    Note that the current date is placed in the DateModified control.