Ms Access VBA go to last record

Question

I am trying to open a form and move to last record. I am using the following

 DoCmd.RunCommand acCmdRecordsGoToLast

The form open and go to last record, but it is hiding the other records(I must use the scroll bar). This might confuse users that there are no other records.

Is it possible to go to last record and have visible the 10 last records?

Answer 1

It's your lucky day - this is surprisingly non-trivial, and I have written a function for this purpose some time ago.

'---------------------------------------------------------------------------------------
' Procedure : FormGotoEnd
' Author    : Andre
' Purpose   : Go to the last record of a continuous form, but don't scroll that record to the top
'             (as DoCmd.RunCommand acCmdRecordsGoToLast would do).
'             Instead scroll up so that the last record is visible at the bottom of the form.
' Parameters: F = the form, can be a subform
'             AddtlEmptyRowsBottom = if you want to have room for more than one empty row, for data entry forms
'
' Call this sub e.g. in Form_Load() or in Form_Current of the parent form, like this:
'             Call FormGotoEnd(Me)
' or          Call FormGotoEnd(Me!SubformControl.Form, 3)
'---------------------------------------------------------------------------------------
'
Public Sub FormGotoEnd(F As Form, Optional AddtlEmptyRowsBottom As Long = 0)

    Dim DetailSectionHeight As Long
    Dim nVisible As Long
    Dim nRecords As Long

On Error GoTo FormGotoEnd_Error

    ' Calculate height of full details section: Window height minus header+footer
    DetailSectionHeight = F.InsideHeight
    ' Ignore errors if form has no header or footer
On Error Resume Next
    If F.Section(acHeader).Visible Then
        DetailSectionHeight = DetailSectionHeight - F.Section(acHeader).Height
    End If
    If F.Section(acFooter).Visible Then
        DetailSectionHeight = DetailSectionHeight - F.Section(acFooter).Height
    End If
On Error GoTo FormGotoEnd_Error

    ' Number of visible records in details section
    nVisible = CLng(DetailSectionHeight / F.Section(acDetail).Height)

    ' Nothing to do if the form has no records
    If F.RecordsetClone.RecordCount > 0 Then
        ' For complex record source and/or many records, Access may not know .RecordCount yet
        ' -> calculate via .MoveLast
        F.RecordsetClone.MoveLast
        nRecords = F.RecordsetClone.RecordCount
        ' Nothing to do if all records are visible
        If nRecords > nVisible Then

            ' Move to last record. Use .Bookmark so the subform doesn't need to get focus
            F.Bookmark = F.RecordsetClone.Bookmark
            ' This is the important part!
            ' Add 2 to AddtlEmptyRowsBottom, in order to see the empty data-entry record plus one empty line
            F.SelTop = nRecords - nVisible + 2 + AddtlEmptyRowsBottom
            ' Make sure the last record is selected
            F.Bookmark = F.RecordsetClone.Bookmark

        End If
    End If

FormGotoEnd_Exit:
    On Error GoTo 0
    Exit Sub

FormGotoEnd_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in FormGotoEnd", vbExclamation
    Resume FormGotoEnd_Exit

End Sub