MoveFirst, MoveLast, MoveNext, MovePrevious Methods Example

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.

Sub MoveFirstX()

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

	' Open recordset from Authors table.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstAuthors = New ADODB.Recordset
	rstAuthors.CursorType = adOpenStatic
	rstAuthors.Open "SELECT au_id, au_fname, au_lname, city, state " & _
		"FROM Authors ORDER BY au_lname", strCnn

	With rstAuthors
		' Store record position.
		.MoveFirst
		ReDim avarPosition(.RecordCount)
		intSetIndex = 1
		Do Until .EOF
			avarPosition(intSetIndex) = !au_id
			intSetIndex = intSetIndex + 1
			.MoveNext
		Loop
		
		.MoveFirst
		Do While True
			' Get record position, then show current record
			' information and get user's method choice.
			
			For intGetIndex = 1 To UBound(avarPosition)
				If avarPosition(intGetIndex) = !au_id Then Exit For
			Next
			
			strMessage = "Name: " & !au_fName & " " & _
				!au_lName & vbCr & "Record " & _
				intGetIndex & " of " & _
				.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
		.Close
	End With

End Sub

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

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

End Sub

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