Updating date when modifying data

[caecilie]

Hi

I want to update my date field in my database everytime when the record is changed. I do not want to do this by using a macro and I cannot make it word by using the before update function.
In the before update I have tried the following without success (nothing happens):

Private Sub Modified_Date_BeforeUpdate(Cancel As Integer)
Me![Modified Date] = Now()
End Sub


//caecilie

 

[CazB]

Have you tried me.[modified date] instead of me![modified date] ?

 

[jdraw]

Do you have a field in your table defined for this? And the Form is Bound to the table?

 

[caecilie]

me.[modified date] doesn't work either.

My field is a date field bound to a table, which already inserts the date the record is created, which works fine in the form. I then want it to change the date to the modified date when the record is modified. There is no need for saving the other dates, so the field should just be updated.

 

[contractor]

you could use on change event for each field.

for example :

Private Sub txtField_Change()
    Me.[Modified Date] = Now()
End Sub

 

[David R]

Better to use Form_BeforeUpdate... which, come to think of it, is probably why the OP doesn't work. It's trying to run code to modify Modified Date when Modified Date is modified!

caecille: Click the box in the top left to get the Form properties. Go to the Event tab, find Before Update, and paste the code snippet there. Then try it (you'll also want to remove the code behind Modified_Date_BeforeUpdate or things might try to get stuck in a loop).

 

[contractor]

Hi David,

Your advice is better then mine

 

[David R]

Your code is good if you only want to track when/if a particular field is modified (for instance you care when Status is changed, but not Middle Initial).