Access 2007 VBA: how to get / change the current recording position on the screen

Question

I have an Access 2007 form that displays 40 rows. When I open it, I would like the current record line to be displayed at line position No. 5 of the 40 displayed lines. In other words, there should be 4 lines above the current line and 35 lines below.

MoveUpDown is my first cut when doing this, but currently it only works if the current record line (moved to FindFirst) is initially displayed at line positions from # 1 to # 5. How to find out what the current line position represents , regarding the displayed lines and how to change it? Thanks.

Private Sub OpenMyFormTo(sqlWhere As String) Dim rs As DAO.Recordset, frm As Form DoCmd.Openform "myForm", acNormal Set frm = Forms("myForm").Form Set rs = frm.RecordsetClone If Not (rs.BOF And rs.EOF) Then rs.MoveFirst rs.FindFirst sqlWhere frm.Bookmark = rs.Bookmark MoveUpDown frm.Recordset, 5 End If End Sub Private Sub MoveUpDown(rs As DAO.Recordset, RowsToMove As Integer) Dim RowsActuallyMoved As Integer, i As Integer RowsActuallyMoved = 0 For i = 1 To RowsToMove If Not rs.BOF Then rs.MovePrevious RowsActuallyMoved = RowsActuallyMoved + 1 End If Next i For i = 1 To RowsActuallyMoved If Not rs.EOF Then rs.MoveNext Next i End Sub 

Answer 1

I'm not quite sure that this is all you need:
a) go directly to the 5th record, use: DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, 5
(Me.Name, only if the code is in the form of its own code module)
b) you can request the current position using Me.Recordset.AbsolutePosition
Oddly enough, .AbsolutePosition starts to count at 0, the goto command is at 1.

Answer 2

I believe that I have an answer, although for me this seems awkward. This procedure finds the target line, scrolls down until the target line is visible, scrolls back to the target line, exceeds the job with additional lines (offset), and then scrolls back to the target line. The result is that the target line is displayed as the (Offset + 1) th visible line. If the number of lines is <Offset, then it is displayed as the last line.

 Private Sub DisplayWithOffset(frm As Form, sqlWhere as String, Offset As Integer) Dim rs As DAO.Recordset, DetailsHt As Long, RowHt As Long, i As Integer Dim RowsVisible As Integer, BookmarkAP As Integer, RowsMoved As Integer DetailsHt = frm.InsideHeight _ - frm.Section(acHeader).Height - frm.Section(acFooter).Height RowHt = frm.Section(acDetail).Height RowsVisible = IIf(RowHt > 0, DetailsHt Mod RowHt, 0) Set rs = frm.Recordset If Not (rs.BOF And rs.EOF) Then rs.MoveLast rs.FindFirst sqlWhere BookmarkAP = rs.AbsolutePosition RowsMoved = 0 For i = 1 To RowsVisible If rs.AbsolutePosition + 1 < rs.RecordCount Then rs.MoveNext RowsMoved = RowsMoved + 1 End If Next i Do Until rs.AbsolutePosition = BookmarkAP rs.MovePrevious Loop RowsMoved = 0 For i = 1 To Offset If rs.AbsolutePosition > 0 Then rs.MovePrevious RowsMoved = RowsMoved + 1 End If Next i For i = 1 To RowsMoved rs.MoveNext Next i End If End Sub