To comp.databases.ms.access --

I know that the recommended way to detect an update to a form, is to
use the form's "Before Update" event, and putting in VBA code like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Last_changed_date = Date
End Sub

Then, the field "Last_changed_date" on the form would show the date of
the most recent update.

HOWEVER, in my case this does not work the way I want it to. I have a
field on the form that gets automatically updated, based on the value
of a field in another table. (This is done by a function that is
invoked on the "On Current" event for the form, and the "On Lost Focus"
event for one of the fields in the form.) This has the effect of
setting "Last_changed_date" to today *whenever* the form is opened --
which is NOT what I want to do. What I really want to do is update
"Last_changed_date" only when the user actually, MANUALLY, keys data
into the form. Does anyone know how I can do this -- hopefully in not
too complicated a manner? Thanks much & happy holidays!

--Tom

did you try the Dirty property of the form?

Tom_F wrote:

To comp.databases.ms.access --
>
I know that the recommended way to detect an update to a form, is to
use the form's "Before Update" event, and putting in VBA code like:
>
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Last_changed_date = Date
End Sub
>
Then, the field "Last_changed_date" on the form would show the date of
the most recent update.
>
HOWEVER, in my case this does not work the way I want it to. I have a
field on the form that gets automatically updated, based on the value
of a field in another table. (This is done by a function that is
invoked on the "On Current" event for the form, and the "On Lost
Focus" event for one of the fields in the form.) This has the effect
of setting "Last_changed_date" to today *whenever* the form is opened
-- which is NOT what I want to do. What I really want to do is update
"Last_changed_date" only when the user actually, MANUALLY, keys data
into the form. Does anyone know how I can do this -- hopefully in not
too complicated a manner? Thanks much & happy holidays!
>
--Tom

The falls into the category of "Doctor, it hurts when I do this".

It is a terrible idea to update a record every time you view it in a form.
Why on earth are you doing that? It sounds you are saving derived data and
updating (correcting) it every time you navigate to each record. If so, the
answer would be to not store such data in the first place.

Eliminate that, then you won't have a problem.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Instead of using the FORM before update, use the Beforeupdate event of
each of the fields that you want to track changes to.

That event is ONLY triggerd on a manual change to the field.

Ron

Mr. Brandt --

Thanks! I followed your suggestion. I left the calculated field on
the form (because the user wants to see this information on the
screen), BUT I changed it from a bound field to an UNBOUND field. Of
course this is better database practice, since a calculated value
stored on a table could well be out of date tomorrow. But I got an
extra "dividend" for my virtue: I can now use the form's "Before
Update" event without a problem! I guess if a field is updated but is
unbound to a table, then it's not considered an "update" -- right?

Before I did the above, I was fooling around with a way to detect a
manual update -- with the calculated field BOUND to the table. It
looks like (as another poster suggested) I could use the "On Dirty"
event. But I believe just one extra wrinkle has to be added: at the
end of the function which calculates my derived value, I put in this
line of VBA code:

Me.Dirty = False

This allows me to subsequently check for a "real" dirty-ing event (i.e.
a manual entry by the user at the keyboard).

Thanks again, everybody -- and Happy Holidays,

--Tom

Rick Brandt wrote:

The falls into the category of "Doctor, it hurts when I do this".
>
It is a terrible idea to update a record every time you view it in a form.
Why on earth are you doing that? It sounds you are saving derived data and
updating (correcting) it every time you navigate to each record. If so, the
answer would be to not store such data in the first place.
>
Eliminate that, then you won't have a problem.
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com