Set Value of Last Update Field

[Guest]

I believe I my problem relates to misunderstanding events and/or their
sequence.

I have a 'LastUpdate' field which I'd like to change to the current
date/time when the 'Status' field value changes. Here's what I've done so
for...

1. Create a macro ('SetStatusValue') to change the 'Status' field from 1 to
5 as dates are added to Action1, Action2...Action5 ([Action1] Is Not Null and
[Action2] Is Null..SetValue = 1). I attached that macro to the form's
current event. This field changes as desired.

2. On the OnChange event for the 'Status' field, I attached
Me.[LastUpdate] = Now
This field does not populate.

I also use the form's BeforeUpdate event to requery the Status field.

I've experimented multiple ways and can't seem to figure out this 'simple'
task. Any help is appreciated.

 

[Dirk Goldgar]

I believe I my problem relates to misunderstanding events and/or their
sequence.

I have a 'LastUpdate' field which I'd like to change to the current
date/time when the 'Status' field value changes. Here's what I've
done so for...

1. Create a macro ('SetStatusValue') to change the 'Status' field
from 1 to 5 as dates are added to Action1, Action2...Action5
([Action1] Is Not Null and [Action2] Is Null..SetValue = 1). I
attached that macro to the form's current event. This field changes
as desired.

2. On the OnChange event for the 'Status' field, I attached
Me.[LastUpdate] = Now
This field does not populate.

I also use the form's BeforeUpdate event to requery the Status field.

I've experimented multiple ways and can't seem to figure out this
'simple' task. Any help is appreciated.

The Change event fires when a control is changed by user action via the
user-interface (as by typing in the control), but not when the control
is changed by VBA code or by a macro.

Is Status a bound field? I'm not sure that you're going about this the
right way. If Status is a bound field, then you can use code in the
form's BeforeUpdate event to see whether the Status value about to be
saved is the same as the one that was initially loaded for this record:

With Me!Status
If Nz(.Value) <> Nz(.OldValue) Then
Me!LastUpdate = Now
End If
End With

If Status is not a bound field, though, then the .OldValue property
isn't going to be useful. In that case you might (a) save the
calculated Status value to a module-level variable in the form's Current
event, and compare to that in the BeforeUpdate event, or (b) update
LastModified directly in whatever code or macro changes the Status.

 

[Guest]

Presto! Thank you very much Dirk.

I believe I my problem relates to misunderstanding events and/or their
sequence.

I have a 'LastUpdate' field which I'd like to change to the current
date/time when the 'Status' field value changes. Here's what I've
done so for...

1. Create a macro ('SetStatusValue') to change the 'Status' field
from 1 to 5 as dates are added to Action1, Action2...Action5
([Action1] Is Not Null and [Action2] Is Null..SetValue = 1). I
attached that macro to the form's current event. This field changes
as desired.

2. On the OnChange event for the 'Status' field, I attached
Me.[LastUpdate] = Now
This field does not populate.

I also use the form's BeforeUpdate event to requery the Status field.

I've experimented multiple ways and can't seem to figure out this
'simple' task. Any help is appreciated.

The Change event fires when a control is changed by user action via the
user-interface (as by typing in the control), but not when the control
is changed by VBA code or by a macro.

Is Status a bound field? I'm not sure that you're going about this the
right way. If Status is a bound field, then you can use code in the
form's BeforeUpdate event to see whether the Status value about to be
saved is the same as the one that was initially loaded for this record:

With Me!Status
If Nz(.Value) <> Nz(.OldValue) Then
Me!LastUpdate = Now
End If
End With

If Status is not a bound field, though, then the .OldValue property
isn't going to be useful. In that case you might (a) save the
calculated Status value to a module-level variable in the form's Current
event, and compare to that in the BeforeUpdate event, or (b) update
LastModified directly in whatever code or macro changes the Status.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)