Go to last record (Access 2003)

[Carlos1815]

Hello! I have a form with a default view of Continuous Forms.
Basically, I have a list of 18 text boxes that a user can enter up to
18 topic names. I also made a set of command buttons for navigation:
First, Next, Prev, and Last, so the user can navigate up and down the
list of topic names. The nav buttons work, except for the Last button,
I have it set to go to the last record using:

'******************************************
Private Sub LastQuestion_Click()
On Error GoTo Err_LastQuestion_Click

DoCmd.GoToRecord , , acLast

Me.monyrtm.SetFocus

Exit_LastQuestion_Click:
Exit Sub

Err_LastQuestion_Click:
MsgBox Err.Description
Resume Exit_LastQuestion_Click

End Sub

But, all 18 topic name text boxes won't always be filled, so I'd like
the Last button to go to the last topic name created instead of just
the last (18th) record. This seems like a simple problem, but I'm
having a tough time trying to figure it out. Any help would be
appreciated. Thanks!!

Carlos

 

[Jeff Boyce]

Access stores data in tables as "buckets o' data", with no readily
discernable order. Order comes in when you use a query to sort by one/more
fields.

The concept of "last" depends on you identifying which field(s) to sort by.
If you let Access chose "last", there's no telling ... !

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Snell \(MVP\)]

Try this:

Private Sub LastQuestion_Click()
On Error GoTo Err_LastQuestion_Click

Dim varBookMark As Variant

With Me.RecordsetClone
.MoveLast
Do While Len(Me.TopicNameFieldName.Value) = 0
.Move -1
Loop
varBookMark = .Bookmark
End With

Me.Bookmark = varBookmark

Me.monyrtm.SetFocus

Exit_LastQuestion_Click:
Exit Sub

Err_LastQuestion_Click:
MsgBox Err.Description
Resume Exit_LastQuestion_Click

End Sub

 

[Carlos1815]

Thank you for your response! I ended up figuring it out by doing
something similar:

'******************************************
Private Sub LastQuestion_Click()
On Error GoTo Err_LastQuestion_Click

Dim iMaxstg As Integer

iMaxstg = DMax("stg", "tblTopicTitle", "TopicTitle <> Null")

DoCmd.GoToRecord acDataForm, "TopicTitle", acGoTo, iMaxstg

Me.monyrtm.SetFocus

Exit_LastQuestion_Click:
Exit Sub

Err_LastQuestion_Click:
MsgBox Err.Description
Resume Exit_LastQuestion_Click

End Sub

I had to use the DMax function because the topics can be shuffled, so
I basically told Access to go to the biggest stg number that has
"something in it" and set Focus in that record.