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.