Access Pagination
I have a client who has an underperforming continuous form so I start tinkering
around with a model, and created an Access continuous form with pagination abilities.
The database is attached. Hopefully it can help you. I will probably be modifying it as needed.
[sourcecode language=”vb”]
Option Compare Database
‘code 2014 by http://loeblcomservices.com
‘contact: erik@loeblcomservices.com
Const INT_MAX_RECS_PER_PAGE = 10
Private Sub btnBack_Click()
Dim strSQL As String
Dim intStart As Integer
Dim intLast As Integer
If Me.t_id > 1 Then
intLast = Me.t_id – 1
intStart = intLast – (INT_MAX_RECS_PER_PAGE – 1)
If intStart <= 0 Then
intStart = 1
intLast = INT_MAX_RECS_PER_PAGE
End If
strSQL = "SELECT TOP " & INT_MAX_RECS_PER_PAGE & " * FROM tblNums WHERE t_id BETWEEN " & intStart & " AND " & intLast & " ORDER BY t_id ASC "
Me.RecordSource = strSQL
End If
End Sub
Private Sub btnMore_Click()
Dim strSQL As String
Dim intLast As Integer
Dim rst As Recordset
intLast = Nz(Me.t_id, 0)
intLast = intLast + INT_MAX_RECS_PER_PAGE – 1
strSQL = "SELECT TOP " & INT_MAX_RECS_PER_PAGE & " * FROM tblNums WHERE t_id > " & intLast & " ORDER BY t_id ASC "
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not rst.EOF Then
Me.RecordSource = strSQL
End If
Set rst = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = "SELECT TOP " & INT_MAX_RECS_PER_PAGE & " * FROM tblNums"
End Sub
[/sourcecode]
Click here for the database and the code:
access_pagination.mdb