Microsoft Access 2010 tutorial:retrieve data using recordset


Retrieve data by using recordset

VI.1. Retrieve data from a table by using recordset object

To Retrieve the data from the table through recordset object, you need to know the properties and methods of recordset object:

 - RecordsetObject(FieldIndex) or

 - RecordsetObject(“ObjectName”) or

 - RecordsetObject!FieldName

 - RecordsetObject.MoveFirst

 - RecordsetObject.MoveNext

 - RecordsetObject.MovePrevious

 - RecordsetObject.MoveLast

 - RecordsetObject.EOF

 - RecordsetObject.RecordCount

 - RecordsetObject.BOF

 - RecordsetObject.AbsolutePosition

V.1. 1. RecordsetObject(FieldIndex) or RecordsetObject(“ObjectName”)or RecordsetObject!FieldName

Syntax1: Reading data from Recordset object

                   Variable= RecordsetObject(Index)

                   or

                   Variable= RecordsetObject(“FieldName”)

                   or

                    Variable= RecordsetObject!FieldName

Index: Represents the index of each field of Recordset Object. In general, index is counted from zero(0); it mean that index zero represent field one, index one represent field two, index two represent field three and so forth.

FieldName: The field name of Recordset

If you want to assign data to recordset object, use the syntax2 below:

Syntax2:

                   RecordsetObject(Index)= Value

                  or

                    RecordsetObject(“FieldName”)= Value

                   or

                    RecordsetObject!FieldName= Value

For instance, you have the form as figure below:

Access 2010 read data form 

When you click the Fetch Data command button, the data is retrieved from Recordset(one record) to display in the text boxes(CategoryID, CategoryName). Look at the demonstrated figure below:

Acces 2010 read data from a table 

To read data from Recordset object to display in the two text boxes above, type the following code:

Dim Db As DAO.Database

Dim Rs As DAO.Recordset

Private Sub Form_Load()

 Set Db = CurrentDb

End Sub

 

Private Sub cmdRead_Click()

 Dim SqlText As String

 SqlText = "Select * From TblCategory"

 Set Rs = Db.OpenRecordset(SqlText, dbOpenDynaset)

'Assign the first field to txtCategortID text box

 txtCategoryID.Value = Rs(0) 

' Assign the second field to txtCategortName text box

  txtCategoryName.Value = Rs(1) 

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.