How to refresh a form to the same record

[madspof]

I know i can refresh a form using the Use Me.Requery function but how would i go about refreshing the from so it will go back to the same record in the form


thanks before hand
madspof

 

[DevastatioN]

The way I would think to go about this is, retrieve the primary key value they're on, requery the form, then go back to that primary key... such as

Dim key as string (or whatever it datatype it is)
Dim rs As Object

key = primarykey

Me.requery

Set rs = Me.Recordset.Clone

rs.FindFirst "[PrimaryKey] = " & Str(key)
Me.Bookmark = rs.Bookmark

Note you don't need Str(key) but only key if your key is actually a string already.

 

[LPurvis]

Unless you're using an ADP, then a Refresh instead of a Requery will maintain your cursor position in the form's resultset.
(Are you needing new records inserted by other users to be included and hence the Requery?)

Stephen Lebans has a subtle (but nice) example of requerying without changing position in a continuous form/datasheet if that's what you're interested in.
(You can just switch off screen/form updates for the duration to make the action appear seemless).

As for the Str use above Pat, I believe it's just DevastatioN being rigorous in VBA concatenation.
We so often in code rely on VBA to perform implicit type convertion for us - but it can be argued that we should use more like
strVariable2 = "Literal String " & CStr(strVariable1)
rather than
strVariable2 = "Literal String " & strVariable1
but of course - we pretty much all use the latter and so the former can look out of place.
The result will be he same.


Back to using the Bookmark method - FWIW if you're not going to check for a matching record before attempting to navigate to it - then you might as well just use the forms recordset directly.
Me.Recordset.FindFirst "[PrimaryKey] = " & CStr(keyVariable)
or
Me.Recordset.FindFirst "[PrimaryKey] = " & keyVariable

 

[DevastatioN]

AH, you learn a shortcut everyday

Thanks guys, I'll remember that this is a shorter way to do the same.