Keeping history on timestamp.

[Guest]

Tina, again thanks for your help.

See if this is possible:

Here is the VB script:

Private Sub Form_BeforeUpdate()
Me!LastUpdated = Now()
End Sub

I would like to keep a running history of the time stamps.

Is that possible?

 

[Guest]

If you want a running history of timestamps by record, meaning when a record
is created it gets a stamp, when it is updated, it gets a stamp, now you have
2 stamps for the record, then you really need an audit table. Your first
thought might be to link the audit table to your main table, but then, you
loose all audit data when the record is deleted.

So, a common approach is to create an Audit table with these fields:
TABLE_NAME
RECORD_KEY
LAST_UPDATE
UPDATE_TYPE (Add, Change, Delete)
UPDATE_USER

Then in the After Update event of your form write code to do an INSERT each
time there is an update to your main table. Want to get real freaky? Create
a child table to that that will tell you which fields were changed and what
the old and new values were.

 

[tina]

yes, it's possible, but a bit trickier. you need a separate table to hold
the timestamps, as

tblTimeStamps
DataID (foreign key from your data table - the table that your form is bound
to)
DateTimeStamp (date/time data type. set the DefaultValue to Now())
you can use the two fields together to create a combination primary key.

your tblData will have a one-to-many relationship with tblTimeStamps.

on the form's AfterUpdate event, you can run an Append query to add a new
record to tblTimeStamps, as

INSERT INTO tblTimeStamps ( DataID )
SELECT [Forms]![MyForm]![DataID];

if you want a more sophisticated audit trail, see MVP Allen Browne's
instructions on creating an audit trail at
http://allenbrowne.com/AppAudit.html

hth

btw, it's just chance that i saw your post, since you started a new thread
rather than posting back to the previous one.