Move Method Example (VB)

This example uses the Move method to position the record pointer based on user input.
'BeginMoveVB

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

Public Sub MoveX()

    ' connection and recordset variables
   Dim rstAuthors As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strSQLAuthors As String
    ' record variables
   Dim varBookmark As Variant
   Dim strCommand As String
   Dim lngMove As Long

   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=a-iresmi2000;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 allow use of AbsolutePosition property
   strSQLAuthors = "SELECT au_id, au_fname, au_lname, city, state FROM Authors ORDER BY au_lname"
   rstAuthors.Open strSQLAuthors, strCnxn, adOpenStatic, adLockOptimistic, adCmdText
      
   rstAuthors.MoveFirst

   Do
      ' Display information about current record and
      ' ask how many records to move

      strCommand = InputBox( _
         "Record " & rstAuthors.AbsolutePosition & _
         " of " & rstAuthors.RecordCount & vbCr & _
         "Author: " & rstAuthors!au_fname & _
         " " & rstAuthors!au_lname & vbCr & _
         "Location: " & rstAuthors!city & _
         ", " & rstAuthors!State & vbCr & vbCr & _
         "Enter number of records to Move " & _
         "(positive or negative).")

        ' this is for exiting the loop
      If strCommand = "" Then Exit Do

      ' Store bookmark in case the Move goes too far
      ' forward or backward
      varBookmark = rstAuthors.Bookmark

      ' Move method requires parameter of data type Long
      lngMove = CLng(strCommand)
      rstAuthors.Move lngMove

      ' Trap for BOF or EOF
      If rstAuthors.BOF Then
         MsgBox "Too far backward! Returning to current record."
         rstAuthors.Bookmark = varBookmark
      End If
      If rstAuthors.EOF Then
         MsgBox "Too far forward!  Returning to current record."
         rstAuthors.Bookmark = varBookmark
      End If
   Loop
   
    ' clean up
   rstAuthors.Close
   Cnxn.Close
   Set rstAuthors = Nothing
   Set Cnxn = Nothing

End Sub
'EndMoveVB
Move Method Example (VB)

See Also
