Macro Help

[Guest]

Hello...I have a macro that crates a date/time stamp when a record is
modified. However, the macro seems to crate the date/time stamp when a record
is even just accessed and no changes to data have been made. I would like to
see if the macro could be adjusted to create the date/time stamp only when
there is actual data changes and not if a record is just accessed or viewed.
I am assuming this could be done with an expression of sort in the
"condition" column but not quite sure how to create the expression/formula.
Any assistance will be grratly appreciated.

Here is what I have so far:
Macro:

Action Item Expression
SetValue [DateModified] Date()
SetValue [TimeModified] Time()

Both of these items only need to happen if data is modified not just viewed.

 

[Brian Smith]

Hello...I have a macro that crates a date/time stamp when a record is
modified. However, the macro seems to crate the date/time stamp when a
record
is even just accessed and no changes to data have been made. I would like
to
see if the macro could be adjusted to create the date/time stamp only when
there is actual data changes and not if a record is just accessed or
viewed.
I am assuming this could be done with an expression of sort in the
"condition" column but not quite sure how to create the
expression/formula.
Any assistance will be grratly appreciated.

Here is what I have so far:
Macro:

Action Item Expression
SetValue [DateModified] Date()
SetValue [TimeModified] Time()

Both of these items only need to happen if data is modified not just
viewed.

Randy, I assume you are entering your data into a form. Instead of using a
macro use VBA on the form's BeforeUpdate Event. You will also have to use
the Dirty property. The Dirty property will check to see if something has
actually changed. Something like this should work.

If Dirty Then
Me.[DateModified] = Now
Me.[TimeModified]=Now
End If

You can use formatting functions to get things to look the way you want.
Actually, there is no need to have both date and time modified fields. Both
the date and time will be stored in the field.

Brian

 

[Kelvin Beaton]

I create a time stamp by adding a field called timestamp and making the
default value Now().

This way it only enters a date/time when the record is first created.

Hope this helps!

Kelvin

Hello...I have a macro that crates a date/time stamp when a record is
modified. However, the macro seems to crate the date/time stamp when a
record
is even just accessed and no changes to data have been made. I would like
to
see if the macro could be adjusted to create the date/time stamp only
when
there is actual data changes and not if a record is just accessed or
viewed.
I am assuming this could be done with an expression of sort in the
"condition" column but not quite sure how to create the
expression/formula.
Any assistance will be grratly appreciated.

Here is what I have so far:
Macro:

Action Item Expression
SetValue [DateModified] Date()
SetValue [TimeModified] Time()

Both of these items only need to happen if data is modified not just
viewed.

Randy, I assume you are entering your data into a form. Instead of using a
macro use VBA on the form's BeforeUpdate Event. You will also have to use
the Dirty property. The Dirty property will check to see if something has
actually changed. Something like this should work.

If Dirty Then
Me.[DateModified] = Now
Me.[TimeModified]=Now
End If

You can use formatting functions to get things to look the way you want.
Actually, there is no need to have both date and time modified fields.
Both the date and time will be stored in the field.

Brian

 

[Brian Smith]

I create a time stamp by adding a field called timestamp and making the
default value Now().

This way it only enters a date/time when the record is first created.

Hope this helps!

Kelvin

But he wants to keep track of when a record was last modified.

Brian

 

[Guest]

Hi Brian and thank you for your response. The code works fine, however, it
still modifies the date/time stamp whenever a record is just opened and not
actually "modified"...same as the macro. Might you have any other ideas as to
why this might be happening when a record is opened and not just modified?
--
Randy Street
Rancho Cucamonga, CA

Hello...I have a macro that crates a date/time stamp when a record is
modified. However, the macro seems to crate the date/time stamp when a
record
is even just accessed and no changes to data have been made. I would like
to
see if the macro could be adjusted to create the date/time stamp only when
there is actual data changes and not if a record is just accessed or
viewed.
I am assuming this could be done with an expression of sort in the
"condition" column but not quite sure how to create the
expression/formula.
Any assistance will be grratly appreciated.

Here is what I have so far:
Macro:

Action Item Expression
SetValue [DateModified] Date()
SetValue [TimeModified] Time()

Both of these items only need to happen if data is modified not just
viewed.

Randy, I assume you are entering your data into a form. Instead of using a
macro use VBA on the form's BeforeUpdate Event. You will also have to use
the Dirty property. The Dirty property will check to see if something has
actually changed. Something like this should work.

If Dirty Then
Me.[DateModified] = Now
Me.[TimeModified]=Now
End If

You can use formatting functions to get things to look the way you want.
Actually, there is no need to have both date and time modified fields. Both
the date and time will be stored in the field.

Brian

 

[John W. Vinson]

Hello...I have a macro that crates a date/time stamp when a record is
modified. However, the macro seems to crate the date/time stamp when a record
is even just accessed and no changes to data have been made. I would like to
see if the macro could be adjusted to create the date/time stamp only when
there is actual data changes and not if a record is just accessed or viewed.
I am assuming this could be done with an expression of sort in the
"condition" column but not quite sure how to create the expression/formula.
Any assistance will be grratly appreciated.

Here is what I have so far:
Macro:

Action Item Expression
SetValue [DateModified] Date()
SetValue [TimeModified] Time()

Both of these items only need to happen if data is modified not just viewed.

What Event are you using for this macro? It should be the form's BeforeUpdate
event.

Note that Access stores dates and times in a Date/Time datatype - actually a
double number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. It's almost always best to use just *one* field set to
Now() rather than two fields - one of which will contain #6/13/2007 00:00:00#
and the other #12/30/1899 19:19:25#. Sure you can combine them for searching
but it's really better to combine them up front!

If it's possible that the user is doing "non edit edits" to data on the form -
e.g. typing something and then backspacing over it - the BeforeUpdate event
will still fire even though the user has undone all their changes. It might be
necessary to loop through the controls comparing each's OldValue and Value
property to see if a change has been made. As a rule, though, this would be
overkill!

John W. Vinson [MVP]

 

[Brian Smith]

Hi Brian and thank you for your response. The code works fine, however, it
still modifies the date/time stamp whenever a record is just opened and
not
actually "modified"...same as the macro. Might you have any other ideas as
to
why this might be happening when a record is opened and not just modified?

Randy, I've been using that code for years and it has always worked. Are you
sure you don't have something else going on behind the scenes? Did you stop
your macro from running?

In the BeforeUpdate Event you might want to have a MsgBox pop up to tell you
if the record is dirty or not.

Brian

Hello...I have a macro that crates a date/time stamp when a record is
modified. However, the macro seems to crate the date/time stamp when a
record
is even just accessed and no changes to data have been made. I would
like
to
see if the macro could be adjusted to create the date/time stamp only
when
there is actual data changes and not if a record is just accessed or
viewed.
I am assuming this could be done with an expression of sort in the
"condition" column but not quite sure how to create the
expression/formula.
Any assistance will be grratly appreciated.

Here is what I have so far:
Macro:

Action Item Expression
SetValue [DateModified] Date()
SetValue [TimeModified] Time()

Both of these items only need to happen if data is modified not just
viewed.

Randy, I assume you are entering your data into a form. Instead of using
a
macro use VBA on the form's BeforeUpdate Event. You will also have to use
the Dirty property. The Dirty property will check to see if something has
actually changed. Something like this should work.

If Dirty Then
Me.[DateModified] = Now
Me.[TimeModified]=Now
End If

You can use formatting functions to get things to look the way you want.
Actually, there is no need to have both date and time modified fields.
Both
the date and time will be stored in the field.

Brian