Move Method Example

This example prompts the user to select a record number. The example then copies the selected record from the Customer recordset in the Nwindex.mdb database to Sheet1.

Set db = Workspaces(0).OpenDatabase(Application _
    .DefaultFilePath & "\NWINDEX.MDB")
Set rs = db.OpenRecordset( _
    "SELECT [CUSTMR_ID], [COMPANY], [REGION] " _
    & "FROM Customer")
rs.MoveLast
lst = rs.RecordCount
Sheets("Sheet1").Activate
aDistance = Application.InputBox("What record (1 - " & _
        lst & ") do you want to copy", Type:=1)
If aDistance = False Or aDistance > lst Then
    Exit Sub
End If
rs.MoveFirst
rs.Move aDistance - 1
For i = 0 To 2
    ActiveCell.Offset(, i).Value = rs.Fields(i).Value
Next
rs.Close
db.Close