Paging through Result Sets

[dpimental]

All, I have a ListView control and I want to page through the results.

I have 200 records. I want to page through the results 25 at a time. So when the form comes up the first time, it is displaying the first 25 records. It has a next button for the next 25 records, a previous button for going back to the previous 25 records, etc.

Has anyone done that before? Would you put the recordset into an array with a counter? I'm not sure how to do it?

David Pimental
(US, Oh)

 

[randy700]

There are probably more elegant methods, but this works is you know the number of records in your recordset.

Create multiple queries.
1st query...
SELECT TOP 25 FieldName
FROM yourTable

2nd query...
SELECT TOP 25 FieldName
FROM yourTable
WHERE FieldName NOT IN
(SELECT TOP 25 FieldName FROM yourTable)

3rd query...
SELECT TOP 25
FROM yourTable
WHERE FieldName NOT IN
(SELECT TOP 50 FieldName FROM yourTable)

etc.....

With a little work, you could probably create a loop in VBA to continue until EOF.


Randy

 

[CautionMP]

dpimental,
Another method: Use a Value List as the source for you list box. You can create a recordset (basically an array on steroids) that you can navigate through to build your Value List.

Here is a concept based on a form with one List Box and two Command Buttons. I didn't add any error handlers which this will need before it is 'usable'.

'Global Declarations
Dim gdbsCurrent As DAO.Database
Dim grstValueList As DAO.Recordset
Dim glngPage As Integer

Private Sub Form_Load()
glngPage = 0
Set gdbsCurrent = CurrentDb
Set grstValueList = gdbsCurrent.OpenRecordset("[i]Single Field Query[/i]", _
dbOpenSnapshot)
grstValueList.MoveLast
End Sub

Private Sub cmdForward_Click()
List0_Page 24
End Sub

Private Sub cmdBackward_Click()
List0_Page -24
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
grstValueList.Close
Set grstValueList = Nothing
Set gdbsCurrent = Nothing
End Sub

Sub List0_Page(Number_Of As Long)
'This will move the display of the List Box
'[i]Number_Of[/i] records at a time
Dim lngStart As Long, lngStop As Long
Dim strValueList As String

'Check to see it Paging forward or Backwards
Select Case Number_Of
  Case Is < 0
    lngStop = glngPage + Number_Of - 2
    lngStart = lngStop + Number_Of
  Case 0
    Exit Sub
  Case Is > 0
    lngStart = glngPage
    lngStop = glngPage + Number_Of
End Select

'Now build the Value List by stepping through the
'recordset leaving glngPage set as a pointer to the
'last (or is it next?) record to display
For glngPage = lngStart To lngStop
  grstValueList.AbsolutePosition = glngPage
  strValueList = strValueList & grstValueList.Collect(0) & ";"
Next glngPage

'Set the RowSource of the ListBox
With Me.List0
  .RowSourceType = "Value List"
  .RowSource = strValueList
End With
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)