Microsoft KB Archive/209637

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

Article ID: 209637

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209637



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



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. 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.

 Start Microsoft Access and open the sample database Northwind.mdb. 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

 Save the Customers table and close it. 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. Save the macro as LastModified, and then close it. 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: <ul> DateModified</li> CustomerID</li> CompanyName</li> Address</li></ul>

</li> Save the form as ModifyCustomers.</li> Click the square to the left of the ruler on the form in Design view to select the form.</li> If the property sheet is not visible, click Properties on the View menu.</li> In the BeforeUpdate property of the ModifyCustomers form, select the LastModified macro.</li> On the View menu, click Form View.</li> 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.</li></ol>

Keywords: kbhowto kbusage KB209637

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.