Requery a Form in Place
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:
- Save the current record
- Requery the form
- Move to the saved record
- Move backwards enough records so that the selected record is in its original place with respect to the other visible records
- 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