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

Question

This should be an easy one. This form is filtered 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?

Answer 1

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.