A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old December 28th, 2009, 10:36 AM posted to microsoft.public.access.tablesdbdesign
Anne
external usenet poster
 
Posts: 445
Default How do I save the date a record was last modified in Access 2003

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
  #2  
Old December 28th, 2009, 01:01 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default How do I save the date a record was last modified in Access 2003

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

--
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.


"Anne" wrote in message
...
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


  #3  
Old December 28th, 2009, 02:29 PM posted to microsoft.public.access.tablesdbdesign
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default How do I save the date a record was last modified in Access 2003

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
"Anne" wrote in message
...
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



  #4  
Old December 28th, 2009, 10:31 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default How do I save the date a record was last modified in Access 2003

"Allen Browne" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #5  
Old December 28th, 2009, 10:31 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default How do I save the date a record was last modified in Access 2003

"Al Campagna" wrote in
:

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #6  
Old December 28th, 2009, 11:41 PM posted to microsoft.public.access.tablesdbdesign
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default How do I save the date a record was last modified in Access 2003

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."


"David W. Fenton" wrote in message
36.100...
"Al Campagna" wrote in
:

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



  #7  
Old December 29th, 2009, 12:03 AM posted to microsoft.public.access.tablesdbdesign
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default How do I save the date a record was last modified in Access 2003

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 wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

  #8  
Old December 29th, 2009, 03:05 PM posted to microsoft.public.access.tablesdbdesign
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default How do I save the date a record was last modified in Access 2003

Good point/s Clifford...
Al Campagna

"Clifford Bass via AccessMonster.com" u48370@uwe wrote in message
news:a148c2c00e97c@uwe...
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 wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1



  #9  
Old December 29th, 2009, 05:52 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default How do I save the date a record was last modified in Access 2003

"Al Campagna" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #10  
Old December 29th, 2009, 05:53 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default How do I save the date a record was last modified in Access 2003

"Clifford Bass via AccessMonster.com" u48370@uwe wrote in
news:a148c2c00e97c@uwe:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.