Move Method Example

This example uses the Move method to position the record pointer based on user input.

Sub MoveX()

   Dim dbsNorthwind As Database
   Dim rstSuppliers As Recordset
   Dim varBookmark As Variant
   Dim strCommand As String
   Dim lngMove As Long

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstSuppliers = _
      dbsNorthwind.OpenRecordset("SELECT CompanyName, " & _
      "City, Country FROM Suppliers ORDER BY CompanyName", _
      dbOpenDynaset)

   With rstSuppliers
      ' Populate recordset.
      .MoveLast
      .MoveFirst

      Do While True
         ' Display information about current record and ask 
         ' how many records to move.
         strCommand = InputBox( _
            "Record " & (.AbsolutePosition + 1) & " of " & _
            .RecordCount & vbCr & "Company: " & _
            !CompanyName & vbCr & "Location: " & !City & _
            ", " & !Country & 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

   dbsNorthwind.Close

End Sub