How do I save the date a record was last modified in Access 2003

[Anne]

How do I save the date a record was last modified in Access 2003?

I know a little about Access databases, and need help with a database I
designed. Any comment will be appreciated.

Anne

 

[Allen Browne]

Use the BeforeUpdate event of the *form* where the records are edited, e.g.:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[WhateverYourFieldIsCalled] = Now()
End Sub

 

[Al Campagna]

Anne,
In addition Allen's correct reply...
On my website below, I have a sample 97 and 2003 file called
Record Statistics
They each demostrate how to capture the DOC (Date of Creation) and
the DOLE (Date of Last Edit) for each record.

They also demostrate how to display the current record number and the
current recordset count. If you were looking at the 1st record of a total
of
100 records...
1 of 100 of 100
If you were to filter out 10 records...
1 of 90 of 100
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

If you filtered out

 

[David W. Fenton]

Use the BeforeUpdate event of the *form* where the records are
edited, e.g.:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[WhateverYourFieldIsCalled] = Now()
End Sub

Keep in mind that Now() returns a full date/time value with both
date and time. I generally only record the date when records are
updated (which is generally sufficient for the purposes in my apps),
using Date() instead of Now().

If the time were important, I'd probably put it in a separate
column, because it makes querying on date ranges simpler.

 

[David W. Fenton]

They each demostrate how to capture the DOC (Date of Creation)
and
the DOLE (Date of Last Edit) for each record.

Date of Creation requires only a default value, no?

 

[Al Campagna]

David,
Of course... but, I use templates with common code already
built in, so I just have the DOC code in the "canned" BeforeUpdate code.
I don't have to enter a Default for DOC...

Regarding your comment about capturing Date instead of Now, it's
usually preferable to capture Now... but just display Date format.
If an active and busy DB, which might have multiple edits within a
day... it's
better to capture Now. So I think it's more a case of... why guess whether
Date is sufficient... just do Now, and you're always OK.
--
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Al Campagna]

Good point/s Clifford...
Al Campagna

Hi,

I find setting the default values for date/time fields in Access to
Date
(), Now() or Time() to be highly inaccurate because the value is set when
the
new row is displayed, not when data is initially being entered and not
when
it is actually saved. So if I go to a new record on 12/28/2009 at 11:50
pm,
but do not actually start entering anything until 12/29/2009 at 12:15 am
and
do not actually save it until 12/29/2009 at 12:45 am, it will save with a
creation date of 12/28/2009 and a creation time of 11:50 pm. Which is
just
plain wrong. So the use of the Before Insert event to set it at the time
of
starting the record is a better solution. Likewise the use of the Before
Update event to set it at the time of saving. It would depend on what you
are defining as the creation date/time. The default values would be
useful
for situations where you are importing data; then you would not have to
set
the creation date/time explicitly. Of course, the whole issue can be
complicated further by an inaccurate computer clock. So I could be
entering
data on a computer with a different time zone setting and it would provide
that time zone's date/time. Hmmm... If you use a default value say of
Now()
on a column in a back-end database on the network, does Access use that
network computer's clock? Or the front-end computer's clock? I presume
the
front-end computer's.

For anything where it is critical to know the correct date/time, you
need to use a database system that will use your server's clock and that
will
set the values by that clock regardless of how or from where the record is
being created.

And here is another issue when the correct date/time of creation is
critical. It has to be set up so that that date/time can never be changed
once it has been set.

Things to think about,

Clifford Bass

 

[David W. Fenton]

Regarding your comment about capturing Date instead of Now,
it's
usually preferable to capture Now... but just display Date format.

It is perhaps preferable to *you* -- for me, I vastly prefer a
date-only field. Where I need the time value, I use a separate
column for the time part.

If an active and busy DB, which might have multiple edits
within a
day... it's
better to capture Now. So I think it's more a case of... why
guess whether Date is sufficient... just do Now, and you're always
OK.

But it's much harder to query date fields with time parts,
particularly if you are only ever displaying the date part.

 

[David W. Fenton]

