Advent Day 16 Ms Access Gurus

VBA > Form > Go to Last

Go to the last record on a form.

Screen shot

In the upper left, you see the (orange) Last Record command button on a form. Underneath, the Control Tip property pops up as you hover over it. The command button Caption is &Last ... so Alt-L is the HotKey .

On the right is the Property sheet. The On Click event says:

=RecordLast([Form])

By putting a call to a function directly on the Property Sheet, it will be copied when the button is copied too. In code behind the form, you reference the form as "Me". In expressions for controls, it is [Form].

Generally, it is a good idea to keep code behind the form, and not on the property sheet. However, with form navigation buttons, it is easier to copy buttons with logic when function calls are on the property sheet.

Embedded macros are also copied when buttons are copied, but they are harder to document.

Examples


on Property Sheet

=RecordLast( [Form] )

in code behind the form:

Call RecordLast( Me )

in code behind the form: also set focus to the contact note control (control name = "NoteCtc"):

Call RecordLast( Me, "NoteCtc" )

Logic

The return data type is Byte, but it is not set, or intended to be used. This is an indicator that this function is also designed to be called from a property sheet (since Byte is small and rarely used).

Skip errors.

If a form reference is not passed, use Screen.ActiveForm , which is okay for mainforms.

If the record has unsaved changes, save the record (Dirty = False)

If records are showing (RecordCount > 0) then move to the last record. And then ...

if pFirstControlName was specified, then move focus to that control name specified.

Parameters

Optional:

If no parameters are passed, move to the last record on the active(main) form.

Code

'*************** Code Start *****************************************************
' Purpose  : Go to the last record on a form.
' Author   : crystal (strive4peace)
' Return   : Byte (not set)
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'-------------------------------------------------------------------------------

' RecordLast

'------------------------------------------------------------------------------- '
Function RecordLast(Optional pF As Form _ , Optional pFirstControlName As String = "") As Byte ' crystal (strive4peace) '3-20-09... 160819, 181216 ' example useage: Click [Event Procedure] for a Go To Last Record command button ' RecordLast ' Call RecordLast(Me) ' Call RecordLast(Me, "Controlname") 'can also be assigned on the property sheet 'to make logic copy when buttons are copied ' =RecordLast([Form]) On Error Resume Next 'if form reference was not passed, use the active form 'NOTE: specify form parameter for subforms If pF Is Nothing Then Set pF = Screen.ActiveForm 'with the referenced (or active) form ... With pF 'if there have been changes to the current record, save them If .Dirty Then .Dirty = False DoEvents 'do it now 'if there are records showing, move to the last record If .Recordset.RecordCount > 0 Then .Recordset.MoveLast 'set focus to first control, if specified If pFirstControlName <> "" Then .Controls(pFirstControlName).SetFocus End If End If End With End Function ' LICENSE ' You may freely use and share this code ' provided this license notice and comment lines are not changed; ' code may be modified provided you clearly note your changes. ' You may not sell this code alone, or as part of a collection, ' without my handwritten permission. ' All ownership rights reserved. Use at your own risk. ' ~ crystal (strive4peace) www.MsAccessGurus.com '*************** Code End *******************************************************

Notes


on Property Sheet

Picture property

When you look at the property sheet for a command button, you can change the Picture property PropertySheet-CommandButton-Format

When you click in the Picture property, you can click on the Builder button (...) or press Ctrl-F2, to popup the Picture Builder:

PictureBuilder

and then find the picture you want, or Browse... to specify a file on your drive.

Share

Share with others ... here's the link to copy:
secure: https://MsAccessGurus.com/VBA/Code/Form_GotoLast.htm
or
unsecure: http://MsAccessGurus.com/VBA/Code/Form_GotoLast.htm

Reference

Docs / Office client development / Access / Access desktop database reference / Microsoft Data Access Objects reference / Recordset / Methods / MoveLast method

Help: Recordset.MoveLast method (DAO)