MoveFirst, MoveLast, MoveNext, and MovePrevious Methods Example (VB)

This example uses the MoveFirst, MoveLast, MoveNext, and MovePrevious methods to move the record pointer of a Recordset based on the supplied command. The MoveAny procedure is required for this procedure to run.

'BeginMoveFirstVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub MoveFirstX()

    ' connection and recordset variables
   Dim rstAuthors As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strSQLAuthors
    ' record variables
   Dim strMessage As String
   Dim intCommand As Integer

   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=pubs;User Id=sa;Password=;"
   Cnxn.Open strCnxn

   ' Open recordset from Authors table
   Set rstAuthors = New ADODB.Recordset
   rstAuthors.CursorLocation = adUseClient
   ' Use client cursor to enable AbsolutePosition property
   strSQLAuthors = "Authors"
   rstAuthors.Open strSQLAuthors, Cnxn, adOpenStatic, adLockReadOnly, adCmdTable

   ' Show current record information and get user's method choice
   Do

      strMessage = "Name: " & rstAuthors!au_fname & " " & _
         rstAuthors!au_lname & vbCr & "Record " & _
         rstAuthors.AbsolutePosition & " of " & _
         rstAuthors.RecordCount & vbCr & vbCr & _
         "[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
         "3 - MoveNext, 4 - MovePrevious]"
      intCommand = Val(Left(InputBox(strMessage), 1))
      
       ' for exiting the loop
      If intCommand < 1 Or intCommand > 4 Then Exit Do

    ' Use specified method while trapping for BOF and EOF
    Select Case intCommand
       Case 1
          rstAuthors.MoveFirst
       Case 2
          rstAuthors.MoveLast
       Case 3
          rstAuthors.MoveNext
          If rstAuthors.EOF Then
             MsgBox "Already at end of recordset!"
             rstAuthors.MoveLast
          End If
       Case 4
          rstAuthors.MovePrevious
          If rstAuthors.BOF Then
             MsgBox "Already at beginning of recordset!"
             rstAuthors.MoveFirst
          End If
    End Select
   
   Loop
   
    ' clean up
   rstAuthors.Close
   Cnxn.Close
   Set Cnxn = Nothing
   Set rstAuthors = Nothing
   
End Sub

'EndMoveFirstVB

MoveFirst, MoveLast, MoveNext, and MovePrevious Methods Example (VB)

See Also