Move Method Example (VB)

Move Method Example (VB)

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

Public Sub MoveX()

    Dim rstAuthors As ADODB.Recordset
    Dim strCnn As String
    Dim varBookmark As Variant
    Dim strCommand As String
    Dim lngMove As Long

    ' Open recordset from Authors table.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set rstAuthors = New ADODB.Recordset
    rstAuthors.CursorType = adOpenStatic
    ' Use client cursor to allow use of 
    ' AbsolutePosition property.
    rstAuthors.CursorLocation = adUseClient
    rstAuthors.Open "SELECT au_id, au_fname, au_lname, city, state " & _
        "FROM Authors ORDER BY au_lname", strCnn, , , adCmdText
        

    rstAuthors.MoveFirst

    Do While True
        ' 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).")

        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
    rstAuthors.Close

End Sub