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.

'BeginBOFVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
    
Public Sub BOFX()

    'recordset and connection variables
   Dim Cnxn As ADODB.Connection
   Dim rstPublishers As ADODB.Recordset
   Dim strCnxn As String
   Dim strSQLPubs As String
    'record variables
   Dim strMessage As String
   Dim intCommand As Integer
   Dim varBookmark As Variant
    
    ' 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 and use client cursor
    ' to enable AbsolutePosition property
   Set rstPublishers = New ADODB.Recordset
   strSQLPubs = "SELECT pub_id, pub_name FROM publishers ORDER BY pub_name"
   rstPublishers.Open strSQLPubs, strCnxn, adUseClient, adOpenStatic, adCmdText

   rstPublishers.MoveFirst
   Do Until rstPublishers.EOF
      ' 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))

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

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

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

         Case Else
            Exit Do
      End Select
   Loop

    ' clean up
   Cnxn.Close
   rstPublishers.Close
   Set Cnxn = Nothing
   Set rstPublishers = Nothing

End Sub
'EndBOFVB

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.

'BeginBOF2VB
Public Sub BOFX2()

    'recordset and connection variables
    Dim rs As New ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strSQL As String
    Dim strCnxn As String
    
    Dim bmk(10)
       
        'open the recordset client-side
    Set rs = New ADODB.Recordset
    strSQL = "Select * from Authors"
    rs.Open strSQL, Cnxn, adUseClient, adLockReadOnly, adCmdText
    Debug.Print "Number of records before filtering: ", rs.RecordCount
    
    Dim ii As Integer
    ii = 0
    
    If rs.EOF <> True And ii < 11 Then
        Do
            bmk(ii) = rs.Bookmark
            ii = ii + 1
        rs.Move 2
        Loop Until rs.EOF
    End If
    
    rs.Filter = bmk
    Debug.Print "Number of records after filtering: ", rs.RecordCount
    
    rs.MoveFirst
    If rs.EOF <> True Then
        Do
            Debug.Print rs.AbsolutePosition, rs("au_lname")
            rs.MoveNext
        Loop Until rs.EOF
    End If
        
     ' clean up
    rs.Close
    Cnxn.Close
    Set rs = Nothing
    Set Cnxn = Nothing
        
End Sub
'EndBOF2VB

BOF, EOF, and Bookmark Properties Example (VB)

See Also