Last Updated Field

[airmojo]

Setting a date last updated field ?

What's the best (and hopefully easy) way to automatically update a special field that contains the date (and possibly the time) of when a table's record has been updated (added or changed) ?

This date last updated would not be a user-updateable field on a form, but reside on each table's record.

I have an Access application where the main input table is imported from data on our mainframe, and I would like to keep audit trail information on each record when ever it is updated (or possibly when a specific field has been updated as well).

I know there are properties that can be set like: 'after update', 'on change', but I'm not sure what would be the easiest way to set this 'date last updated' or if I have to check each field on the form to set it. On my mainframe applications its easy to set right before a 'write' or 'rewrite' operation occurs.

Initially I would like to set this date anytime any field on the form has been changed (or added), and I know how easy it is to figure out some convoluted way of doing something that should be easy.

Thanks for your advice !

airmojo

 

[Mile-O]

As long as the LastUpdated field (or whatever you've called it) is selected in the form's RecordSource, you can simply put this line in the Form's BeforeUpdate event:

Me.LastUpdated = Now()

Of course, you'd change LastUpdated to whatever your field was called.

Better however, to have a textbox on your form (with Visible set to No) which is bound to the LastUpdated field. Give this the same name as your field but with the txt prefix. The same line applies:

Me.txtLastUpdated = Now()

 

[airmojo]

Thank you Mile-O-Phile !

I think I got it !

Is there a way to suppress (make invisible) the data field (LastUpdated) when in datasheet view ?

The Visble = No property works fine in Form view, but it still shows up in Datasheet view... No big deal, but I am curious...

Thanks again !

airmojo

 

[Mile-O]

Just don't have the control in the form view. In this case, use the first example I gave.

 

[airmojo]

Ah yes... that works nicely too !

It nice to know both ways.

Thank you very much for the help!

airmojo

 

[cos]

Hi, I fell into the same problem, however, when I do your steps, it doesn't seem to work when i change one field within the form.

however, if i was to put the code into Form_Current(), the LastUpdated field changes every time I open that form..

how can i make it, so that the LastUpdated field changes when some field on the form changes?