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 <SteveS@discussions.microsoft.com> wrote:

>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???

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/

Sounds like a solution is on the way.  I will chack back in a few days.  
thanks much
-- 
Steve S


"Tony Toews [MVP]" wrote:

> Steve S <SteveS@discussions.microsoft.com> wrote:
> 
> >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???
> 
> 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

Steve S wrote:
> 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.

John Smith <johnDOTsmithATbromleyhospitalsDOTnhsDOTuk> wrote:

>Create a 'TOP' query based on the table:

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 S wrote:

>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???


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

-- 
Marsh
MVP [MS Access]

Marshall Barton <marshbarton@wowway.com> wrote:

>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

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/

Marshall Barton <marshbarton@wowway.com> wrote:

>        Me.Bookmark = .Bookmark

I'm getting not a valid bookmark on that line.

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 [MVP]" <ttoews@telusplanet.net> wrote:

>I'm getting not a valid bookmark on that line.

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

Tony Toews [MVP] wrote:
> John Smith <johnDOTsmithATbromleyhospitalsDOTnhsDOTuk> wrote:
> 
>> Create a 'TOP' query based on the table:
> 
> 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.

The Load event of the form the subform is based on, or the load event of the 
main form?

Thanks for your help.
-- 
Steve S


"Marshall Barton" wrote:

> Steve S wrote:
> 
> >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???
> 
> 
> 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
> 
> -- 
> Marsh
> MVP [MS Access]
> 

Steve S wrote:

>The Load event of the form the subform is based on, or the load event of the 
>main form?

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

-- 
Marsh
MVP [MS Access]

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

"Marshall Barton" wrote:

> Steve S wrote:
> 
> >The Load event of the form the subform is based on, or the load event of the 
> >main form?
> 
> 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
> 
> -- 
> Marsh
> MVP [MS Access]
> 

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.
-- 
Marsh
MVP [MS Access]


Steve S wrote:
>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

>> 
>> 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

dans l'article 3635154B-5C37-41EB-A64A-3AD804660B32@microsoft.com, Steve S �
SteveS@discussions.microsoft.com a �crit le 21/01/08 17:10�:

> 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
> 
> "Marshall Barton" wrote:
> 
>> Steve S wrote:
>> 
>>> The Load event of the form the subform is based on, or the load event of the
>>> main form?
>> 
>> 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
>> 
>> -- 
>> Marsh
>> MVP [MS Access]
>>