Form refresh

[Leslie Isaacs]

Hello All

I often add the following line to the AfterUpdate or other event of a
textbox or combobox (I got the code by creating abutton and accepting the
wizard's Refresh Form Data option):

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

.... but quite often when carrying out the Update (or relevant other) action
I get the following message:

The command or action Rrefresh' isn't available now.
Why does this happen, and what are the 'rules' about when the refresh
command is available?

Thanks for any help.
Les

 

[Arvin Meyer [MVP]]

You cannot refresh a form while it is dirty (i.e. has uncommitted data) You
can replace your code with:

DoCmd.RunCommand acCmdSaveRecord
Me.Refresh
--
Arvin Meyer, MCP, MVP
Microsoft Access
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Leslie Isaacs]

Arvin

Many thanks for this info.

I will certainly try the code you have suggested, and I bet it works ... but
what exactly is "uncommitted data"?
I had thought the the refresh command meant 'go back to the database and
updates all fields', which I suppose would include a Save. Is that not
right?

Thanks again
Les

 

[Douglas J. Steele]

According to the Help file, the Refresh method "Updates the objects in a
collection to reflect the current database's schema." The key is "the
current database's schema": you have to apply the update before it's part of
the schema.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Dirk Goldgar]

In

You cannot refresh a form while it is dirty (i.e. has uncommitted
data) You can replace your code with:

DoCmd.RunCommand acCmdSaveRecord
Me.Refresh

Pardon me, Arvin, but I don't think this is right. Documentation
states, and experimentation confirms, that you can refresh a dirty form.
The effect is to save the form's record, then retrieve it again.

I think something more than this is behind the occasional unavailability
of the Refresh action. I wonder if it is caused by (a) a record that
can't be saved because it is missing required fields or contains data
that fails validation rules, or (b) a record that has been modified by
another user since it was originally displayed. In either of these
cases, though, I'd expect a message relevant to the specific condition,
not just a "can't refresh" message. So I don't really know what's going
on.

 

[Arvin Meyer [MVP]]

You may be right. I assumed that it is because the form is dirty because the
code I posted works. If the conditions occured that caused the form's
validation to fail, my code wouldn't work either. But if someone else was
working on a record and saved it in the interim, the condition may have
resolved itself. The way to test is with an error handler, like:

Error_Handler: 'If someone is editing this record trap the error
If Err = 3188 Then
Dim intRetry As Integer
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, "Another user editing this number"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Str$(Err) & " " & Error$, 48, "Error"
Resume Exit_Here
End If
--
Arvin Meyer, MCP, MVP
Microsoft Access
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[missinglinq via AccessMonster.com]

As is evidenced here, much about Refresh is unclear. Per Help in Access 2000:

"The Refresh method immediately updates the records in the underlying record
source for a specified form or datasheet *to reflect changes made to the data
by you and other users in a multiuser environment.*

The Refresh method shows only changes made to records in the current set.
Since the Refresh method doesn't actually requery the database, *the current
set won't include records that have been added or exclude records that have
been deleted since the database was last requeried."*

I take this to mean that Refresh should only be used in a multi-user
environment, and that even there it is of limited use. I think in this case
that Me.Requery would probably be better than Me.Refresh.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com