auto date - whenever a record changes

[Guest]

I want a date field that automatically changes whenever any field in a record
changes.

How do I do this?

 

[Allen Browne]

Access has no triggers, so cannot do this at the engine level.

However, if all changes are made through a form, you can use the
BeforeUpdate event of the form to write the date and time into your field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![NameOfYourDateTimeFieldHere] = Now()
End Sub

 

[Guest]

In the afterupdate event of your form enter:

Me.Yourdatefield = date()

HTH

Rico

 

[Allen Browne]

Rico, better to use Form_BeforeUpdate than Form_AfterUpdate.

There is no point dirtying the record again as soon as it saves, so that it
must save again, which again dirties it so it has to save again, which again
dirties it ...

 

[Guest]

You also might consider using a history table that records all changes
instead of last one. Link history table to your key field. If using logons
you can capture who done it.

Access has no triggers, so cannot do this at the engine level.

However, if all changes are made through a form, you can use the
BeforeUpdate event of the form to write the date and time into your field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![NameOfYourDateTimeFieldHere] = Now()
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want a date field that automatically changes whenever any field in a
record
changes.

How do I do this?

 

[Allen Browne]

Like this one, Karl:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You also might consider using a history table that records all changes
instead of last one. Link history table to your key field. If using
logons
you can capture who done it.

Access has no triggers, so cannot do this at the engine level.

However, if all changes are made through a form, you can use the
BeforeUpdate event of the form to write the date and time into your
field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![NameOfYourDateTimeFieldHere] = Now()
End Sub


"(e-mail address removed)"
<[email protected]>
wrote in message

I want a date field that automatically changes whenever any field in a
record
changes.

 

[peregenem]

Access has no triggers, so cannot do this at the engine level.

However, if all changes are made through a form, you can use the
BeforeUpdate event of the form to write the date and time into your field

At the data engine level, you can and should enforce the requirement
for the date to be updated when the data in the row is updated, whether
using forms, Access or otherwise

ALTER TABLE NameOfYourTableHere ADD CONSTRAINT date_must_change CHECK
(NameOfYourDateTimeFieldHere = NOW())