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

Free! Subscribe To Our YouTube Channel!

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather
Tags: ,




 

Call Erik Now