Insert Date/Update Date

[Dale and Sandi Brown]

I need to know if I create two fields in a table how I can have them filled
out automatically if possible. I want to create an insert date and an update
field for a table. Is there a way to have these populated automatically?

 

[John Vinson]

I need to know if I create two fields in a table how I can have them filled
out automatically if possible. I want to create an insert date and an update
field for a table. Is there a way to have these populated automatically?

The insert date is easy: use a Date/Time field and set its Default
property to

=Date()

or, if you want the exact date and time, to =Now().

Update date takes a bit more work. You must ensure (using database
security, or supervisory directive) that ALL data updating is done
*only* using a Form; on that Form's BeforeUpdate event put code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
<any form validation code goes here>
<if the record is valid then...>
Me!txtDateUpdated = Date ' or = Now for date & time
End Sub

John W. Vinson[MVP]

 

[peregenem]

You must ensure (using database
security, or supervisory directive) that ALL data updating is done
*only* using a Form

Good point but I fear effective 'database security' may require more
planning than most people are prepared to put into the job and
'supervisory directive' may not prevent non-malicious (e.g. accidental)
loss of data integrity. Also, tying a potentially useful database to
just one front end application may not benefit the enterprise and you
may end up with a higher 'supervisory directive' decreeing, "Let them
use Excel."

A quick, simple and very effective fix is to use a constraint (CHECK or
Validation rule) at the data engine level to ensure the column is kept
up to date e.g.

ALTER TABLE Payroll
ADD edited_date DATETIME DEFAULT NOW() NOT NULL
;
UPDATE Payroll
SET edited_date = NOW()
;
ALTER TABLE Payroll
ADD CHECK (edited_date = NOW())
;