Access VBA女士走到最后一条记录 [英] Ms Access VBA go to last record

问题描述

我正在尝试打开一个表单并移至上一条记录. 我正在使用以下

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.

是否可以转到最后一条记录并显示10条最近的记录?

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

推荐答案

今天是您的幸运日-这真是不平凡的一天，我早些时候已经为此目的编写了一个函数.

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

这篇关于Access VBA女士走到最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！