BOF, EOF, and Bookmark Properties Example (ADO)


		Contents Index Topic Contents

BOF, EOF, and Bookmark Properties Example

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 = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	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