Last Modified Date ONLY if record is modified

[Snowflake68]

Is there a way of updating the 'LastModified' date of the current record only if the record is changed?

I have the following code on the forms 'BeforeUpdate' event which updates the current record as soon as the form is opened BUT, I only want to run the update if the record is edited. I could set the code to run on the 'After Update' event of every control on the form but think this is a bit overkill.

    [LastModified] = Format(Now(), "dd/mm/yyyy hh:nn")

 

[bob fitz]

Try your code in the form's After Update event

 

[CJ_London]

I have the following code on the forms 'BeforeUpdate' event which updates the current record as soon as the form is opened

the form beforeupdate event is not triggered until the record is about to be updated



assuming LastModified is a date field type, there is no need to format the date


LastModified=now()

is sufficient

if still having problems you can try

if me.dirty then LastModified=now()

 

[CJ_London]

@bob - isn't that too late the record has already been updated

 

[Snowflake68]

the form beforeupdate event is not triggered until the record is about to be updated



assuming LastModified is a date field type, there is no need to format the date


LastModified=now()

is sufficient

if still having problems you can try

if me.dirty then LastModified=now()

I am formatting Now() to remove the seconds as I dont want to store the seconds.

I have the code on the BeforeUpdateEvent of the form but it is still triggering and updating the record even though I havent actually updated anything, merely opened form to the record.

I have also tried the Me.dirty but still the record updates.

 

[Snowflake68]

Try your code in the form's After Update event

Thanks but I didnt use the AfterUpdate event due to the infinite loop it would get into as per the post by Pat Hartman here https://www.access-programmers.co.uk/forums/showthread.php?t=165477

 

[Snowflake68]

Just realised that I had some other code running on the 'OnOpen' event which was checking something else and updating the record unnecessarily. This was due to me copying another form and not removing this code.

All working now and the 'Before Update' event is the correct event to use.

Thanks to all for you input. Sunday nights are the best time to get things fixed

 

[Pat Hartman]

Glad you got it worked out. To avoid the issue in the future, don't modify the form before the user does. If you need to modify a "new" record, use the form's BeforeInsert event. This event runs ONCE, as soon as the user dirties any data field. If you want to modify any changed record, and you want the change to be visible immediately, use the form's On Dirty event. Again, it runs ONCE, as soon as the user dirties any data field. Changing the form with your own code in any event other than these two (or the form's BeforeUpdate) event frequently leads to saving incomplete records or user confusion if you have code that complains about bad data when stuff is missing.

 

[Snowflake68]

Glad you got it worked out. To avoid the issue in the future, don't modify the form before the user does. If you need to modify a "new" record, use the form's BeforeInsert event. This event runs ONCE, as soon as the user dirties any data field. If you want to modify any changed record, and you want the change to be visible immediately, use the form's On Dirty event. Again, it runs ONCE, as soon as the user dirties any data field. Changing the form with your own code in any event other than these two (or the form's BeforeUpdate) event frequently leads to saving incomplete records or user confusion if you have code that complains about bad data when stuff is missing.

Thanks Pat that's very useful information and I will be sure to refer to this in future.