I find setting the default values for date/time fields in Access
to Date (), Now() or Time() to be highly inaccurate because the
value is set when the new row is displayed, not when data is
initially being entered and not when it is actually saved. So if
I go to a new record on 12/28/2009 at 11:50 pm, but do not
actually start entering anything until 12/29/2009 at 12:15 am and
do not actually save it until 12/29/2009 at 12:45 am, it will save
with a creation date of 12/28/2009 and a creation time of 11:50
pm. Which is just plain wrong.

That's something I never considered.

But I still prefer the default values in the table, nonetheless.

 

[Al Campagna]

Did you read Clifford Bass's response. He makes additional good
points to consider...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Armen Stein]

It is perhaps preferable to *you* -- for me, I vastly prefer a
date-only field. Where I need the time value, I use a separate
column for the time part.

But it's much harder to query date fields with time parts,
particularly if you are only ever displaying the date part.

Hi David,

I understand your preference. However, once one gets the hang of
querying dates with time components, it's easy. The time and data
components can be extracted from one field when necessary. Date/Time
math is easier when they're in one field. And having them in one
field takes half the space.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

I understand your preference. However, once one gets the hang of
querying dates with time components, it's easy. The time and data
components can be extracted from one field when necessary.
Date/Time math is easier when they're in one field.

99% of my querying against these fields is by date, and while it's
not that hard for *me* to query with:

WHERE DateField >= #12/18/2009# AND < #12/19/2009#

....but if you have user populations who write their own queries,
this is more than I want to ask them. On the other hand, these are
not fields that they are most likely to be querying on.

Aggregation on these date fields is also more complicated, as you
have to do it on an expression (DateValue(Created)) instead of on
the base field. Again, likely not a big deal, but it could be a
performance drain on a large table. It could also lead to issues if
you're using a server back end instead of Jet/ACE -- it could force
the aggregation client-side instead of server-side unless you move
the logic for doing the aggregation to a server-side component.

In general, my apps don't need the time component at all for the
created/updated fields. About the only ones that do are logging
tables, where time really is crucial. But even in that case, I'm
mostly searching on date ranges and ordering by time for each date.
That is, the time component is used only for ordering and not for
querying.

And having them in one
field takes half the space.

This is a difference that is not in any way relevant in our modern
operating environment. The only criticism in this direction I can
see that would be significant is if you index both fields, as
maintaining two indexes could add to overhead enough to be
noticeable, or you could bump up against the 32 indexes limit.

I was just away from the computer for a moment and realized that I
have an antipathy towards Now() that comes from its frequent mis-use
in a lot of the Access apps that I've encountered and been required
to update. A field populated with Now() and with a format of Short
Date is a red flag to me -- it's not that you won't sometimes want
to omit the time component for display, it's just that if that's the
only way it's displayed, then it's being populated with the wrong
function. Another example is Invoice Dates populated with Now() --
that's quite clearly an error, in my opinion, but I encounter that
kind of thing frequently (especially in apps created a long time
ago, in particular by an odd fellow named David Fenton who back in
1997 or so wasn't all that experienced with Access and created some
really bad messes).

I think a lot of the Access sample apps and the report wizards use
Now() and then format it for display. I can sort of see this, as it
makes them more forgiving for novice users. But it also trains them
to not choose the right function for the job.

 

[Armen Stein]

On 31 Dec 2009 21:48:13 GMT, "David W. Fenton"

Hi David,

...but if you have user populations who write their own queries,
this is more than I want to ask them. On the other hand, these are
not fields that they are most likely to be querying on.

Well, we write applications where few (if any) user write their own
queries. Instead, we build forms where they can specify their own
criteria, and we handle the SQL. But I see your point if users are
rolling their own queries.

This is a difference that is not in any way relevant in our modern
operating environment.

Obviously disk drives are vast and getting more so. I often advocate
for less efficient storage if there's a good reason. But if the same
information can literally be put into a smaller space, then I would
lean toward doing so. Of course, I also don't think that handling the
time component is a big deal.

Another example is Invoice Dates populated with Now() --
that's quite clearly an error, in my opinion, but I encounter that
kind of thing frequently

I agree. It's an error to store the time component when it isn't
relevant. Almost all of our date fields have just the Date in them.
But timestamping when a record is created or changed can actually be
useful. We have a client where the boss works late. Whenever we see
activity in the evening, we know it's his fault. <s>

Armen Stein
Microsoft Access MVP
www.JStreetTech.com