Access VBA - use Bookmark to remove filter and stay on current record

Question

In Access 2010, I have a form which can be opened to a specific record or records using a filter:

DoCmd.OpenForm "frmStories", , , "StoryID = " & someNumber  'open one record
DoCmd.OpenForm "frmStories", , , someCriteria               'open multiple records

Using the code below (source) lets me remove the filter and remain on the current record... or so I thought. Parts of the form - namely the fields calculated by VBA - still think they're on the first record, use StoryID = 1, and consequently display the wrong results.

Dim varFilterID As Variant

Public Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    
    'Note current record if filter is removed
    If ApplyType = acShowAllRecords Then
        varFilterID = Me.StoryID
    End If
    
End Sub

Private Sub Form_Current()        
    ' If the filter is OFF, and we have a stored ID from the filter setting,
    ' use standard bookmark code to return to the record selected for the filter.

    If Me.FilterOn = False Then
        If Nz(varFilterID) <> "" Then
            Dim rs As DAO.Recordset
            Set rs = Me.RecordsetClone
            rs.FindFirst = "StoryID = " & varFilterID
            Debug.Print "varFilterID=" & varFilterID & " storyID = " & Me.StoryID & " 1st"
            If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
            ' Reset the stored filterID so that the code does not keep forcing this
            ' selection as the user navigates through the records.
            varFilterID = Null
            Set rs = Nothing
            Debug.Print "varFilterID=" & varFilterID & " storyID = " & Me.StoryID & " 2nd"

        End If
    End If
    
    'other stuff    
End Sub

Stepping through the code showed that it works fine the first time, reaches the end of the sub and then restarts when Form_Current is triggered again (why?) at which point Me.StoryID reverts to 1. This makes me think the problem has something to do with the event firing order (ApplyFilter seems to trigger ''after'' Current has finished).

Paging to the previous record and back fixes it; and when placed in a command button, the code works perfectly.

What am I doing wrong? Alternatively, is there another approach I could take? (I need to filter on several non-contiguous records, so loading the form with .FindFirst is not an option.)

ETA: I added some Print.Debug lines to see what was going on. This is the result:

ApplyType
varFilterID=35 storyID = 1 1st
varFilterID=35 storyID = 35 1st
varFilterID= storyID = 35 2nd
varFilterID= storyID = 1 2nd      <- resets between Current's End Sub and the last Current

EDIT (5 years later): SOLVED! I posted my solution below. Tl;dr Exit Sub is god.

Answer 1

After five whole years, I finally (finally!) worked out what was going on. As Erik said, Me.Bookmark = rs.Bookmark moves the record, triggering another Form_Current, which led to this happening:

1. Apply filter, go to a particular record (StoryID = 58), remove filter
2. Form goes to the first record (StoryID = 1).
3. Form_Current triggers. Because a filter was removed, Nz(varFilterID) <> "" so the recordset code runs, until...
4. Me.Bookmark = rs.Bookmark
5. Form goes to the bookmarked record (StoryID = 58) and Form_Current triggers again. It skips the bookmark code, as intended, and runs the the rest of the event code, do stuff with StoryID
6. It then returns to the line after step 4, and finishes running the original Form_Current (with the original StoryID = 1, so everything in do stuff with StoryID is calculated wrong).

Basically, (Old Form_Current [New Form_Current] rest of old Form_Current). The solution, then, was simply to exit the sub after the bookmark line:

Dim varFilterID As Variant

Public Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)    
    'Note current record if filter is removed
    If ApplyType = acShowAllRecords Then
        varFilterID = Me.StoryID
    End If    
End Sub

Private Sub Form_Current()        
    ' If the filter is OFF, and we have a stored ID from the filter setting,
    ' use standard bookmark code to return to the record selected for the filter.
    ' Reset varFilterID once it has been used.

    If Me.FilterOn = False Then
        If Nz(varFilterID) <> "" Then
            Dim rs As DAO.Recordset
            Set rs = Me.RecordsetClone
            rs.FindFirst "StoryID = " & varFilterID
            varFilterID = ""
            If rs.NoMatch = False Then 
                Me.Bookmark = rs.Bookmark
                Set rs = Nothing
                Exit Sub             'THIS WAS IT!
            End If
            Set rs = Nothing
        End If
    End If
    
    'do stuff with StoryID    
End Sub

Answer 2

The problem is the following: If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark moves the current record in the form, triggering another Form_Current, potentially triggering an endless loop.

You can try rate limiting the Form_Current to only trigger once every second:

Private lastCurrent As Date
Private Sub Form_Current()
   If lastCurrent < Now() - #00:00:01# Then Exit Sub
   LastCurrent = Now()

Note that, depending on how long your code takes to run, you might need to increase the number of seconds.

Note, though, that this is likely is an XY problem. You can move to a specific record when opening a form without applying a filter in the following way

Dim frm As Form
Application.ScreenUpdating = False
DoCmd.OpenForm "frmStories"
Set frm = Forms!frmStories
Dim rs As RecordSet
Set rs = frm.RecordsetClone
strCriteria = "StoryID = " & someNumber
rs.FindFirst strCriteria
If rs.NoMatch = False Then frm.Bookmark = rs.Bookmark
Application.ScreenUpdating = True

Further techniques to achieve this might be things like using OpenArgs, which is one I often use.

Answer 3

I would try a Me.Refresh after applying oder removing the filter.