This example uses the Move method to position the record pointer based on user input.
Public Sub MoveX()
Dim rstAuthors As ADODB.Recordset
Dim strCnn As String
Dim varBookmark As Variant
Dim strCommand As String
Dim lngMove As Long
' Open recordset from Authors table.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set rstAuthors = New ADODB.Recordset
rstAuthors.CursorType = adOpenStatic
' Use client cursor to allow use of
' AbsolutePosition property.
rstAuthors.CursorLocation = adUseClient
rstAuthors.Open "SELECT au_id, au_fname, au_lname, city, state " & _
"FROM Authors ORDER BY au_lname", strCnn, , , adCmdText
rstAuthors.MoveFirst
Do While True
' Display information about current record and
' ask how many records to move.
strCommand = InputBox( _
"Record " & rstAuthors.AbsolutePosition & _
" of " & rstAuthors.RecordCount & vbCr & _
"Author: " & rstAuthors!au_fname & _
" " & rstAuthors!au_lname & vbCr & _
"Location: " & rstAuthors!City & _
", " & rstAuthors!State & vbCr & vbCr & _
"Enter number of records to Move " & _
"(positive or negative).")
If strCommand = "" Then Exit Do
' Store bookmark in case the Move goes too far
' forward or backward.
varBookmark = rstAuthors.Bookmark
' Move method requires parameter of data type Long.
lngMove = CLng(strCommand)
rstAuthors.Move lngMove
' Trap for BOF or EOF.
If rstAuthors.BOF Then
MsgBox "Too far backward! " & _
"Returning to current record."
rstAuthors.Bookmark = varBookmark
End If
If rstAuthors.EOF Then
MsgBox "Too far forward! " & _
"Returning to current record."
rstAuthors.Bookmark = varBookmark
End If
Loop
rstAuthors.Close
End Sub