BOF, EOF Properties Example

This example demonstrates how the BOF and EOF properties let the user move forward and backward through a Recordset.

Sub BOFX()

	Dim rstPublishers As ADODB.Recordset
	Dim strCnn As String
	Dim avarPosition() As Variant
	Dim intSetIndex As Integer
	Dim intGetIndex As Integer
	Dim strMessage As String

	' 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
	rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
		"ORDER BY pub_name", strCnn

	With rstPublishers
		' Store record position.
		.MoveFirst
		ReDim avarPosition(.RecordCount)
		intSetIndex = 1
		Do Until .EOF
			avarPosition(intSetIndex) = !pub_id
			intSetIndex = intSetIndex + 1
			.MoveNext
		Loop
		
		.MoveFirst

		Do While True
			' Get the current record position, then display information
			' about current record and get user input.
			For intGetIndex = 1 To UBound(avarPosition)
				If avarPosition(intGetIndex) = !pub_id Then Exit For
			Next
			strMessage = "Publisher: " & !pub_name & _
				vbCr & "(record " & intGetIndex & _
				" of " & .RecordCount & ")" & vbCr & vbCr & _
				"Enter 1 to go forward, 2 to go backward:"

			' Move forward or backward and trap for BOF or EOF.
			Select Case InputBox(strMessage)
				Case 1
					.MoveNext
					If .EOF Then
						MsgBox _
							"End of the file!" & vbCr & _
							"Pointer being moved to last record."
						.MoveLast
					End If

				Case 2
					.MovePrevious
					If .BOF Then
						MsgBox _
							"Beginning of the file!" & vbCr & _
							"Pointer being moved to first record."
						.MoveFirst
					End If

				Case Else
					Exit Do
			End Select

		Loop

		.Close
	End With

End Sub

© 1997 Microsoft Corporation. All rights reserved. Terms of Use.