VBA Add a Change Date

[gheyman]

On a form I have two fields (From a Table). One is a Comments field (Long Text) and the other is a Date Field

I need an Event that whenever the Comments field is changed the Date field ("Status_Comments_Date") is changed to the current date.

Help please.

Thanks

 

[Joe4]

Assuming your field names are "Comments" and "Status_Comments_Date".
Go to the Comments field on your form, bring up Properties, go to the Event tab, and select the Code Builder option and add this VBA code:

Private Sub Comments_AfterUpdate()
'   Update Status_Comments_Date field to current date whenever Comments field is updated
    Me.Status_Comments_Date = Date
End Sub

Note that the Status_Comments_Date field must be on the Form. If you do not want it shown, just change its Visible property to "No".

 

[gheyman]

Thanks Joe! I was trying "=NOW()"

 

[Joe4]

You can use NOW() instead of DATE in my code if you want to capture a time component along with the date.

 

[stumac]

Another way would be to use the table level after update event.

If you haven't used table events before I would recommend looking into them, it means that no matter where the data is changed any form, any query or in the table itself) the event will trigger. They take a bit of getting used to, however, they are a good way to manage data integrity and audit functionality.