Results 1 to 10 of 10
  1. #1
    millby2000
    Guest

    Last Update Date

    I am trying to understand how to create a field on a form that will display the current date whenever any of the other fields on the form are changed. Unfortunately, I have no VBA background and I believe using VBA is the only way to accomplish this. Any suggestions would be appreciated.

    Also, if a field in the Table were modified, would the Update date field in the related form update to show the date of modification?

    Thanks again for your help.

    Steve Metcalf

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Update Date

    Check out the "Dirty Property" in Access help.


    Good luck

    Richard Aheron
    raheron@hotmail.com
    Richard

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Last Update Date

    If you want to datestamp a record in a form to track its last updated date, use the beforeupdate event of the form to set the control's value to the Date() function. Then if the update is cancelled, the datestamp will be rolled back as well. You can do it with a bit of code like this, where txtLastUpdated is the name of the control bound to your [Last Update] field:

    <pre> If IsNull([txtLastUpdated]) Or _
    [txtLastUpdated] < Date Then
    [txtLastUpdated] = Date
    End If '[txtLastUpdated] < Date</pre>



    Fields in tables should only be modified through a form if you want to track a last updated value (actually they should only be updated in a form anyhow if you value data integrity). The form only knows about events that happen when it is open. It doesn't have any way to handle things that occur in the table itself.
    Charlotte

  4. #4
    millby2000
    Guest

    Re: Last Update Date

    Richard,

    Thanks for your response, I'll check out the "Dirty Property."

    Steve

  5. #5
    millby2000
    Guest

    Re: Last Update Date

    Charlotte,

    Thank you. I believe this is exactly what I am looking for. Now since I am such a Neophite at this, let me read your directions 10 times to make sure I understand.

    Thanks again.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Last Update Date

    This was very helpful for a similar problem of mine. My Main form has a subform. The Updated field is on the main form and works fine with changes to the main form. How do I get the Updated field to change when I have modified data on the subform?

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

    Re: Last Update Date

    In my own databases, I have a LastUpdated field in each table, so the "main" table and the "sub" table would have their own LastUpdated fields. If you only keep track of updates in the "main" table, you have no way of knowing whether it was a change in the "main" table or in the "sub" table (and in which record of the "sub" table).

    But if you prefer to use only the field in the "main" table, you can put code in the After Update event of the subform:

    Private Sub Form_AfterUpdate()
    Me.Parent!Updated = Date
    End Sub

    When used in code behond the subform, Me refers to the subform itself, and Me.Parent refers to the main form.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Last Update Date

    Thanks, Hans. I have one question. You used the AfterUpdate event while Charlotte used the BeforeUpdate event in her post (30,826) about the main form. please explain.
    Thanks,
    Howard

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

    Re: Last Update Date

    Charlotte used the Before Update event of the form to change the value of a control on the form itself. You can't use the After Update event of the form for that because that occurs when the record has already been updated - changing a value would cause the record to be modified again,
    You want to modify a value in the main form; this can safely be done after the record in the subform has been updated.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Last Update Date

    Hans, thanks for the lucid explanation.
    Howard

Posting Permissions

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