Solved Insert Edit date and time to a form

[Zhang]

hi,
I have a form for edit the records, I need to insert the edit date/time if any of the controls on a form changed.

any suggestion

 

[Isaac]

You could maintain a field in your table DateEdited, and assign the associated form control a Now() value, in the form's BeforeUpdate event.

 

[Zhang]

You could maintain a field in your table DateEdited, and assign the associated form control a Now() value, in the form's BeforeUpdate event.

I have fields in the table for dateedited and edited by, but I need to catch the time if the data changed if in a main form or subform.

 

[theDBguy]

I have fields in the table for dateedited and edited by, but I need to catch the time if the data changed if in a main form or subform.

Hi. As already suggested, using the BeforeUpdate event should work, but I use a data macro for something like that.

 

[jdraw]

It is the tables that your form and subform are bound to that get the update/edit info recorded.
As has been said, the BeforeUpdate event --or a data macro as DBG has just suggested, should do what you are asking.

 

[Zhang]

It is the tables that your form and subform are bound to that get the update/edit info recorded.
As has been said, the BeforeUpdate event --or a data macro as DBG has just suggested, should do what you are asking.

I used this lines in before update event

If Me.NewRecord = False Then
Me.Enteredby.Value = CMAID
Debug.Print CMAID
Me.DateEntered = Now
End If

but it doesn't work as expected, if i inserted the code on form load event it works but creates a new record
any suggestion

 

[June7]

Shouldn't matter if it is a new record or not. And since code is in form BeforeUpdate event, form must be dirty otherwise the event would not trigger.

What do you mean by "doesn't work as expected"? What happens - error message, wrong result, nothing?

 

[Gasman]

I used this lines in before update event

If Me.NewRecord = False Then
Me.Enteredby.Value = CMAID
Debug.Print CMAID
Me.DateEntered = Now
End If

but it doesn't work as expected, if i inserted the code on form load event it works but creates a new record
any suggestion

You were advised to put the code in the form's BeforeUpdate event, and you put it in the form's Load event?????

This is what I have in some of my forms

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

 

[Zhang]

You were advised to put the code in the form's BeforeUpdate event, and you put it in the form's Load event?????

This is what I have in some of my forms

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

I put the the code on before update in the Edit form
and I put it in the form load event in the form of adding new records

 

[June7]

Why have 2 forms? Regardless, use BeforeUpdate event for both.

 

[Zhang]

Why have 2 forms? Regardless, use BeforeUpdate event for both.

When I use the code on BeforeUpdate event in the form for addition the timestamp doesn't updated till the form being dirty

 

[June7]

Right, form should be dirty as soon as user enters data to any control. If you use code when form loads then a record edit is initiated. If user changes mind and closes form, you still have a record created unless you have button and code for user to cancel without saving.