Using a Data Control to Scroll Up and Down in a Recordset
This article is reprinted from the Microsoft Knowledge Base. To view the article, maximize your help window. This information applies to Visual Basic for Windows, versions 2.0 and 3.0.
The data control provided with Visual Basic does not provide an
automatic way to scroll up or down in a recordset by groups (pages)
of records. This article shows you how to use the MoveNext and
MovePrevious methods to scroll up or down in a recordset by groups
(pages) of records without displaying all the records.
Usually, when you use the MoveNext and MovePrevious methods to scroll
up or down by a specified number of records, all the records are
displayed as you move through them. This is undesirable behavior if
you want a way to scroll through the recordset by pages.
In order to display only the record you have scrolled to, without
displaying all the records in between, you need to use the Clone
method to clone the data control's recordset.
Once you clone the recordset, you can use the MoveNext and MovePrevious
methods to move to the desired record within the cloned recordset. Then
set the Bookmark property of the original recordset to the Bookmark
property of the cloned recordset. This makes the desired record the
current record in the original recordset and causes the fields of this
record to be displayed in the bound data controls.
Perform the following steps to create an example program that
demonstrates how to scroll up and down by pages in a data control's
1. Start Visual Basic or from the File menu, choose New Project (ALT, F, N)
if Visual Basic is already running. Form1 is created by default.
2. Add a data control (Data1) to Form1.
3. Add two text boxes (Text1 and Text2) to Form1.
4. Add two command buttons (Command1 and Command2) to Form1.
5. Using the following table as a guide, set the properties of the
controls you added in steps 2, 3, and 4.
Control Name Property New Value Comment
Command1 Caption "Page Up"
Command2 Caption "Page Down"
Data1 DatabaseName BIBLIO.MDB Provide the full path to
to this file, which
should be in the Visual
Basic directory -- C:\VB
Data1 RecordSource Authors
Text1 DataSource Data1
Text1 DataField AU_ID
Text2 DataSource Data1
Text2 DataField Author
6. Add the following code to the general declarations section of Form1:
Const PAGEUP = 1
Const PAGEDOWN = 2
Const Records_per_Page = 10
7. Add the following procedure to Form1:
Sub Page (RecSet As Dynaset, ByVal iDirection As Integer, ByVal
Records As Integer)
Dim dsClone As Dynaset
Dim i As Integer
'Copy the visible recordset. This is necessary so that you can
'move through the clone recordset without displaying each record.
Set dsClone = RecSet.Clone()
'Set the current record of the cloned recordset to the current
'record of the visible recordset.
dsClone.Bookmark = RecSet.Bookmark
'Scroll up or down N number of records in the cloned recordset.
i = 1
Do While i <= Records And Not dsClone.EOF And Not dsClone.BOF
If iDirection = PAGEUP Then
i = i + 1
'If the above loop caused a BOF or EOF condition, move to the
'beginning or end of the recordset as appropriate.
If dsClone.BOF And iDirection = PAGEUP Then
ElseIf dsClone.EOF And iDirection = PAGEDOWN Then
'Change the bookmark of the visible record set to the bookmark
'of the desired record. This makes the current record of the
'visible recordset match the record moved to in the cloned
'dynaset. The fields of the record are displayed in the data
'bound controls without displaying any intervening records.
RecSet.Bookmark = dsClone.Bookmark
8. Add the following code to the Command1_Click event for Form1:
Sub Command1_Click ()
'Scroll up 10 records in the recordset associated with Data1
Page Data1.RecordSet, PAGEUP, Records_per_Page
9. Add the following code to the Command2_Click event for Form1:
Sub Command2_Click ()
'Scroll down 10 records in the recordset associated with Data1
Page Data1.RecordSet, PAGEDOWN, Records_per_Page
10. From the Run menu, choose Start (ALT, R, S), or press the F5 key
to run the program.
Click the "Page Up" or "Page Down" button to scroll up or down in
10-record increments. Change the value of Records_per_Page to modify