MoveFirst, MoveLast, MoveNext, MovePrevious Methods Example

This example uses the MoveFirst, MoveLast, MoveNext, and MovePrevious methods to move the record pointer of a Recordset based on the supplied command. The MoveAny procedure is required for this procedure to run.

Sub MoveFirstX()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim strMessage As String
   Dim intCommand As Integer

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = dbsNorthwind.OpenRecordset( _
      "SELECT FirstName, LastName FROM Employees " & _
      "ORDER BY LastName", dbOpenSnapshot)

   With rstEmployees
      ' Populate Recordset.
      .MoveLast
      .MoveFirst
      Do While True
         ' Show current record information and get user's
         ' method choice.
         strMessage = "Name: " & !FirstName & " " & _
            !LastName & vbCr & "Record " & _
            (.AbsolutePosition + 1) & " of " & _
            .RecordCount & vbCr & vbCr & _
            "[1 - MoveFirst, 2 - MoveLast, " & vbCr & _
            "3 - MoveNext, 4 - MovePrevious]"
         intCommand = Val(Left(InputBox(strMessage), 1))
         If intCommand < 1 Or intCommand > 4 Then Exit Do

         ' Call method based on user's input.
         MoveAny intCommand, rstEmployees
      Loop
      .Close
   End With

   dbsNorthwind.Close

End Sub

Sub MoveAny(intChoice As Integer, _
   rstTemp As Recordset)

   ' Use specified method, trapping for BOF and EOF.
   With rstTemp
      Select Case intChoice
         Case 1
            .MoveFirst
         Case 2
            .MoveLast
         Case 3
            .MoveNext
            If .EOF Then
               MsgBox "Already at end of recordset!"
               .MoveLast
            End If
         Case 4
            .MovePrevious
            If .BOF Then
               MsgBox "Already at beginning of recordset!"
               .MoveFirst
            End If
      End Select
   End With

End Sub