Continuous Form - show last record at bottom of list (1 Viewer)

essaytee

essaytee

Need a good one-liner.
Local time
Tomorrow, 01:50
Joined
Oct 20, 2008
Messages
512
Continuous Form - show last record at bottom of list - Access 2010

How do I go to the last record and show that last record at the bottom of a continuous form list (without having to click the vertical scroll bar to force all the records above to appear)? In the process, the last record is to have the focus.

It seems by default when navigating to the last record on a continuous form, the record is shown at the top of the list, it does have the focus but the records above not displayed. I want the last record to be displayed, and have the focus, at the bottom of the list.

Here is a code snippet that achieves most of my requirement, except for the last record having the focus.

Code: 
Sub GoToLastRecord()
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
   
    If rst.RecordCount <> 0 Then
        rst.MoveLast
        rst.Move -19
        Me.Bookmark = rst.Bookmark
    End If
End Sub

Under the above method, the last (or latest) records are shown, but the record at the top of the list has the focus.

I am aware of reversing the sort order, but that is not an option.

I'm open to any suggestions.

ps. Yeah, Recordset & RecordsetClone used, neither here or there, just a remnant of testing every possible scenario.
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:50
Joined
May 7, 2009
Messages
19,242
you already have the code. just create a variable to save your bookmark:

dim v as variant

below this code:

rst.movelast

save your bookmark:

rst.MoveLast
v = rst.Bookmark
rst.Move -19
Me.Bookmark = v
 
essaytee

essaytee

Need a good one-liner.
Local time
Tomorrow, 01:50
Joined
Oct 20, 2008
Messages
512
arnelgp said:
you already have the code. just create a variable to save your bookmark:

dim v as variant

below this code:

rst.movelast

save your bookmark:

rst.MoveLast
v = rst.Bookmark
rst.Move -19
Me.Bookmark = v
Click to expand...

Thanks for replying. I copied and pasted your suggestion and yes, the last record gets the focus but it's displayed at the top of the list, the previous 18 records are not shown. I want to see those records.

ps. Why I didn't see that the bookmark reference wasn't directly below the MoveLast line, I don't know, but thanks for pointing that out. Still doesn't change the behaviour, doesn't show all last records.
 
Last edited:
essaytee

essaytee

Need a good one-liner.
Local time
Tomorrow, 01:50
Joined
Oct 20, 2008
Messages
512
Re: Continuous Form - show last record at bottom of list - Access 2010

Just to correct the code logic, thanks for pointing it out Arnelgp, here it is, though the problem still remains as per my first post.

Code: 
Sub GoToLastRecord()
    Dim rst As Recordset
    Dim v As Variant
    Set rst = Me.RecordsetClone
   
    If rst.RecordCount <> 0 Then
        rst.MoveLast
        v = rst.Bookmark
        rst.Move -19
        Me.Bookmark = v
    End If
End Sub
 
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:50
Joined
May 7, 2009
Messages
19,242
from where are you executing those code's. from Form's Load event?

you can simply goto the last record:

Docmd.GoToRecord ,, acLast
 
MarkK

MarkK

bit cruncher
Local time
Today, 08:50
Joined
Mar 17, 2004
Messages
8,181
It does nothing to navigate in the clone. Use the actual recordset, because that is where your navigation will show on screen...
Code: 
Sub ShowLast19Rows()
    With Me.Recordset   [COLOR="Green"]'use the recordset in the form, not the clone[/COLOR]
        .MoveLast       [COLOR="Green"]'move to the last record, shows at top of list[/COLOR]
        .Move -19       [COLOR="Green"]'move up 19, shows more records, last row moves down[/COLOR]
        .MoveLast       [COLOR="Green"]'move last again, highligh moves to bottom[/COLOR]
    End With
End Sub
See if that comes closer to what you need.
hth
Mark
 
essaytee

essaytee

Need a good one-liner.
Local time
Tomorrow, 01:50
Joined
Oct 20, 2008
Messages
512
Re: Continuous Form - show last record at bottom of list - Access 2010

arnelgp said:
from where are you executing those code's. from Form's Load event?

you can simply goto the last record:

Docmd.GoToRecord ,, acLast
Click to expand...

I'm executing the code from an AfterUpdate event of a ComboBox (a sorting routine, by a date or number). The Form Recordset is changed accordingly to suit. Then the GoToLastRecord process kicks in.

Using the DoCmd.GoToRecord,,acLast - does go to the last record but the last record is shown at the top of the list, all white space below. If you click on the vertical scroll bar all the previous records are then displayed with the last record (that has the focus) now displayed at the bottom of the list.
 
essaytee

essaytee

Need a good one-liner.
Local time
Tomorrow, 01:50
Joined
Oct 20, 2008
Messages
512
MarkK said:
It does nothing to navigate in the clone. Use the actual recordset, because that is where your navigation will show on screen...
Code: 
Sub ShowLast19Rows()
    With Me.Recordset   [COLOR="Green"]'use the recordset in the form, not the clone[/COLOR]
        .MoveLast       [COLOR="Green"]'move to the last record, shows at top of list[/COLOR]
        .Move -19       [COLOR="Green"]'move up 19, shows more records, last row moves down[/COLOR]
        .MoveLast       [COLOR="Green"]'move last again, highligh moves to bottom[/COLOR]
    End With
End Sub
See if that comes closer to what you need.
hth
Mark
Click to expand...

You are kidding me! (I say that in a nice way). I could have sworn I tried that version; obviously not. Thanks, it works a treat. Also thanks to arnelgp for helping out.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:50
Joined
May 7, 2009
Messages
19,242
Try decreasung yiur negatuve move there.
 
essaytee

essaytee

Need a good one-liner.
Local time
Tomorrow, 01:50
Joined
Oct 20, 2008
Messages
512
arnelgp said:
Try decreasung yiur negatuve move there.
Click to expand...

That number is hard-coded but will be a variable. I'll modify the code further to account for the fact when the RecordCount is less than the number of records that can be seen on the screen.

Each form will have a constant (kRECORDS_PER_PAGE). I'm even toying with the idea of programmatically calculating the number of records that can be displayed on the page. I'm assuming it goes something like this, height of the Window section divided by the height of the Detail section, and presto, records per page calculated.

If 20 records can be displayed on a page, then the move required is one less, that being 19. If I move less than 19, say 10, I end up with only half the available area with records, that is, white space under the last record.
 
You must log in or register to reply here.

Similar threads

C
Solved Continuous Form last row
Replies
19
Views
294
theDBguy
theDBguy
amorosik
SUM function inside textbox on the bottom of the form return #Error
Replies
8
Views
204
amorosik
amorosik
J
Solved Focus on continuous form
2
Replies
25
Views
1,136
Pat Hartman
P
H
Solved Problem with Rowsource in Combobox of Continuous Form.
Replies
15
Views
336
HillTJ
H
G
Combobox on continuous form only showing first record
Replies
10
Views
505
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom