Record Modification Dates

[CementCarver]

I have several edits forms that my client will be using to edit existing data. But what I'm looking for is to be able to record the date/time when any record gets changed.



How can this be achieved with VB? Or is there a simpler solution?

CementCarver

[dniezby]

The method I use is create a timestamp.

In the tables that you're using, create a date field. Even better, two date fields.

One that is called, timestamp and one called modified_rec.

The timestamp field is easy. In the default value you simply put, Now() and it will put the date and time that the record is entered. For modified, I would say, leave that blank and do some VB code.

Private Sub Form_Dirty()

' If the record gets edited in anyway, record the date and time.
modified_rec = Now()
End Sub

[CementCarver]

Thanks Dniezby. I'll give that a try.

CementCarver

[dniezby]

Thanks Dniezby. I'll give that a try.

CementCarver

No problem. I hope it helps. If it doesn't work where I put it in this example, I've found that finding the right trigger event changes everything. For example, if someone changes a field that you KNOW will always be updated, then put the trigger there. Let me know how it turns out.

[CementCarver]

But how do I track whether the record has been changed or not? Do I have to test every field to see if it's been changed?

CC

[dniezby]

But how do I track whether the record has been changed or not? Do I have to test every field to see if it's been changed?

CC

The OnDirty Trigger fires as soon as ANYTHING in that record is even touched.

[dniezby]

Also, forgot that you will need to format the subroutine like this:

Private Sub Form_Dirty(Cancel As Integer)
modified_rec = now()
End Sub

[Missinglinq]

Actually, the kind of code needs to be in the Form_BeforeUpdate event. This event will fire if and only if a change has been made to the Record. The user could Dirty the Record, change their mind, and end up not changing any data, but having the code in that event would indicate (wrongly) that the Record was modified! Choosing the proper event is, indeed, critical.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Not Me.NewRecord Then
   Me.modified_rec = Now()
 End If
End Sub

Linq ;0)>

[dniezby]

Actually, the kind of code needs to be in the Form_BeforeUpdate event. This event will fire if and only if a change has been made to the Record. The user could Dirty the Record, change their mind, and end up not changing any data, but having the code in that event would indicate (wrongly) that the Record was modified! Choosing the proper event is, indeed, critical.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Not Me.NewRecord Then
   Me.modified_rec = Now()
 End If
End Sub

Linq ;0)>

Also a good place to put it.

[CementCarver]

Thank you.

CementCarver

[Missinglinq]

Glad we could help!

Linq ;0)>

[dniezby]

Thank you.

CementCarver

Pretty neat little technique huh? It's amazing how somethings can be so simple and things that seem simple can be soooo difficult in programming. Damn logic.