date/time last updated field

I'd like to have a field in a table that records the date/time that the
record was last updated. I'd like this field to be automatically updated
whenever any other field in the table is changed (or a new record is created
-- that part is easy). Any way I can do that in the table? I'd prefer to do
it there so I don't have to repeat code for different forms or queries that
allow updates to the table. I'm using Access 2000, but may be able to use
Access 2003 and I'll soon be updating to Access 2007, so if there's a
solution that works with all of these versions, that would be ideal.

No. Access lacks triggers, so you cannot do that at the table level.

You can do it at the form level. Use the BeforeUpdate event procedure of the
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"KumbiaKid" wrote in message
...
I'd like to have a field in a table that records the date/time that the
record was last updated. I'd like this field to be automatically updated
whenever any other field in the table is changed (or a new record is
created
-- that part is easy). Any way I can do that in the table? I'd prefer to
do
it there so I don't have to repeat code for different forms or queries
that
allow updates to the table. I'm using Access 2000, but may be able to use
Access 2003 and I'll soon be updating to Access 2007, so if there's a
solution that works with all of these versions, that would be ideal.

On Jan 21, 9:54 am, "Allen Browne" wrote:
I'd like to have a field in a table that records the date/time that the
record was last updated. I'd like this field to be automatically updated
whenever any other field in the table is changed (or a new record is
created

can do that in the table? I'd prefer to

No. Access lacks triggers, so you cannot do that at the table level.

Did you mean to say, "Jet lacks triggers..."?

At the very least the OP should put a validation rule in the table to
ensure the 'timestamp' is maintained e.g. column-level rule

= NOW()

Jamie.

--

As Allen says, this must be done at the form level, but there is very little
code to duplicate across multiple forms. Place a control (e.g. textbox) on
each form, make its data source the timestamp field of your table. You could
make this hidden if users are not to see the timestamp - if visible, it
should be locked. The BeforeUpdate event is one line of code:
txtboxTimeStamp=Now()

For queries that update this table, insert TimeStamp=Now() into the SET
clause of each query.
--
TedMi

You are accurate, but a bit nit picky. I am sure Allen said Access to avoid
confusing the OP. You would be surprised at how many users do not understand
the distinction between Access and Jet.

Should we educate? Sure, but it takes baby steps. As in teaching music, you
don't introduce a E# min Add 9 to someone who barely understands a C major
code.
Yes, I said E#. Yes it is really F, but it is taught as a reading exercise.
--
Dave Hargis, Microsoft Access MVP


"Jamie Collins" wrote:

On Jan 21, 9:54 am, "Allen Browne" wrote:
I'd like to have a field in a table that records the date/time that the
record was last updated. I'd like this field to be automatically updated
whenever any other field in the table is changed (or a new record is
created

can do that in the table? I'd prefer to

No. Access lacks triggers, so you cannot do that at the table level.

Did you mean to say, "Jet lacks triggers..."?

At the very least the OP should put a validation rule in the table to
ensure the 'timestamp' is maintained e.g. column-level rule

= NOW()

Jamie.

--

On Jan 21, 5:08 pm, Klatuu wrote:
No. Access lacks triggers, so you cannot do that at the table level.

Did you mean to say, "Jet lacks triggers..."?

You are accurate, but a bit nit picky. I am sure Allen said Access to avoid
confusing the OP.

It's more about being specific (do you really think the OP would have
been confused by the use of the term 'Jet'?)

I considered my point worthy because in a sense Access has triggers
(i.e. Form events) whereas Jet does not.

Jamie.

--

Thanks all. Although I'm an old timer at database design, I'm very green at
this sort of coding and at application development in Access/VB etc. I've
created an event procedure for the BeforeUpdate event with the following code
(the name of the field is DateLastModified):

Private Sub Form_BeforeUpdate(Cancel As Integer)
DateLastModified = Now()
End Sub

Visual Basic started with "Option Compare Database" above the code section
-- I don't know what that's about, but presume it should be there.

This makes no change in the DateLastModified field when I make a change in
another field. What am I missing?

"tedmi" wrote:

As Allen says, this must be done at the form level, but there is very little
code to duplicate across multiple forms. Place a control (e.g. textbox) on
each form, make its data source the timestamp field of your table. You could
make this hidden if users are not to see the timestamp - if visible, it
should be locked. The BeforeUpdate event is one line of code:
txtboxTimeStamp=Now()

For queries that update this table, insert TimeStamp=Now() into the SET
clause of each query.
--
TedMi

LOVE your musical analogy! Wonder how many readers here understand enharmonics?
--
TedMi

Thanks all. Although I'm well experienced in database design, I'm really a
newbie at application development using Access/VB, etc. I've tried to code an
Event Procedure in a form using TedMi's suggestion. The textbox control is
already a part of the form. The name of the field is DateLastModified and
I've put the following code in an Event Procedure attached to the Before
Update Event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
DateLastModified = Now()
End Sub

There's a line above the code section in the VB window that says "Option
Compare Database" which I presume should be there. When I make a change in
another field in the form, no errors are generated but the DateLastModified
field is not updated. What am I missing?

"tedmi" wrote:

As Allen says, this must be done at the form level, but there is very little
code to duplicate across multiple forms. Place a control (e.g. textbox) on
each form, make its data source the timestamp field of your table. You could
make this hidden if users are not to see the timestamp - if visible, it
should be locked. The BeforeUpdate event is one line of code:
txtboxTimeStamp=Now()

For queries that update this table, insert TimeStamp=Now() into the SET
clause of each query.
--
TedMi

Thanks all. My reply posts seem to not be making it to the discussion, so I
hope this one does. I guess I was in the wrong group (should have been in
Forms Coding where I found the full solution).

"tedmi" wrote:

As Allen says, this must be done at the form level, but there is very little
code to duplicate across multiple forms. Place a control (e.g. textbox) on
each form, make its data source the timestamp field of your table. You could
make this hidden if users are not to see the timestamp - if visible, it
should be locked. The BeforeUpdate event is one line of code:
txtboxTimeStamp=Now()

For queries that update this table, insert TimeStamp=Now() into the SET
clause of each query.
--
TedMi