tl;dr: Use Form.Recordset.Requery to requery a continuous Access form "in place."


I never liked the user experience when I requeried a continuous form in Access.

Let's say I have a form that's filtered to show 100 results.  I have the form sized so that 4 records can be visible at once.  I navigate to record 55.  Record 55 is the top visible record and record 58 is at the bottom of the visible records.  Next, I select record 57.  This is the third of the visible records.  Here's what the form looks like:

 | Record 55
 | Record 56
>| Record 57
 | Record 58
----------------
[ 57] of 100

Now, let's say I adjust the filtering so that there are 110 results, but all 10 new results are sorted so they appear at the end of the form.  

Desired Behavior

Here's how I envisioned the user experience should work when I changed the filtering to show those 10 extra records.  I wanted Access to update the form so that 1) the filtering showed 110 records, 2) record 57 remained the current record, and 3) record 57 appeared as the third visible record on the form.

Following the update to the filter, I wanted to see the result below.  It's identical to our starting point except that the record count is now 110 instead of 100.  

 | Record 55
 | Record 56
>| Record 57
 | Record 58
----------------
[ 57] of 110

For a loooooong time, this seemingly simple behavior eluded me.  Let me take you on my journey (stick with me, the story ends with an amazingly simple solution).

Form.Requery

My first attempt was to use the form's Requery method.  This updated the total record count but it also completely reset the user interface.  This is no good:

>| Record 1
 | Record 2
 | Record 3
 | Record 4
----------------
[  1] of 110

Form.Refresh

So the Form.Requery was too extreme.  Maybe Form.Refresh will work better?  Nope.  The user interface didn't change this time, but neither did the record count.  Where the .Requery did too much, the .Refresh does too little:

 | Record 55
 | Record 56
>| Record 57
 | Record 58
----------------
[ 57] of 100

Form.Bookmark

Daniel Pineault has one of many functions available on the interwebs that uses the form's .Bookmark property to return to a previous record following a requery of the form.  This gets us much closer to where we want to be, but the record "jumps" from the #3 position to the #1 position among the visible records.  This is disconcerting to the user.  What happened to records 55 and 56?  And where did records 59 and 60 come from?

>| Record 57
 | Record 58
 | Record 59
 | Record 60
----------------
[ 57] of 110

RequeryInPlace

This user experience bothered me so much that I devoted many, many hours into crafting a function that would produce my desired behavior.

Here's the original algorithm for my custom function:

  1. Save the current record
  2. Requery the form
  3. Move to the saved record
  4. Move backwards enough records so that the selected record is in its original place with respect to the other visible records
  5. Move to the saved record

At least, that is how it started out.  And even this part was difficult.  There is no property to indicate which of the visible records was selected.  Instead, I had to calculate this value using a combination of the Detail section height and the form's CurrentSectionTop property.

And then I had to start accounting for all the little bugs, gotchas, and edge cases that reared their ugly heads.  After several years of bug fixes, I had created quite the unreadabe mess of code.  It mostly worked though.  I was able to achieve my desired behavior:

 | Record 55
 | Record 56
>| Record 57
 | Record 58
----------------
[ 57] of 110

I'm going to post my sample code, but DO NOT USE IT!!!  It probably has missing dependencies and other problems.  I'm not going to bother checking for any of those things; I simply want to illustrate the lengths I went to for this solution.  You've been warned.

The real solution that I promised you at the top of the article awaits for you below the source code.

'---------------------------------------------------------------------------------------
' Procedure : RequeryInPlace
' DateTime  : 9/10/2009 - 7/24/2015 14:41
' Author    : mwolfe
' Purpose   : Requeries a form and returns to the original record and position.
' Notes     - Function exits without error if the form is closed or does not exist.
'           - To use with subforms, pass the form object of the subform as the optional FormObj
'               parameter.  If FormObj is set, FrmName is ignored.
'           - To use a new recordsource for the form (instead of simply requerying the existing
'               recordsource), pass the new recordsource in the optional NewRecSrc parameter.
' Requires  : GetCurrentRecord() function
'---------------------------------------------------------------------------------------
'
' !!! DEPRECATED !!! use Form.Recordset.Requery as a direct replacement
Function RequeryInPlace(FormName As String, ID_Fld As String, _
                        Optional FormObj As Form, Optional NewRecSrc As String)    
                        
