You're going a bit off track, it's not the way it's normally done.
In other words there is unlikely to be anything out there you can use off the peg as it were.
I would search for pagination, you might get lucky.
I think the way to go about it would be to have the normal main form subform arrangement. Have the subform show the list of records in datasheet view or possibly continuous form view. Then you need to work out a way to feed the subform the set of 5 records you want to display.
I prefer sending an SQL string to the subforms record source. Alternatively you can apply a filter.
you can modify your recordsource for each page using the TOP component of a query
if your current recordsource is
SELECT * FROM myTable
for the first page
SELECT TOP 5 * FROM myTable
for the second page
Code:
SELECT TOP 5 *
FROM myTable T LEFT JOIN (SELECT TOP [COLOR=red]5[/COLOR] ID FROM myTable) Q ON T.ID=Q.ID
WHERE Q.ID is Null
for the third page
Code:
SELECT TOP 5 *
FROM myTable T LEFT JOIN (SELECT TOP [COLOR=red]10[/COLOR] ID FROM myTable) Q ON T.ID=Q.ID
WHERE Q.ID is Null
increment the 10 to 15 for the fourth page etc.
You will need to build the sql to the query in VBA, you cannot pass the 10 as a parameter to a query.
your code for the form might be something like
Code:
Option CompareDataBase
Option Explicit
Dim Pagenum as integer
Private Sub Form_Open()
Pagenum=1
chgRecordsource(pagenum)
End Sub
private sub chgRecordsource()
dim RecSrc as string
RecSrc="SELECT TOP 5 * FROM myTable T"
if Pagenum<>1 then recsrc=recsrc & " LEFT JOIN (SELECT TOP " & pagenum * 5 & " ID FROM myTable) Q ON T.ID=Q.ID
WHERE Q.ID is Null"
me.recordsource=recsrc
end sub
private sub btnNextpage_click()
pagenum=pagenum+1
chgRecordsource(pagenum)
end sub
private sub btnPrevpage_click()
pagenum=pagenum-1
chgRecordsource(pagenum)
end sub
you'll need to add in some code to prevent a page number of zero or user trying to go beyond the last page
The other benefit is it reduces the amount of network traffic so forms with large recordsets will load faster - although there can be a small performance hit on each page change
