Requery without using Me.Requery?

[Daniel]

Is there any way I can requery or perform the requery
method with out calling Me.Requery? I don't want to use
Me.Requery because it sets the form back to the first
record. I want to perform the requerying without setting
back to the first record.

Please help
Thank you

 

[Dan Artuso]

Hi,
No you can't requery without requerying.
You can however, store the primary key of the record
you want to be on after the requerying takes place and then
do a FindFirst to move the form to that record.

pk = Me.yourPk
'do your requery
Me.RecordsetClone.FindFirst "PkField = " & pk
Me.Bookmark = Me.RecordsetClone.Bookmark

 

[Van T. Dinh]

No but you can use code to move back to the Record you
were at just prior to the Requery action.

assuming you have a numeric Field [RecordID] in the Form's
Recordset, you can temporarily store the current value,
requery and then navigate back to it with something like:

'****Untested air-code****
Dim lngRecordID As Long

'Storing value
lngRecordID = Me.RecordID
'Requery the Form
Me.Requery
'Navigating back
Me.Recordset.FindFirst "[RecordID] = " & lngRecordID
********

HTH
Van T. Dinh
MVP (Access)

 

[Bruce M. Thompson]

Is there any way I can requery or perform the requery

method with out calling Me.Requery? I don't want to use
Me.Requery because it sets the form back to the first
record. I want to perform the requerying without setting
back to the first record.

Does your recordset include a unique record identifier (primary key), such as an
autonumber field? If yes, you can use the following approach to requery the form
and then return the form to the record it was on prior to the requery:

'***EXAMPLE START
'If no valid current record then exit sub
If IsNull(Me.ID.Value) Or Me.NewRecord Then _
Exit Sub

Dim lngID As Long
'Store primary key
lngID = Me.ID.Value

'Requery form
Me.Requery
'Restore form to prior record
With Me.RecordsetClone
'Find the key value stored in "lngID"
.FindFirst "ID=" & lngID
If Not .NoMatch Then
'Key value located, so move form to this record
Me.Bookmark = .Bookmark
End If
End With
'***EXAMPLE END

The above example assumes that the "ID" field is an autonumber (if using a
string value, Dim your variable accordingly and embed quotes around the
reference in the search string).