Timestamp Updated Records

[DanChard]

Hello all.

I've got a form which is bound to a table. I've just added a "date_last_modified" column to the table which I intend to update every time a record is changed.

I decided that the best way to do this would be to compare the original record (probably via opening a record set on the underlying table) to the values displayed on the form whenever the user tries to navigate away from the current record. If they're different then I'll know that the record has been changed and can update the date_last_modified column. If they're the same I'll know that no changes have been made and so won't update the date_last_modified.

Hope this makes sense so far. The main point of this post is to confirm that when changes are made on a bound form, they don't get changed in the underlying table until the user navigates away from the current record. It seems that this is how it works which is ideal for my intended purpose as it enables it to work - otherwise I'll have to think of something else!

However, if I'm wrong about this or if anyone can see any obvious flaws in my approach then I'd be grateful to know of them and any possible alternatives!

Thanks in advance,
Dan

 

[jgoodman00]

The simplest way of doing this is probably to use the BeforeUpdate_Event.

Add a hidden control, which is bound to the Date_last_modified, & then in the before event update this column with the latest date.

The BeforeUpdate event should only fire if something is changed & is very useful...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[DanChard]

Hello and thanks for your suggestion.

I don't want to update the date_last_modified field EVERY time the beforeupdate event is triggered because it may be the case that something is 'changed' (be entering a field, typing an extra character and then deleting it) but the value remains the same.

However, I am still using the beforeupdate event to check the current values back against the values in the database and then update the date_last_modified if they are different. The only trouble I am having is with null values but I should be able to deal with them seperately.

Thanks again,
Dan

 

[OhioSteve]

If we are going to tell, the computer "when an update occurs do X" then we need to define "update". Usually that means ANY change to a record. Your personal definition of "update" seems to be more narrow. You say that updates occur when "the value" changes. Do you mean when one particular field changes? Or is the value a calculated variable?

Here are some more thoughts on your project. You say that the table is bound to the form. Remember that that automates many processes. If you don't use binding, you will be able to control what happens much more easily. I might create a macro to do the update, instead of relying on binding. I might also have an invisible field on the form with "now()" as its control source. When the macro ran, it would update all of the fields. It would update the timestamp field with the invisible field's value.