Open form to last record

Question

hello there,

please i need help as i have a button which use to navigate to a form, this form have an Auto-number field,

i need  to open the form and automatically show the last record entered.

i'm using a macro to openform as shown, 

thanks a lot,

Answer 1

I'd suggest that you add a DateTimeStamp column, of DateTime data type, to your table, with a DefaultValue property of Now().  You don't need to show this in the form, but if you base the form on a query which sorts the records in DateTimeStamp order you'd simply need to move to the last record in the form's recordset, which you can do in its Load event procedure.

If you wish to sort the form other than in DateTimeStamp order you'd need to use code to find the record with the MAX DateTimeStamp value in the form's RecordsetClone and navigate to it by synchronizing the form's Bookmark with that of the recordset.

Answer 2

I am not sure in a macro but on the open event you could use

DoCmd.GoToRecord , , acLast

Answer 3

I am not sure in a macro but on the open event you could use

DoCmd.GoToRecord , , acLast

That would only work if the form's records are in order of date/time entered, which might not be the case.  The only really reliable way is to date/time stamp each row when it's inserted.  You can then navigate to the row with the latest date/time value.  It's easily done in the form's Load event procedure:

Private Sub Form_Load()

    Dim dtmLatestDateTime As Date

    dtmLatestDateTime = DMax("DateTimeStamp", "TableNameGoesHere"")

    With Me.RecordsetClone

        .FindFirst "DateTimeStamp = #" & _

            Format(dtmLatestDateTime, "yyyy-mm-dd hh:nn:ss") & "#"

        If Not .NoMatch Then

            Me.Bookmark = .Bookmark

        End If

    End With

End Sub