Update other field using BeforeUpdate

[Mcihael]

Hi All. I have used the Form BeforeUpdate Event to change
the data in a DateLastModified field using the following
code

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

It works fine, but when I go to edit the same record
again, I get the following error without an error number.

"The data has changed
Another user edited this record and saved the changes
before you attempted to save your changes.
Re-edit the record."

I am then able to re-edit the record.

I have also tried using alternate methods to update the
field such as recordsets, RecordsetClone, even triggers
from the back end SQL server, but they all give the same
result.

One other detail is that the message happens after the
keydown event for the control.

Please help!! - Thanks

 

[Geoff]

You code is perfect.

This sounds like a cache problem, but
I don't know
how to solve it. I mention the
following in case it
makes a difference.

The general advice is, if the name of
the field in the
table is "DateLastModified" and if a
control for this
field appears the form, it's best to
set the Name
property of the control to a different
name, say,
"txtDateLastModified". The reason is
that Access
allows you to refer to fields in a
form's underlying
record source even if controls for
those fields don't
appear on the form. By giving the
control (if you
have one) a different name, ambiguity
is removed
as to what you're updating in the
Form_BeforeUpdate
event. (If a control is on the form,
it's usual to set its
Enabled property to No and its Locked
property to
Yes.)

Sorry I can't help directly.
Good luck
Geoff

 

[Guest]

Thanks Geoff, it didn't work but was worth a shot.

I changed the name of the text box to CtlDateLastModified, and then tried the following in code 1 at a time.

Me.CtlDateLastModified = Date
Me.DateLastModified = Date
Me!CtlDateLastModified = Date
Me!DateLastModified = Date

nothing worked, any other ideas?

 

[Guest]

Solved.
It turns out the ODBC refresh interval (sec), with a default of 1500 seconds, on the Advanced tab of the Access Options, is the governing factor as the underlying table is a linked table to a SQL server. It seems the Me.refresh method does not work for such a case (I don't know why)

If I change the underlying source to a linked table in another mdb file, it works fine, or if I reduce the ODBC refresh interval at Form Load, it also works, except that increases network traffic.

The following link describes a similar occurrence, but with a subform. I suppose if I made the form different to use a subform I could use the suggested solution.

http://support.microsoft.com/default.aspx?scid=kb;en-us;302492&Product=acc

 

[Geoff]

Interesting. Thanks for reporting back.

It seems it was a "cache" problem. It's surprising the default
refresh interval is so long (1500 seconds = 25 minutes). Users may
well want to update the same record in that time (eg forgotten
something).

I would speculate that, if it is possible to force a write of the
"cache" with vba, you could intercept the offending error number,
write the "cache", and allow the user to try again to edit the same
record. However, if the second attempt to edit the record failed with
the same error number, you'd presumably have to message the user that
an update isn't possible at the present time. I'm speculating because
I don't know how to force a write of the "cache".

Anyway, good luck with it.
Geoff

PS:

(1) By the way, have you also put your code in the Before_Insert
event? In your case, "DateLastModified" may not apply to new records,
but some applications complete a DateLastModified field for new and
existing records.

(2) I see you wisely tried out all the options. Just to confirm
for future reference (as is no doubt obvious to you in fact):

If you have a control on the form, you use:
Me!CtlDateLastModified = Date

If you don't have a control on the form, you use:
Me!DateLastModified = Date

(3) You could replace the Date function with the Now() function if
it's important to know the date and time the record was updated.