Show last 'X' records in a subform

  • Thread starter Thread starter Steve S
  • Start date Start date
S

Steve S

Have a form that the user uses to input data to create new records. these
records are displayed in a subform just below the data entry fields. I now
have the subform set to display all records in reverse order so the user can
see the latest added at the top fo the subform(decending order). this is
confusing for some users ( if not all). they want to see the latest record
entered at the bottom of the subform (ascending order) which means they have
to hit the scroll bar to get to the bottom of the list. Pain in the...

Is there a way to set the subform properties to show the last X records in
ascending order instead of the first x records???

Any help is appreciated.
 
Steve S said:
Have a form that the user uses to input data to create new records. these
records are displayed in a subform just below the data entry fields. I now
have the subform set to display all records in reverse order so the user can
see the latest added at the top fo the subform(decending order). this is
confusing for some users ( if not all). they want to see the latest record
entered at the bottom of the subform (ascending order) which means they have
to hit the scroll bar to get to the bottom of the list. Pain in the...

Is there a way to set the subform properties to show the last X records in
ascending order instead of the first x records???
Click to expand...

No, but you can use the subforms recordsetclone, the recordset
movelast, moveprevious and bookmark to set the subform to the desired
position.

I've been meaning to do a web page on this topic and hopefully will do
it in the next day or so. So check back here.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Create a 'TOP' query based on the table:

SELECT TOP 5 somedate, otherfields
FROM sometable
ORDER BY somedate DESC;

Then base the subform on a query of that query ordered by the ascending date
should give you what the users want. The '5' should be whatever number of
records you need.

SELECT *
FROM topquery
ORDER BY somedate ;

HTH
John
##################################
Don't Print - Save trees
 
John Smith said:
Create a 'TOP' query based on the table:
Click to expand...

However the user won't be able to see the records previous to the ones
selected by the TOP query. What I want to do, and what Steve wants,
and which I have working except for one bug, is display the last five
but allow the user to scroll backwards and view the rest of the
records too.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Steve said:
Have a form that the user uses to input data to create new records. these
records are displayed in a subform just below the data entry fields. I now
have the subform set to display all records in reverse order so the user can
see the latest added at the top fo the subform(decending order). this is
confusing for some users ( if not all). they want to see the latest record
entered at the bottom of the subform (ascending order) which means they have
to hit the scroll bar to get to the bottom of the list. Pain in the...

Is there a way to set the subform properties to show the last X records in
ascending order instead of the first x records???
Click to expand...


You can use this code in the Load event:

With Me.RecordsetClone
If .RecordCount > 5 Then
.AbsolutePosition = .RecordCount - 5
Me.Bookmark = .Bookmark
End If
End With
 
Marshall Barton said:
You can use this code in the Load event:

With Me.RecordsetClone
If .RecordCount > 5 Then
.AbsolutePosition = .RecordCount - 5
Me.Bookmark = .Bookmark
End If
End With
Click to expand...

AbsolutePosition?!?! I never knew about that one.

Thanks Marshall. My solution was getting rather complex and ugly. I
was getting rather embarrassed by it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Tony Toews said:
I'm getting not a valid bookmark on that line.
Click to expand...

Never mind. My fault.

Mind you I'm still getting my wield bug but that's definitely a lot
simpler than I had.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Fair enough, I read 'show last X records' as meaning that was all that he
wanted to have on the form. Hope you manage to nail the bug!

John
##################################
Don't Print - Save trees
 
The Load event of the form the subform is based on, or the load event of the
main form?

Thanks for your help.
 
Steve said:
The Load event of the form the subform is based on, or the load event of the
main form?
Click to expand...

The form with the records you want to position.

I noticed that my code won't position to display the last N
records in the case where N < RecordCount < 2*N

I think this version should deal with that situation:

With Me.RecordsetClone
If .RecordCount > 5 Then
.MoveLast
Me.Bookmark = .Bookmark
.AbsolutePosition = .RecordCount - 5
Me.Bookmark = .Bookmark
End If
End With
 
I tried your suggestion but there is no change in the way records are shown
in the subform. the exact coed is:
Private Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 12 Then
.MoveLast
.AbsolutePosition = .RecordCount - 12
Me.Bookmark = .Bookmark
End If
End With
End Sub

Maybe what I need is to have the scroll bar be positioned ate the bottom of
the window as each record is addes not at the top which is the default.

any suggestions?

appreciate your help
 
You're missing the Bookmark line after the MoveLast.

But that should only make a difference if the record source
has between 13 and 23 records.
 
dans l'article (e-mail address removed), Steve S à
(e-mail address removed) a écrit le 21/01/08 17:10 :
 
Back
Top