Refreshing Underlying Table?

[PeteCresswell]

I'm calling a routine from AfterUpdate that needs to know the latest-
and-greatest value of the field for whom the AfterUpdate is firing.

Normally I would do a DoCmd.DoMenuItem acFormBar, acFile, acSaveRecord

But that breaks down when stepping though the code and FormBar is not
available.

Is there another way to flush that value to the table in VBA?

 

[Dirk Goldgar]

I'm calling a routine from AfterUpdate that needs to know the latest-
and-greatest value of the field for whom the AfterUpdate is firing.

Normally I would do a DoCmd.DoMenuItem acFormBar, acFile, acSaveRecord

But that breaks down when stepping though the code and FormBar is not
available.

Is there another way to flush that value to the table in VBA?

So you're talking about the AfterUpdate event of a control on the form, not
of the form itself? (In the AfterUpdate event of the form, you'd *know*
that the record had just been saved.) And you want to force the record to
be saved now, before calling this other routine? My favorite way is to set
the form's Dirty property to False:

Me.Dirty = False

Be aware that, if the record can't be saved for some reason, the error
message you get when forcing a save this way can be misleading -- it may say
something about being unable to set a property.

 

[Ken Sheridan]

Rather than forcing the row to be saved, which might not be possible and
raise an error, why not just get the current highest value of the column with
the DMax function, then compare it with the value of the control which has
just been updated, If its greater or equal to the control's value than it’s
the highest, otherwise the value of the control is the highest.

Ken Sheridan
Stafford, England

 

[a a r o n . k e m p f]

uh, if you want to flush that value to the table in VBA, you could
always run an update statement in SQL..
I mean, isn't this what you're trying to do?

strSql = "UPDATE TABLE SET VALUE = 'X' WHERE RECORDID = 12"

Docmd.RunSql strSql

-Aaron

 

[Dirk Goldgar]

message

uh, if you want to flush that value to the table in VBA, you could
always run an update statement in SQL..
I mean, isn't this what you're trying to do?

strSql = "UPDATE TABLE SET VALUE = 'X' WHERE RECORDID = 12"

Docmd.RunSql strSql

No, if this is a bound form the record may be locked, in which case I
believe the update query will fail. And if the record is not locked, then
when Access attempts to save the form's record after the update query has
been run, the user will probably get a warning about how "this record has
been changed by another user." Better to just instruct the form to save the
edited record.

 

[a a r o n . k e m p f]

_IF_YOU_USE_JET_

SQL Server allows you to manage locks.

SQL Server allows you to specify _WITH_NOLOCK_.

-Aaron

 

[Dirk Goldgar]

message

_IF_YOU_USE_JET_

SQL Server allows you to manage locks.

SQL Server allows you to specify _WITH_NOLOCK_.

1. Have we any reason to believe the OP is using SQL Server? All his
previous questions have implied Jet.

2. As far as I can tell from BOL, NOLOCK is not permitted in an UPDATE
statement. Am I misreading that?

3. Even if the update is successful with NOLOCK, I believe the user will
still get a warning message later, when Access attempts to save the form's
current dirty record, and the user will be forced either to accept the
"other user's" changes or overwrite them. This could be confusing, and
there's no need to subject the user to this warning.

 

[(PeteCresswell)]

Per Dirk Goldgar:

1. Have we any reason to believe the OP is using SQL Server? All his
previous questions have implied Jet.

JET.

Me.Dirty=False works like the proverbial charm.

Thanks again.

 

[a a r o n . k e m p f]

everyone should be using SQL, jet sucks

1. Have we any reason to believe the OP is using SQL Server?  All his
previous questions have implied Jet.

2. As far as I can tell from BOL, NOLOCK is not permitted in an UPDATE
statement.  Am I misreading that?

3. Even if the update is successful with NOLOCK, I believe the user will
still get a warning message later, when Access attempts to save the form's
current dirty record, and the user will be forced either to accept the
"other user's" changes or overwrite them.  This could be confusing, and
there's no need to subject the user to this warning.