Move Method Example

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.