save date change record

[Alexandersels]

In a database I want to know the when the last change of a record has been made. I know one way to do it, but is not realy efficient:

I made the folowing sub

Private Sub addtime()
Date_of_Record.Value = Now()
End Sub

I call this sub if one of the 200+ inputboxes change:

Private Sub inputbox_change
call addtime
End Sub

Is there a more efficient way to save the date the record is last modified???

thanks in advance

 

[RuralGuy]

The Form's BeforeUpdate event fires before *any* changes are made to the table/query. That is the event where your timestamp should go.

 

[applevacross]

Or here is an even better way, I just learned this the other day but the database has to have security, meaning the users must have to login first.

Create two new fields in your table ex. UpdateBy and LastUpdateDate

Next, On the form your users are accessing; create two new text boxes with the control source bound to these fields

Next, make the text boxes hidden in properties>Format>Visible>No
Then in the BeforeUpdate event of each text box enter the following code:

Private Sub Text105_BeforeUpdate(Cancel As Integer)
Me![Update By] = Application.CurrentUser
End Sub

Private Sub Text107_BeforeUpdate(Cancel As Integer)
Me![LastUpdateDate] = Now()
End Sub

Then in the BeforeUpdate event of the form itself, enter the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![Update By] = Application.CurrentUser
Me![LastUpdateDate] = Now()
End Sub

You're all set, the form will use the security setting control of the DB to find who specifically logged in and the day date and time the person updated the record

Table View Ex. UpdateBy LastUpdateDate
bsmith 8/24/2005 9:58:33 AM

Is this cool or what?

 

[Alexandersels]

Re: save date change record Reply to Thread

Thanks RuralGuy , that's just what i needed.

@ applevacross:
Good thinking, but this means that I need to get a logon for the entire company (200 people) How efficient is that, since I can just ask the updater to put in his name , since he has to give me a call to get the record into a usable database, I know who he is
I do have a security file on the database so my co-admins and I are the only ones that can put the database into design mode .