How to create date modified field that changes when form is modifi

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to Access. In FileMaker it was very easy to include a field that
recorded the date each record was modified. I cannot figure out how to do it
with Access.

I assume it has something to do with the "After Update" form property, using

[datemodified] = date()

But I cannot get anything to work.

Which "Builder" do I use: Expression, Macro, or Code.

Usually, I do not get an error message. It just doesn't work.


I am using MS Access 2007.

Please let me know which discussion group is relevant, if this one is not.
Thanks.
 
I am new to Access. In FileMaker it was very easy to include a field
that recorded the date each record was modified. I cannot figure out
how to do it with Access.
Click to expand...

I suggest you to you a timestamp field in the table you wanto to audit: SQL
Server will keep it updated for you :-)
 
Assuming you have a field in your table called [datemodified] and it is in
the form, you can use the form's OnDirty event with an [EventProcedure] (code)
to set the value of the field:

Me.[datemodified] = Date()

You can also use Now() for more precision.
I am new to Access. In FileMaker it was very easy to include a field that
recorded the date each record was modified. I cannot figure out how to do it
with Access.

I assume it has something to do with the "After Update" form property, using

[datemodified] = date()

But I cannot get anything to work.

Which "Builder" do I use: Expression, Macro, or Code.

Usually, I do not get an error message. It just doesn't work.

I am using MS Access 2007.

Please let me know which discussion group is relevant, if this one is not.
Thanks.
Click to expand...
 
Assuming you have a field in your table called [datemodified] and it
is in
the form, you can use the form's OnDirty event with an
[EventProcedure] (code)
to set the value of the field:
Me.[datemodified] = Date()

You can also use Now() for more precision.
Click to expand...

I disagree: what about the case where you place a modification then cancel
it? The OnDirty event is fired anyway, but no changes are made.
If you just want to use a client-side approach, you should use the Form_BeforeUpdate
event.

Anyway, I continue suggest to demand this problem to the database server.
 
For a solution executing on the server side, you must use a trigger on
insert and update; something like:

AFTER TRIGGER dbo.AddChgDate
ON dbo.TableTest
FOR INSERT, UPDATE
AS

IF ( @@ROWCOUNT = 0 )
RETURN

UPDATE TableTest
SET ChangeDate = getdate()
FROM TableTest t JOIN inserted i ON t.PKColumn = i.PKColumn

replacing PKColumn with the primary key for the table. Of course, the
Insert trigger can be replaced with a default value for the field in the
table but there might be interference when using a Recordset that will
contains the field ChangeDate.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


PBsoft said:
Assuming you have a field in your table called [datemodified] and it
is in
the form, you can use the form's OnDirty event with an
[EventProcedure] (code)
to set the value of the field:
Me.[datemodified] = Date()

You can also use Now() for more precision.
Click to expand...

I disagree: what about the case where you place a modification then cancel
it? The OnDirty event is fired anyway, but no changes are made.
If you just want to use a client-side approach, you should use the
Form_BeforeUpdate event.

Anyway, I continue suggest to demand this problem to the database server.
Click to expand...
 
Thanks for your reply.

This sounds promising, but I still do not know how to accomplish this.

I assume I start by opening the table in Design mode and add a new date field.

What is the next step?

northwardspirit
 
This sounds promising, but I still do not know how to accomplish this.
I assume I start by opening the table in Design mode and add a new
date field.
Click to expand...

You cannot do it via Access graphic interface.
You can use some VBA code to execute this SQL code:

ALTER TABLE [schema].

ADD [columnname] [timestamp] NULL
GO

Or, you can use another tool like SQL Server Management Studio
 
Back
Top