Date/Timestamp for records added and updated

[Lori]

How do I design a couple of fields in my tables for
recording the date and time that a new record is added, by
what user, and also record the last time a record was
updated?

What syntax do I use to have this done automatically?

Thanks!

 

[Rebecca Riordan]

It depends on what database you're using. If you're using Jet (a standard
Access .mdb), you can't do it automatically, you have to use standard text
and datetime fields and control the updates through Forms. If you're using
SQL Server as your backend, you can build a trigger to do the updates.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Lori]

I'm using Jet. I know I have seen this before in the
table design, I just can't remember what I put in the
properties. Do you know what data I put in the
properties? Thanks

 

[Armen Stein]

I'm using Jet. I know I have seen this before in the
table design, I just can't remember what I put in the
properties. Do you know what data I put in the
properties? Thanks

Hi Lori,

The created date/time is the easy part. In the table design, add a
field (say CreateDtTm) and set it's Default property to Now(). Every
new record will be stamped with the date/time automatically, whether by
form or table.

The updated date/time is a little trickier. In each form that updates
the table, you need to add VBA code on the form's Before Update event
that sets your field (say Me.UpdateDtTm) to Now(). Unfortunately,
there's no way to do it at the table level.

Hope this helps,