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




By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!


These posts may help answer your question too...

How can I interact with other Office applications (Excel) using VBA in Access?

Need to write your Access data or query to an Excel file? Here is the how to do it: Most people are familiar with Excel and know how to use it well (enough), and when you start talking about Access, they get scared off, and don’t know what to do anymore. Well, here you are […]

How To Generate A XML File With Access VBA

XML is used to structure your data. You can send a CSV file to another person, but using XML, you can structure your data in a more elegant format, and get very specific about your fields, and what data is contained within your tags (your field names). This is different than a basic CSV file […]

Mouse Click Counter On Access Form

This post will demonstrate how you can count the number of clicks on your button in a certain time frame. It will function like a game . Here is the database form all in one: Option Compare Database Public m_dteStartTime As Date Public m_dteStopTime As Date Private Sub btnClicker_Click() Dim intValue As Integer Dim rst […]

Shared Access Database Management

Here is a handy setup for those who need to manage a shared access database. Either you can create this from scratch as per the example or add the new tables to your existing database. This setup will track: 1. the users currently using the database 2. what time the user and computer name currently […]

Previous Post

How To Return A Dollar Value As A Text String

Next Post

MS Access Hiding Text Boxes Based On User Selection VBA