Show All

Hide All

When you use a form to edit records, you can add a date and time stamp to a record by attaching a macro to the BeforeUpdate form property, or by writing an event procedure for the BeforeUpdate form event.

The following illustration shows a form for a table containing customer data. At the bottom of the form, you can see the date and time the record was last modified.

The date and time stamp is recorded when the user clicks a navigation button after editing the record. The values are stored in fields in the underlying table (for example, DateModified and TimeModified).

Recording the Date and Time When a Record Is Modified in Access

1. Add two date/time fields to the table for which you want to record date and time stamps. Name the fields DateModified and TimeModified.
2. Create a form based on the table and include the fields you want from the table, including the date and time fields. If a form already exists, add the date and time fields to the form.
3. Do one of the following:

   Attach a macro to the BeforeUpdate form property

   1. Create a macro named LastModified and set its action and item arguments as described in the following table:

      Action	Item	Expression
      SetValue	[DateModified]	Date()
      SetValue	[TimeModified]	Time()

      
      
   2. Set the BeforeUpdate property of the form to LastModified (the name of the macro).

   Attach an event procedure to the BeforeUpdate form event

   1. Open the Customer form in Design view.
   2. On the form property sheet, click the Event tab.
   3. Click the Before Update event, and then click the Build button.
   4. Double-click Code Builder to display the event procedure in the form module.
   5. Add code to the event procedure between the Sub and End Sub statements to record the date and time values in the underlying fields.

      Sample BeforeUpdate event procedure for the Customer form

      Private Sub Form_BeforeUpdate(Cancel As Integer)
          On Error GoTo BeforeUpdate_Err
      
          ' Set bound controls to system date and time.
          DateModified = Date
          TimeModified = Time()
      
      BeforeUpdate_End:
          Exit Sub
      

      BeforeUpdate_Err:
          MsgBox Err.Description, vbCritical & vbOKOnly, _
              "Error Number " & Err.Number & " Occurred"
          Resume BeforeUpdate_End
      End Sub
      

4. Switch the form to Form view.
5. Edit a record and click the Previous button.
6. Click the Next button to go back to the edited record.

   Note that the form displays the date and time you last modified the record.

For more information about programming in Access, visit the Office Developer Center on the Microsoft Developer Network (MSDN).