Diff between refresh & requery

[Andrew Backer]

I have a question about the difference between refresh & requery :
What is it?

I have a form that uses me.Requery after performing a certain action.
I noticed that after doing this I could not access fields in VB
(Me.Recordset!FieldName, or other ways) without getting a 'no current
record' error. The thing that gets me is that all normal editing
still worked, the form looks ok, etc.

I changed to Me.Refresh and everything is ok.

When should I use one and not the other? I am going to change to
Me.Refresh most places now, but I am still curious.

Thanks,

Andrew Backer

 

['69 Camaro]

Hi, Andrew.

From Access Help:

"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. Nor will it exclude
records that no longer satisfy the criteria of the query or filter. To
requery the database, use the Requery method. When the record source for a
form is requeried, the current set of records will accurately reflect all
data in the record source."

I might add that "Refresh" won't show changes that other users have made in
the database since your Form's RecordSet was last opened, but "Requery"
will. Essentially, "Refresh" shows your own changes to the Form's
RecordSet, but "Requery" shows everybody's changes to this RecordSet.

Normally, when a Form is requeried, the current record will point to the
first record in the RecordSet. If you receive the "No current record" error
message after trying to access a bound field immediately after the "Requery"
operation, then there are no records that met the criteria for the Form's
record source, so the database returned zero records when requeried.

Your other Forms may not need to be changed from "Requery" to "Refresh." It
depends upon whether or not these other Forms need to have the latest
RecordSet from the database.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Andrew Backer]

Thanks for the extra info. What I saw in help did not indicate to me
why I would be getting the error I do.

I get the error when I try to access any field in the recordset after
doing the refresh, not by a bound field or any other method. I can
use Me.Recordset.Fields("...") etc, and it will throw the error.
RecordCount returns 1.

So.. The form looks perfect, all editing works, all the subforms link
properly, the listboxes get requeried properly (depend on master too).
In short, everything works just fine, except this.

Would it have anything to do with dynaset vs. dynaset (Inconsistent
Updates)?

Thanks

Hi, Andrew.

From Access Help:

"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. Nor will it exclude
records that no longer satisfy the criteria of the query or filter. To
requery the database, use the Requery method. When the record source for a
form is requeried, the current set of records will accurately reflect all
data in the record source."

I might add that "Refresh" won't show changes that other users have made in
the database since your Form's RecordSet was last opened, but "Requery"
will. Essentially, "Refresh" shows your own changes to the Form's
RecordSet, but "Requery" shows everybody's changes to this RecordSet.

Normally, when a Form is requeried, the current record will point to the
first record in the RecordSet. If you receive the "No current record" error
message after trying to access a bound field immediately after the "Requery"
operation, then there are no records that met the criteria for the Form's
record source, so the database returned zero records when requeried.

Your other Forms may not need to be changed from "Requery" to "Refresh." It
depends upon whether or not these other Forms need to have the latest
RecordSet from the database.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

..---
| Andrew Backer
| abacker _@_ comcast _dot_ net
`--------