In an Access form, how to return to previous record after requery

This should be an easy one. This form is filt开发者_StackOverflow社区ered by [Dismissed] = "N". When the user clicks the "Dismiss" button, the [Dismissed] field changes to "Y". After the requery, the form should then return to the same row where the user was previously at.

Private Sub DismissButton_Click()
   Me!Dismissed = "Y"
   MsgBox "Dismissed!", vbOKOnly
   Dim GoBackToThisRecord As Integer
   GobacktothisRecord = Me.CurrentRecord
   Me.Requery
   Set Me.CurrentRecord=GoBackToThisRecord
End Sub

However, even though the built-in help files say that CurrentRecord is a read/write property, I get an "Invalid use of property" error message on this last line.

After setting the [Dismiss]="Y", and requerying the form, how do I get the user back to his/her previous location in the form?

I don't understand how your solution can work if the form is filtered to a value that the edited record no longer matches -- if you're filtered on [Dismissed] = "N" then changing the current record's Dismissed field to Y should cause the requeried form to exclude the record you've just updated.

That aside, I would never do it the way you've done it, as Me.CurrentRecord returns a number representing the position in the record. Since a requery can cause the number of records to change (e.g., somebody else edits or adds or deletes a record causing it to be included/excluded from the form's recordset) and the position of the sought-for record to change, I would use the PK instead.

  Dim lngPK as Long

  lngPK = Me!MyPKID
  Me.Requery
  With Me.RecordsetClone
    .FindFirst "[MyPKID]=" & lngPK
    If Not .NoMatch Then
       If Me.Dirty Then
          Me.Dirty = False
       End If
       Me.Bookmark = .Bookmark
    End If
  End With

That won't deal with the filter issue, but I leave that aside, since it didn't seem to be the issue that I thought it would be from the description of the original problem.

Nevermind. Fixed it myself. The last line is now:

Me.Recordset.Move GoBackToThisRecord

The right way to move to the previous record, whether it is a new one or not, is

Me.Recordset.Move GoBackToThisRecord -1

I use this function:

Public Sub RequeryFormAndKeepCurrentlySelectedRecord(f As Form)
Dim Position As Long
  Position = f.CurrentRecord
  f.Requery
  If Position > 1 Then
    f.Recordset.move Position - 1
  End If
End Sub