Const acSingleForm = 0, acContinuous = 1, acDatasheet = 2
Dim SaveID As Variant, RowsAbove As Long, Frm As Form, JumpRows As Long, SaveEcho As Boolean
Dim SaveOrder As String, SaveActiveForm As Form, SaveFilter As String

    On Error GoTo Err_RequeryInPlace

    SaveEcho = App.Echo
    App.Echo = False
    Set SaveActiveForm = Screen.ActiveForm
    Dim FrmName As String
    If FormObj Is Nothing Then
        FrmName = FormName
        If FormIsOpen(FrmName) Then
            Set Frm = Forms(FrmName)
        End If
    Else
        On Error Resume Next
        Set Frm = FormObj
        FrmName = Frm.Name
        On Error GoTo Err_RequeryInPlace
    End If

    If Frm Is Nothing Then GoTo Exit_RequeryInPlace
    If Len(Frm.RecordSource) = 0 Then
        If Len(NewRecSrc) > 0 Then Frm.RecordSource = NewRecSrc
        GoTo Exit_RequeryInPlace
    End If

    If Frm.OrderByOn Then SaveOrder = Frm.OrderBy
    If Frm.FilterOn Then SaveFilter = Frm.Filter
    If Frm.NewRecord Then
        If Len(NewRecSrc) > 0 Then
            Frm.RecordSource = NewRecSrc
        Else
            Frm.RecordSource = Frm.RecordSource
        End If
        If Len(SaveFilter) > 0 Then
            Frm.Filter = SaveFilter
            Frm.FilterOn = True
        End If
        If Len(SaveOrder) > 0 Then
            Frm.OrderBy = SaveOrder
            Frm.OrderByOn = True
        End If
        Frm.Recordset.AddNew

    ElseIf GetCurrentRecord(Frm) = 0 Then
        If Len(NewRecSrc) > 0 Then
            Frm.RecordSource = NewRecSrc
        Else
            Frm.RecordSource = Frm.RecordSource
        End If
        If Len(SaveFilter) > 0 Then
            Frm.Filter = SaveFilter
            Frm.FilterOn = True
        End If
        If Len(SaveOrder) > 0 Then
            Frm.OrderBy = SaveOrder
            Frm.OrderByOn = True
        End If

    Else
        On Error Resume Next
        If Frm.Recordset.AbsolutePosition = -1 Then
            If Not Frm.Recordset.EOF Then
                Frm.Recordset.MoveNext
            ElseIf Not Frm.Recordset.BOF Then
                Frm.Recordset.MovePrevious
            End If
        End If
        SaveID = Frm.Recordset.Fields(ID_Fld)
        On Error GoTo Err_RequeryInPlace
        

        If Frm.DefaultView = acContinuous Then
            RowsAbove = Frm.CurrentSectionTop
            On Error Resume Next
            RowsAbove = RowsAbove - Frm.Section(acHeader).Height
            On Error GoTo Err_RequeryInPlace
            RowsAbove = RowsAbove \ Frm.Section(acDetail).Height
        End If

        If Len(NewRecSrc) > 0 Then
            Frm.RecordSource = NewRecSrc
        Else
            Frm.RecordSource = Frm.RecordSource
        End If
        If Len(SaveFilter) > 0 Then
            Frm.Filter = SaveFilter
            Frm.FilterOn = True
        End If
        If Len(SaveOrder) > 0 Then
            Frm.OrderBy = SaveOrder
            Frm.OrderByOn = True
        End If
        If Not IsEmpty(SaveID) Then
            Select Case Frm.Recordset.Fields(ID_Fld).Type
            Case dbText, dbMemo
                Frm.Recordset.FindFirst ID_Fld & "=" & Qt(SaveID)
            Case dbDate, dbTime, dbTimeStamp
                Frm.Recordset.FindFirst ID_Fld & "=" & Dt(SaveID)
            Case Else
                Frm.Recordset.FindFirst ID_Fld & "=" & SaveID
            End Select
        End If

        If Frm.DefaultView = acContinuous Then
            Dim CurrRecNum As Long
            CurrRecNum = GetCurrentRecord(Frm)
            If GetCurrentRecord(Frm) > RowsAbove + 1 Then
                JumpRows = RowsAbove
                On Error Resume Next
                Frm.Recordset.Move -JumpRows
                If Err.Number = 0 Then Frm.Recordset.Move JumpRows
                On Error GoTo Exit_RequeryInPlace
            End If
        End If
    End If

    If Not SaveActiveForm Is Nothing Then
        If SaveActiveForm.Name <> FrmName Then SaveActiveForm.SetFocus
    End If

Exit_RequeryInPlace:
    App.Echo = SaveEcho
    Exit Function
Err_RequeryInPlace:
    Select Case Err.Number
    Case 2475    'You entered an expression that requires a form to be the active window.
        Resume Next
    Case Else
        LogError Err.Number, Err.Description, "RequeryInPlace", "FormFunctions"
    End Select
    Resume Exit_RequeryInPlace
End Function  
'---------------------------------------------------------------------------------------
' Procedure : GetCurrentRecord
' Author    : Mike
' Date      : 4/9/2014
' Purpose   : Convenience function meant to deal primarily with Error 2455: "You entered
'             an expression that has an invalid reference to the property CurrentRecord."
' Notes     - Always returns 0 if there is no current record, otherwise "The value
'             specified by this property corresponds to the value shown in the record
'             number box found in the lower-left corner of the form."
'---------------------------------------------------------------------------------------
'
Private Function GetCurrentRecord(Frm As Form) As Long
    On Error Resume Next
    GetCurrentRecord = Frm.CurrentRecord
End Function

The Real Solution

If you read the comments in my sample code above, you already know the answer.  The simple solution is to requery the form's recordset and not the form itself.

Me.Recordset.Requery

That simple method call addresses all of my concerns with the refreshing a continuous form in Access.  And instead of requiring 167 lines of code, this "advanced" behavior can be had for the low, low price of one single line of code.

Image by MichaelGaida from Pixabay