How to Use Data Control to Scroll Up and Down in a Recordset

- Professional Edition of Microsoft Visual Basic for Windows, version

  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.