ACC: Macro to Enter Date When a Record Was Last Modified

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

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

MORE INFORMATION

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. This example uses the Customers table in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

1. Add the following field to the Customers table:
   

   
         Table: Customers
         ------------------------
         Field Name: DateModified
            Data Type: Date/Time 




2. Create the following macro called LastModified:
   

   
         Macro Name            Action
         ------------------------------
         LastModified          SetValue
   
         LastModified Actions
         --------------------
         Item: [DateModified]
         Expression: Date() 

   
   NOTE: There is no equal sign (=) in front of the Date() function. If you put an equal sign in front of the Date() function, the macro will always put the date 12/30/99 into the [DateModified] control even though the macro step window shows the correct date in the argument section.



3. Create a form called Modify Customers based on the Customers table. Add a few of the fields to the form as follows:
   

   
         Form Name: Modify Customers
         ---------------------------
         ControlSource: DateModified
         ControlSource: CustomerID
         ControlSource: CompanyName
         ControlSource: Address 

   
   NOTE: In versions 1.x and 2.0, there is a space in the Customer ID and Company Name field names.



4. Enter the macro name, LastModified, into the BeforeUpdate property of the Modify Customers form. Switch the form to Form view. Note that any time you modify the record, the current date is placed in the DateModified control.

Keywords          : kbusage McrActn 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: March 19, 1999