Record the date a record was last updated via VBA

[AccessNubee]

Access 2003

Table name = Members
Primary Key name = CID (a number)
Field name = LastUpdate (date/time Now )

Every time a record is updated/changed, it will always reference the CID
when we enter a payment, history event , update addresses etc.... so....
whenever a record is updated , I want to record the date/time. I want to add
a public function in the common module so I can reference it when the record
is saved via my "Save" buttons.

I found the following code and changed the names to match my stuff but I'm
not sure if this is what I need:
________________________________________
Public Function cmdLastRecordUpdate()

If Not IsNull(Me.CID) Then
Set rs = CurrentDb().OpenRecordset("Members", dbOpenDynaset)
With rs
.FindFirst "[CID] = 'Me.CID'" ' Find the record that was
just edited -PLEASE CHECK THE SYNTAX
.Edit 'Save the date and time in the LastUpdate field.
![LastUpdate] = Now
.Update
End With
rs.Close
End If
Set rs = Nothing

End Function
___________________________________

~OR~ should I just create yet another separate table to record the CID and
LastUpdate? I know This would probably be easier but I would prefer to keep
the LastUpdate info on the Members table.

 

[Allen Browne]

You don't need to open a recordset: the info is right there in the form.

You do need to use the form's BeforeUpdate event, since that's the *only*
way to capture every possible way the save could happen (e.g. closing form,
applying filter, pressing Shift+Enter, and so on.)

Just add this line to each form's BeforeUpdate event procedure:

Private Sub Form_BeforeUpate(Cancel As Integer)
Me.[LastUpdate] = Now()
End Sub

 

[AccessNubee]

OK.. that definately sounds much easier. I'll give it a try.
Thanks Allen!

You don't need to open a recordset: the info is right there in the form.

You do need to use the form's BeforeUpdate event, since that's the *only*
way to capture every possible way the save could happen (e.g. closing
form, applying filter, pressing Shift+Enter, and so on.)

Just add this line to each form's BeforeUpdate event procedure:

Private Sub Form_BeforeUpate(Cancel As Integer)
Me.[LastUpdate] = Now()
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2003

Table name = Members
Primary Key name = CID (a number)
Field name = LastUpdate (date/time Now )

Every time a record is updated/changed, it will always reference the CID
when we enter a payment, history event , update addresses etc.... so....
whenever a record is updated , I want to record the date/time. I want to
add a public function in the common module so I can reference it when the
record is saved via my "Save" buttons.

I found the following code and changed the names to match my stuff but
I'm not sure if this is what I need:
________________________________________
Public Function cmdLastRecordUpdate()

If Not IsNull(Me.CID) Then
Set rs = CurrentDb().OpenRecordset("Members", dbOpenDynaset)
With rs
.FindFirst "[CID] = 'Me.CID'" ' Find the record that was
just edited -PLEASE CHECK THE SYNTAX
.Edit 'Save the date and time in the LastUpdate
field.
![LastUpdate] = Now
.Update
End With
rs.Close
End If
Set rs = Nothing

End Function
___________________________________

~OR~ should I just create yet another separate table to record the CID
and LastUpdate? I know This would probably be easier but I would prefer
to keep the LastUpdate info on the Members table.