Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Date Updated' field (MS Office 2000)

    I was wondering if it is possible to have a field on a form that automatically updates itself with the date that the record was last modified. For example, if I modify it today, the date will be 02 June 2005; when I modify it tomorrow, the date will automatically change to 03 June 2005.

    Thanks for your help,

    Joe

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

    Re: 'Date Updated' field (MS Office 2000)

    Open the table in design view.
    Add a field named DateUpdated, type Date/Time.
    Specify the date format you prefer in the Format property.
    Close and save the table.

    Add code to the Before Update event of the form:

    Private Sub form_BeforeUpdate()
    Me.DateUpdated = Date
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Date Updated' field (MS Office 2000)

    Thanks Hans - you saved me again!

    How would I have to change the code for another field called "Date Created" that would show the date that the record was created (this filed would not change, obviously).

    Thanks.

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

    Re: 'Date Updated' field (MS Office 2000)

    You don't need code for that. Just set the Default Value property of the Date Created field to <code>Date()</code>

  5. #5
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Date Updated' field (MS Office 2000)

    I've used a similar code to record the "last updated" date (except I've used Now() to give the time as well). This works well under most circumstances.

    However, I also have a ListBox (lstSelect) on the form for selecting records. When I use this ListBox, I get the error, "Update or CancelUpdate without AddNew or Edit." When an item is selected in the ListBox, the ListBox's AfterUpdate event runs the following code to select the record:
    <pre> Me.RecordsetClone.FindFirst "[RecordID] = " & Me![lstSelect]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    </pre>

    The form's BeforeUpdate event is then activated, but when it tries to change the "last updated" field, it's already moved to a new record, hence the error.

    Does anyone have any idea how to avoid this problem?

    PS - I'm using Office 97 (just in case that makes any difference - I'm not after sympathy!)

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

    Re: 'Date Updated' field (MS Office 2000)

    Insert code to save the current record if necessary before moving to another one:

    If Me.Dirty = True Then
    RunCommand acCmdSaveRecord
    End If
    Me.RecordsetClone.FindFirst ...
    ...

  7. #7
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Date Updated' field (MS Office 2000)

    That did the trick.

    Thank you very much, Hans!

Posting Permissions

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