This example uses the Move method to position the record pointer based on user input.
Sub MoveX()
Dim rstAuthors As ADODB.Recordset
Dim strCnn As String
Dim avarPosition() As Variant
Dim intSetIndex As Integer
Dim intGetIndex As Integer
Dim varBookmark As Variant
Dim strCommand As String
Dim lngMove As Long
' 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 the current record position, then display information
' about current record and ask how many records to move.
For intGetIndex = 1 To UBound(avarPosition)
If avarPosition(intGetIndex) = !au_id Then Exit For
Next
strCommand = InputBox( _
"Record " & intGetIndex & " of " & _
.RecordCount & vbCr & _
"Author: " & !au_fname & " " & !au_lname & _
vbCr & "Location: " & !City & _
", " & !State & vbCr & vbCr & _
"Enter number of records to Move " & _
"(positive or negative).")
If strCommand = "" Then Exit Do
' Store bookmark in case the Move doesn't work.
varBookmark = .Bookmark
' Move method requires parameter of data type Long.
lngMove = CLng(strCommand)
.Move lngMove
' Trap for BOF or EOF.
If .BOF Then
MsgBox "Too far backward! " & _
"Returning to current record."
.Bookmark = varBookmark
End If
If .EOF Then
MsgBox "Too far forward! " & _
"Returning to current record."
.Bookmark = varBookmark
End If
Loop
.Close
End With
End Sub
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.