Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date stamp updated record (2003)

    I've got a default value of Date() for a field to indicate when the record was created. How can I date stamp this field when it is updated? Which event do I need to use? How could I revert this field value to the previous one if someone Escapes out of it? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date stamp updated record (2003)

    I'd use two date/time fields: one to hold the date/time the record was created, and another one to hold the date/time the record was last updated. You'd use the Before Update event of the form used to enter/edit records to fill the latter field (named dtmLastUpdated in this example)

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.dtmLastUpdated = Now
    End Sub

    Now returns the current date and time; if you prefer to store the date only, use Date instead of Now.

    When the user modifies values of controls, then presses Esc once or twice to cancel these changes, the dtmLastUpdated field will not be changed. However, if the user moves to another record after modifying controls, dtmLastUpdated will be changed. If he/she then uses Edit | Undo or Ctrl+Z to undo the changes in the previous record, dtmLastUpdated will change again. It would be very complicated to get around that.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date stamp updated record (2003)

    That's great, thanks. However, I know discover that I need to store the updated value only when certain of the records fields are updated. Would this make it very much more complicated? Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date stamp updated record (2003)

    In that case, remove the Form_BeforeUpdate event procedure, and use the After Update event for each of the controls that should update the LastUpdated field:

    Private Sub ThisField_AfterUpdate()
    Me.dtmLastUpdated = Now
    End Sub

    Private Sub ThatField_AfterUpdate()
    Me.dtmLastUpdated = Now
    End Sub

    ...

    dtmLastUpdated will be updated each time the user tabs or clicks out of one of these controls after modifying its value, but the value will only be stored in the table when the record as a whole is saved; if the user cancels all modifications to the record by pressing Esc twice, the updated value dtmUpdated will not be stored.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date stamp updated record (2003)

    Thank you v much. Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •