Show last 5 records in Subform datasheet view

is Order By UniqueIDField Desc  okay with you?  Or you have to have sorted Asc?

Mike

If you have to have it sorted Asc and show last 5 records, then post SQL string here.

Mike

Try this


    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim bLoop As Boolean
   

    DoCmd.GoToRecord , , acLast
   
    Set rs = Me.RecordsetClone
   
    rs.MoveLast
    bLoop = True
    i = 1
    Do While bLoop = True
        If rs.BOF = True Then
            rs.MoveFirst
            bLoop = False
        Else
            rs.MovePrevious
            i = i + 1
            If i = 5 Then bLoop = False
        End If
    Loop
    If rs.EOF = False And rs.BOF = False Then Me.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing

Thanks all, I'm glad I was in training for most of the day, because it allowed you all to keep tweaking the solution.

Unfortunatly I get a "type missmatch #13" error on the "Set rs = Me.RecordsetClone" line.
I don't have a clue why.

========Start========
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Open

 Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone
    If rs.EOF = False Then DoCmd.GoToRecord , , acLast, 5

Exit_Open:
    Exit Sub

Err_Open:
    If Err.Number = "2105" Then
        Resume Next
    Else
        MsgBox Err.Description & Chr(13) & "Error #: " & Err.Number
    Resume Exit_Open
    End If

End Sub
========end========

Did I mention that this is a ADP (linked to an sql db).

try one of two things. not sure if it matters that if its an ADP

change

Dim rs as DAO.Recordset

to

Dim rs as Object

or

go to Tools/References and check Microsoft DAO Object Library (latest version)

RecordsetClone is DAO property of the form, u wont be able to use ADO but I dont know about ADP's. Im sure its still DAO

Doh!

Just had to change DAO to ADODB.

works great!! :-)

Good job guys.

Hmm how to split points...
rockiroads what do you think, how much should I give eghtebas?

talk about putting me on the spot!

I aint falling for that trap :)

I'll let u decide, look at the responses, eghtebas came up with a tidier solution from my original one, and I in turn made it even shorter!
So I'll let u go from there

And its good to know, ADP based forms RecordsetClone makes use of ADO not DAO, thanks

Just in case I was clear, which I'm often not as much as I think I am.

 Dim rs As ADODB.Recordset

    Set rs = Me.RecordsetClone
    If rs.EOF = False Then DoCmd.GoToRecord , , acLast, 5


BTW:  Thanks for skiping capricorn1 comment, (nothing personal cap), because I didn't want to limit the records.  They need to see at least the last few, sometimes all (~100s) before they create a *new one.
In addition, the users are accustomed to adding new records to the bottom with acending order; Oldest on top down to newest on bottom.  I don't think you can move the add new record * line to the top and change the order to desc.

edit above LOL
"Just in case I wasN"T clear..."

3 lines of code to achieve what u want, and even then one is a variable declaration
not bad

I always wanted to do this in my own application but had no solution for it.

Credit goes to rockiroads with little contribution from me.

400/100 is fine with me.

Mike

50-50 is also fine, I aint bothered either way

You guys are great, its refreshing to see such honesty, respect, and humility.
With I could give you both 500pts.

I'm going with the average of both your suggestions, which I calc to being 1 : .375
(62.5% 37.5) 312/188

Thanks again.


BTW:
MS Visual Basic Help states:
Recordset Property...
The read/write behavior of the Recordset property is determined by the type of recordset (ADO or DAO) and the type of data (Jet or SQL) contained in the recordset identified by the property.

Recordset type      Based on SQL data      Based on Jet data
ADO                     Read/Write                 Read/Write
DAO                     N/A                            Read/Write