Form - Go to newest (recently added) record using DAO's .Bookmark & .LastModified

[Cosmos75]

After searching the forum and reading the Access Help file, I still can't figure out what I am doing wrong.

What I am trying to do is to set the focus of a form back to the most recently added record.

Private Sub Form_AfterUpdate()

    Dim myDB As DAO.Database
    Dim tblRST As DAO.Recordset

    Set myDB = CurrentDb()
    Set tblRST = Me.RecordsetClone

    With tblRST
        If .Bookmarkable Then
            .Bookmark = .LastModified
            varBookmark = .Bookmark
            Me.Bookmark = varBookmark
            .Close
        End If
    End With
    myDB.Close

End Sub

It doesn't work, it keeps going back to the very first record. What am I doing wrong?


I am not doing a .Requery anywhere (so I am not destroying the bookmark) and am not getting the "Invalid Bookmark" message.

 

[KenHigg]

Are you using an autonumber as a pk?

 

[Cosmos75]

KenHigg,

Yes, I am. Will it not work with an autonumber as the PK?

 

[Cosmos75]

I found this Kb article
ACC2000: "No Current Record" Error Message When Setting Bookmark to LastModified

Change my code to this

    With tblRST
        If .Bookmarkable Then
            .MoveLast
            varBookmark = .Bookmark
            .Bookmark = .LastModified
            Me.Bookmark = .Bookmark
            .Close
        End If
    End With

It works now. The article says that "You may receive this error if you are adding many records to a large recordset, and you try to set the Bookmark property to LastModified". I didn't get that error message and only have 84 records at the moment but it seems to be working now.

 

[Cosmos75]

Spoke too soon. I tested it by editing a record that wasn't the last one and it goes right to the last record which isn't the one that I just edited.

 

[KenHigg]

Hum...

I'd just find the last record by dmax()'ing the pk then use that to filter (or what ever) the form....

But if you got it working - Cool...

 

[Cosmos75]

KenHigg,

That's a good idea! Thanks!

Was hoping to use .LastModified since that would also allow me to go to the most recently edited record. If I ever manage to figure it out, I'll post back.

 

[KenHigg]

Cool...

I'd like to see the .LastModified solution.

 

[Cosmos75]

DOH! I should have read that help file more closely.

You can use the LastModified property to move to the most recently added or updated record. Use the LastModified property with table- and dynaset-type Recordset objects. A record must be added or modified in the Recordset object itself in order for the LastModified property to have a value.

I was adding the new record using "DoCmd.GoToRecord , , acNewRec"
I feel so stupid now...

 

[Cosmos75]

Replacement / Workaround - DAO .Bookmark and .LastModified

Here's what I come up with as a replacement for using .Bookmark and .LastModified

Option Compare Database
Option Explicit

Public blnNewPart As Boolean
Private tblRST As DAO.Recordset
[color=green]'Bookmark for most recent edited record since form was opened[/color]
Private bkmkEdit As Long
[color=green]'Bookmark for record with the biggest autonumber primary key[/color]
Private bkmkNew As Long
Private strMsg As String

Private Sub [b]gotoNewest()[/b]
    Set tblRST = Me.RecordsetClone

    With tblRST
        If Not (.BOF And .EOF) Then
            bkmkNew = DMax("[RecordID]", Me.RecordSource)
            If Not IsNull(bkmkNew) Then
                .FindFirst ("[RecordID] = " & bkmkNew)
                Me.Bookmark = .Bookmark
                Me.RecordText.SetFocus
            Else
                strMsg = "No records found!"
                MsgBox strMsg, vbCritical
            End If
        End If
        .Close
    End With
End Sub

Private Sub [b]gotoEdited()[/b]
    Set tblRST = Me.RecordsetClone

    With tblRST
        If Not (.BOF And .EOF) Then
            If Not (bkmkEdit = 0) Then
                .FindFirst ("[RecordID] = " & bkmkEdit)
                Me.Bookmark = .Bookmark
                Me.RecordText.SetFocus
            Else
                strMsg = "Nothing has been edited since this form was opened!"
                MsgBox strMsg, vbCritical
            End If
        End If
        .Close
    End With
End Sub

Private Sub [b]Form_Open(Cancel As Integer)[/b]
    Me.AllowAdditions = False
    bkmkEdit = 0
End Sub

Private Sub [b]Form_AfterUpdate()[/b]
    If blnNewPart Then
        Call gotoNewest
    Else
        bkmkEdit = Me.RecordID
    End If
    Me.AllowAdditions = False
End Sub

Private Sub [b]Form_BeforeUpdate(Cancel As Integer)[/b]
    If Me.NewRecord Then blnNewPart = True
End Sub

Private Sub [b]cmdAdd_Click()[/b]
    If Not Me.NewRecord Then
        Me.AllowAdditions = True
        DoCmd.GoToRecord , , acNewRec
        Me.RecordText.SetFocus
    End If
End Sub

Private Sub [b]cmdGotoNEW_Click()[/b]
    Call gotoNewest
End Sub

Private Sub [b]cmdGotoEDIT_Click()[/b]
    Call gotoEdited
End Sub