Change Date

[Guest]

I would like to include a date field in a table that inputs todays date/time
upon creation of the record or if one of the fields have been edited.

 

[Allen Browne]

You can record the record creation date by adding a date/time field to your
table, and setting its Default Value property (lower pane in table design)
to:
=Date()

If you wanted the time as well as the date, use:
=Now()

To record the date when the record changed, you must make changes through a
form. Use the form's BeforeUpdate event procedure to put the date and time
into the field:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[NameOfYourFieldHere] = Now()
End Sub

 

[John Vinson]

I would like to include a date field in a table that inputs todays date/time
upon creation of the record or if one of the fields have been edited.

This cannot be done at the Table level, since Access doesn't have
table triggers.

What you can do is to force all data updating to be done using a Form
(a good idea in any case!). In the Form's BeforeUpdate event write
code to set the timestamp field. Say you have a field WhenEdited:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<put any record validation code first>
<if the record is accepted...>
Me!WhenEdited = Now
End Sub

John W. Vinson[MVP]

 

[peregenem]

To record the date when the record changed, you must make changes through a
form. Use the form's BeforeUpdate event procedure to put the date and time
into the field

That's very restrictive i.e. remove other permissions from the t.able
and allow only one UI element in one front end application to modify
it. The OP could do your form thing then add a constraint at the data
engine level to ensure the data is always kept current e.g.

ALTER TABLE MyTable
ADD CONSTRAINT NameOfYourFieldHere_must_be_current
CHECK (NameOfYourFieldHere = NOW())