dirty (stinking) changes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

barret bonden - 01 Sep 2007 00:40 GMT

Trying to save the date of any changes on a data form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty = True Then
Me.[last_updated].SetFocus
Me.[last_updated].Text = Date
'Me.Refresh
End If
End Sub

And I get:

the macro or function set to the beforeupdate property for the

field is preventing MS Access from saving data in the field.

It works w/o this error when I just page to and from records, but fails on a
combo box search

Signature

Net/Works Consulting
www.networks-cc.com
support@networks-cc.com

Reply to this Message

Pete D. - 01 Sep 2007 01:01 GMT

To add to below username and system name see the access web. Call this in
the before update, don't need dirty as if it isn't it won't update. If your
function had error checking it would tell you the problem. I have had this
forever and benchmarked it from bits and pieces on the access web. Mine
finished product also includes username and machinename.
http://www.mvps.org/access/

Option Compare Database
Option Explicit
'------------------------------------------------------------
' M_LastModified
'
'------------------------------------------------------------
Function fLastModified()
On Error GoTo fLastModified_Err
With CodeContextObject
.DateModified = Date
.TimeModified = Time()
End With
fLastModified_Exit:
Exit Function
fLastModified_Err:
MsgBox Error$
Resume fLastModified_Exit
End Function

Signature

Pete D.

> Trying to save the date of any changes on a data form.
>
[quoted text clipped - 14 lines]
> It works w/o this error when I just page to and from records, but fails on
> a combo box search

Reply to this Message

SteveM - 01 Sep 2007 01:06 GMT

The BeforeUpdate event should not be used for changing any field data. In
this instance, put your code in the form's OnDirty event. Then, if any field
is edited the current date will be placed in your field.

Incidentally, you do not have to set focus to the field or use the .Text
property (only if you use the .Text property will you have to set focus to
that field). Try this:

Private Sub Form_Dirty(Cancel As Integer)
Me.[last_updated] = Date
End Sub

Steve

> Trying to save the date of any changes on a data form.
>
[quoted text clipped - 14 lines]
> It works w/o this error when I just page to and from records, but fails on a
> combo box search

Reply to this Message

tina - 01 Sep 2007 06:10 GMT

one caveat to that solution: as soon as the user enters/edits the value in
a control, the date will be assigned. but the user can press the Esc key
ONCE to "undo" the change in that control, before exiting the control. when
that happens, the date will *not* be deleted. so you could have the date
updated even when no other data change is written to the table.

i don't have an alternate *simple* solution to offer. if it's really
important that you track the last date of change - and it's not clear if you
want to track the last date of change to the *table* or to each individual
record in the table - you'll probably have to use a separate table to store
the date(s).

hth

> The BeforeUpdate event should not be used for changing any field data. In
> this instance, put your code in the form's OnDirty event. Then, if any field
[quoted text clipped - 28 lines]
> > It works w/o this error when I just page to and from records, but fails on a
> > combo box search

Reply to this Message

Albert D. Kallal - 01 Sep 2007 14:44 GMT

> The BeforeUpdate event should not be used for changing any field data. In
> this instance, put your code in the form's OnDirty event. Then, if any
> field
> is edited the current date will be placed in your field.

Why not? I think the forms before update event is a good event to use in
this case.

If you do a undo in the form, then that update code will NOT yet have run.

While in both cases the undo would undo the changes, it is a good
programming practice to NOT run the code until you actually need to. For
example, in addition to updating the "last updated" field, our code might
update another table, or some other type of calculation and updates could
occur.

You do NOT want to run that update code until you actually attempt to save
the record.

If we don't do the update of this additional data, then the user is free to
use edit->undo and un-do all changes to the record.

So, the time to run this update code is JUST before you about to update the
actual record. It makes little sense to start running a bunch of update code
when we just dirtied the record, but we not actually committed the record
yet (and, we don't even know if the user is going to save the record)..

I totally open to differing opinions here. I just think the before update
event is a rather good and appropriate event to use here.

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

SteveM - 01 Sep 2007 15:38 GMT

My forms almost always have Add, Edit, Save, Cancel, Delete buttons.
I use date modified and username fields to track any changes. Since the user
has to click the edit button to initiate editing I am happy to assume that
their edit was intentional. In any case, even if they cancel their edit I am
happy to pass responsibility for the latest update to that user, since they
did change something but changed their mind. I do take on board what you have
said and I admit I hadn't really thought of that.

Fields have an OldValue property that can always be used to undo changes and
this could be incorporated in the Cancel button procedure or the form's
OnUndo event if you want that functionality.

The form's BeforeUpdate event can give you problems if you update some
fields. For example, if you attempt to modify a field that has already been
modified by the user, you will get an error. I generally only use this event
for form validation to call a required field function. Field level validation
is done in each field BeforeUpdate event.

Steve

> > The BeforeUpdate event should not be used for changing any field data. In
> > this instance, put your code in the form's OnDirty event. Then, if any
[quoted text clipped - 25 lines]
> I totally open to differing opinions here. I just think the before update
> event is a rather good and appropriate event to use here.

Reply to this Message

Albert D. Kallal - 01 Sep 2007 14:30 GMT

"barret bonden" <support@networks-cc.com> wrote in message news:aF1Ci.257

using the before update event is a GREAT EVENT to use. howver, you code is
full of errors.

Try:

Private Sub Form_BeforeUpdate(Cancel As Integer)

> If Me.Dirty = True Then

You don't need the "dirty" test, because the before update event ONLY fires
if the date is about to be updated. So, remove the ahoe.

> Me.[last_updated].SetFocus

NEVER NEVER use the setfoce to set data up.

> Me.[last_updated].Text = Date

NEVER NEVER use the .text properoty of cntorl, uneslls you write code that
captures EACH keystorle. use the .value, or the devaule.

So, our code should like like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

me.last_updated = Date

end Sub

Or, you could write the above as

Private Sub Form_BeforeUpdate(Cancel As Integer)

me!last_updated = Date

end Sub

And, as menotned, you could put use;

Private Sub Form_BeforeUpdate(Cancel As Integer)

me!last_updated.value = Date

end Sub

> field is preventing MS Access from saving data in the field.

Your tyring to move focus to a contorl, and modiy it, but the form is
underong a data write..and even posiblry a form close.

If you have existing code that goes;

me.MyCtontorl.SetFocues
me.MyContorl.Text = "some value"

change the above to:

me.MyContorl = "some value"

And, if the control is not actually going to be placed on the screen, but
you just want to modify the reocrdset (the field in a table bound to the
form) then use:

me!MyFieldName = "some value"

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message