VBA example - Microsoft Access: move records in DAO recordset

Move records in DAO recordset



In this example, you will learn Recordset Methods MoveFirst, MoveLast, Move Previous,  and MoveNext to move records in recordset object. The example illustrates a CategoryInfo1 form that has two text boxes(CategoryID, CategoryName) and four command buttons(MoveFirst, MoveLast, MovePrevious, MoveNext). See the figure below:

 VBA example - Microsoft Access VBA CategoryInfo1 Form  

-When the form loads, the first record displays in the two text boxes  automatically(See CategoryInfo1 form above).

-If you click MoveFirst command button, the first record will display in the text boxes.

-If you click MoveLast command button, the Last record will display in the text boxes.

-For MovePreviouse and MoveNext command buttons, they will display the previous and next records in the two text Boxes if you click them.

To have a form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA CateInfo1 Form Design 

Note: Unselect Use Control Wizards before you drag these controls to the form.

- Drag and drop two text boxes, and four command buttons.

- Set Name properties of two Text Boxes:

Name: txtCategoryID

Name: txtCategoryName

- Set Name and Caption properties of four command buttons:

Name: CmdFirst

Name: CmdPrevious

Name: CmdNext

Name: CmdLast

 

Then apply the VBA Code below:

 

Option Compare Database

Dim Rs As DAO.Recordset

 ....................................................................................

Private Sub Form_Load()

 ' Use current database to open recordset object in dbOpenDynaset type

  Set Rs = CurrentDb.OpenRecordset("TblCategory", dbOpenDynaset)

  DisplayData

End Sub

 ....................................................................................

Private Sub CmdFirst_Click()

 ' Move to first record

   Rs.MoveFirst

   DisplayData

End Sub

 ....................................................................................

Private Sub CmdLast_Click()

 ' Move to last record

   Rs.MoveLast

   DisplayData

End Sub

 ....................................................................................

Private Sub CmdNext_Click()

' Move to next record

 On Error GoTo aa

 If Not Rs.EOF Then

    Rs.MoveNext

    DisplayData

    aa:  Resume Next

  End If

End Sub

 ....................................................................................

Private Sub CmdPrevious_Click()

' Move to previous record

On Error GoTo aa

If Not Rs.BOF Then

    Rs.MovePrevious

   DisplayData

   aa: Resume Next

 End If

End Sub

.................................................................................... 

Sub DisplayData()

 ' Display a record to text boxes

   txtCategoryID = Rs(0).Value

   txtCategoryName = Rs(1).Value

End Sub




Comments

MVS Srinu comment

 MVS Srinu

You have written exellent code dude...

04-27-2014
Name:
Comment:
Enter the text:
CAPTCHA image

.........................................................................................................................Home | Forum | About | Contact
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.
Copyright @ 2011-2013 worldbestlearningcenter. All Rights Reserved.
Home Programming Web DB & App Questions Exercises Tips Programs FAQs Download About
Computer-Wbest
Popular Pages
You might like