Microsoft Access 2010 tutorial:recordset methods move records


Recordset methods move records

VI.1.2. RecordsetObject.MoveFirst

It is the method of Recordset object. The RecordsetObject.MoveFirst is used to move to the first record of Recordset object.

VI.1.3. RecordsetObject.MoveNext
The RecordsetObject.MoveNext is used to move to the next record of the Recordset object.
VI.1.4. RecordsetObject.MovePrevious
This method of Recordset object is used to move back one record after the current record of Recordset object.
VI.1.5. RecordsetObject.MoveLast
It is the last method of moving record of the Recordset object. RecordsetObject.MoveLast is used to move to the last record of Recordset object.

The example below uses the four moving methods of Recordset object including MoveFirst, MoveNext, MovePrevious, and MoveLast. For instance, you have the form as figure:

Access 2010 moving records in recordset

- When the form loaded, a record was retrieved from Recordset object to display on the form like the figure above.

- When you click the First command button, Recordset object will move to the first record.

-When you click the Previous command button, Recordset object will move back one record after the current record.

-When you click the Next command button, Recordset object will move to the next record of the current record.

-When you click the Last command button, Recordset object will move to the last record.

The VBA code that is applicable to the example is shown below:


Option Compare Database
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim SqlText As String

Private Sub Form_Load()
    Set Db = CurrentDb
    SqlText = " Select * From TblCategory"
    Set Rs = Db.OpenRecordset(SqlText, dbOpenDynaset)
    Call display
End Sub


Sub display()   
    txtCategoryID.Value = Rs(0)
    txtCategoryName.Value = Rs(1)
End Sub

 
Private Sub cmdFirst_Click()
    Rs.MoveFirst
    Call display
End Sub
 
Private Sub cmdNext_Click()
   On Error GoTo aa    ' Track error
    If Not Rs.EOF Then    ' If the record is not the last record
        Rs.MoveNext          ' then it will move to the next record
        Call display
aa: Resume Next  ' Though the error occurs, the program still continues

    End If
End Sub
 
Private Sub cmdPrevious_Click()
   On Error GoTo aa       
    If Not Rs.BOF Then
        Rs.MovePrevious
        Call display
aa: Resume Next  
    End If
End Sub
 
Private Sub cmdLast_Click()
    Rs.MoveLast
    Call display
End Sub

' When the form is closed, you need to close and clear Recordset and Database objects from the memory.


Private Sub Form_Close()
    Rs.Close
    Db.Close
    Set Db = Nothing
    Set Rs = Nothing
End Sub

 


Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.