Using a Data Control to Scroll Up and Down in a Recordset

See AlsoVMZ7S4

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.

Summary:

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.

More Information:

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

recordset:

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

            dsClone.MovePrevious

         Else

            dsClone.MoveNext

         End If

         i = i + 1

      Loop

      '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

         dsClone.MoveFirst

      ElseIf dsClone.EOF And iDirection = PAGEDOWN Then

         dsClone.MoveLast

      End If

      '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

   End Sub

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

    End Sub

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

   End Sub

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

the pagesize.