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
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 […]