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

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.

Public Sub MoveFirstX()

    Dim rstAuthors As ADODB.Recordset
    Dim strCnn As String
    Dim strMessage As String
    Dim intCommand As Integer

    ' 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 enable AbsolutePosition property.
    rstAuthors.CursorLocation = adUseClient
    rstAuthors.Open "Authors", strCnn, , , adCmdTable

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

        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))
        If intCommand < 1 Or intCommand > 4 Then Exit Do

        ' Call method based on user's input.
        MoveAny intCommand, rstAuthors
    Loop
    rstAuthors.Close

End Sub

Public Sub MoveAny(intChoice As Integer, _
    rstTemp As Recordset)

    ' Use specified method, trapping for BOF and EOF.
    Select Case intChoice
        Case 1
            rstTemp.MoveFirst
        Case 2
            rstTemp.MoveLast
        Case 3
            rstTemp.MoveNext
            If rstTemp.EOF Then
                MsgBox "Already at end of recordset!"
                rstTemp.MoveLast
            End If
        Case 4
            rstTemp.MovePrevious
            If rstTemp.BOF Then
                MsgBox "Already at beginning of recordset!"
                rstTemp.MoveFirst
            End If
    End Select

End Sub