Restoring a continuous form after Requery

Question

MS Access arbitrary version.

I have a continuous form which displays a part of a recordset, based on a query that can be sorted in many ways. Let’s say the form displays n records of the recordset (where n>1). The first row in the form isn’t necessary displaying the first record of the recordset. One of the records (let’s say the record on row x of n) is selected.

After a Form.Requery, the first record of the recordset will always be on the first row of the form. That first record is also selected.

That’s not good. I want to restore the form after a Form.Requery so the selected record is still on the x:th row in the form, provided that this is always possible. Which records that is displayed above and beneath the x:th row is obviously depended on the recordset’s underlying query.

In my case, this could be achieved if there was a way to calculate how many rows there is between the selected row and the first row in the form, before Form.Requery.

That calculation would (independently of which part of the recordset that is displayed) result in 0 if the first row of the form is selected, 1 if the second row of the form is selected and so on.

How do I do that?

Or is there is another way to achieve the same thing?

Answer 1

if you have in the form any unique column then you can do something like this:

Dim uniqueId : uniqueId = me.uniqueField_Value
' uniqueId hold the current selected unique field value
   Form.Requery
       With Me.RecordsetClone
      '   if uniqueId is numeric use next line
        .FindFirst "[uniqueField] = "  uniqueId 
      ' if uniqueId is string uncomment next line and comment previous line 
      '  .FindFirst "[uniqueField] = " & """" & uniqueId & """"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

Answer 2

I use this function - see explanations in comments.

'---------------------------------------------------------------------
' Requery a continuous form, keeping the selected record and scroll position as it is before
' With ideas by Stephen Lebans - http://www.lebans.com/SelectRow.htm
'---------------------------------------------------------------------
Public Sub FormRequeryPosition(F As Form)

On Error GoTo Err_FormRequeryPosition

    Dim OrigSelTop As Long
    Dim RowsFromTop As Long
    Dim OrigCurrentSectionTop As Long
    
    ' Must cache the current props because Requery will reset them
    OrigSelTop = F.SelTop
    OrigCurrentSectionTop = F.CurrentSectionTop
    
    ' Turn off screen redraw
    F.Painting = False
    
    ' Requery the Form
    F.Requery
    
    ' Calculate how many rows, if any, the selected row was from the top prior to the Requery
    ' Check if Header is visible or not
    If F.Section(acHeader).Visible = True Then
        RowsFromTop = (OrigCurrentSectionTop - F.Section(acHeader).Height) / F.Section(acDetail).Height
    Else
        RowsFromTop = OrigCurrentSectionTop / F.Section(acDetail).Height
    End If
    
    ' Setting the SelTop property forces this row to appear at the top of the Form.
    ' We will subtract the number of rows required, if any, so that the original
    ' current row remains at the original position prior to the Requery.
    ' First set the current record to the last record.
    ' This is required due to the method that the Access GUI manages the ScrollBar.
    
    ' Prevent error on empty form
    If F.RecordsetClone.RecordCount > 0 Then
        ' With complex record source or many records, Access may not know .RecordCount yet -> use MoveLast to get it
        F.RecordsetClone.MoveLast
        F.SelTop = F.RecordsetClone.RecordCount
        F.SelTop = OrigSelTop - RowsFromTop
        DoEvents
        
        F.Painting = True
        ' Now setfocus back to the original row prior to the Requery
        ' In unknown conditions this raises a runtime error 3001 "Invalid argument"
        On Error Resume Next
        F.RecordsetClone.AbsolutePosition = F.CurrentRecord + RowsFromTop - 1
        If Err.Number = 0 Then
            F.Bookmark = F.RecordsetClone.Bookmark
        End If
    End If
    
Exit_FormRequeryPosition:
    F.Painting = True
    Exit Sub

Err_FormRequeryPosition:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & "Line: " & Erl
    Resume Exit_FormRequeryPosition
    
End Sub