BOF, EOF, and Bookmark Properties Example (VB)

BOF, EOF, and Bookmark Properties Example (VB)

This example uses the BOF and EOF properties to display a message if a user tries to move past the first or last record of a Recordset. It uses the Bookmark property to let the user flag a record in a Recordset and return to it later.

Public Sub BOFX()

    Dim rstPublishers As ADODB.Recordset
    Dim strCnn As String
    Dim strMessage As String
    Dim intCommand As Integer
    Dim varBookmark As Variant

    ' Open recordset with data from Publishers table.
    strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set rstPublishers = New ADODB.Recordset
    rstPublishers.CursorType = adOpenStatic
    ' Use client cursor to enable AbsolutePosition property.
    rstPublishers.CursorLocation = adUseClient
    rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
        "ORDER BY pub_name", strCnn, , , adCmdText

    rstPublishers.MoveFirst

    Do While True
        ' Display information about current record
        ' and get user input.
        strMessage = "Publisher: " & rstPublishers!pub_name & _
            vbCr & "(record " & rstPublishers.AbsolutePosition & _
            " of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
            "Enter command:" & vbCr & _
            "[1 - next / 2 - previous /" & vbCr & _
            "3 - set bookmark / 4 - go to bookmark]"
        intCommand = Val(InputBox(strMessage))

        Select Case intCommand
            ' Move forward or backward, trapping for BOF
            ' or EOF.
            Case 1
                rstPublishers.MoveNext
                If rstPublishers.EOF Then
                    MsgBox "Moving past the last record." & _
                        vbCr & "Try again."
                    rstPublishers.MoveLast
                End If
            Case 2
                rstPublishers.MovePrevious
                If rstPublishers.BOF Then
                    MsgBox "Moving past the first record." & _
                        vbCr & "Try again."
                    rstPublishers.MoveFirst
                End If

            ' Store the bookmark of the current record.
            Case 3
                varBookmark = rstPublishers.Bookmark

            ' Go to the record indicated by the stored
            ' bookmark.
            Case 4
                If IsEmpty(varBookmark) Then
                    MsgBox "No Bookmark set!"
                Else
                    rstPublishers.Bookmark = varBookmark
                End If

            Case Else
                Exit Do
        End Select

    Loop

    rstPublishers.Close

End Sub

This example uses the Bookmark and Filter properties to create a limited view of the Recordset. Only records referenced by the array of bookmarks are accessible.

Public Sub BOFX2()

Dim rs As New ADODB.Recordset
Dim bmk(10)

rs.CursorLocation = adUseClient
rs.ActiveConnection = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=;" 

rs.Open "select * from Authors", , adOpenStatic, adLockBatchOptimistic
Debug.Print "Number of records before filtering: ", rs.RecordCount

ii = 0
While rs.EOF <> True And ii < 11
    bmk(ii) = rs.Bookmark
    ii = ii + 1
    rs.Move 2
Wend
rs.Filter = bmk
Debug.Print "Number of records after filtering: ", rs.RecordCount

rs.MoveFirst
While rs.EOF <> True
    Debug.Print rs.AbsolutePosition, rs("au_lname")
    rs.MoveNext
Wend
    
End Sub