Lesson 22: Introduction to Record Sets

	Introduction to Record Sets

A Record Set

Overview

The records from a table are grouped in an object referred to as a set of records also called a record set. To support record sets, the Microsoft Access Object library is equipped with a class named Recordset. To use it, declare a variable of type Object or of type Recordset. Before using it, use the Set operator to assign it to the object that would use it. Here is an example:

Private Sub cmdRecordset_Click()
    ' Create a recordset
    Dim rstTimeSheet As Recordset
    
    ' Specify that the record set points to the records of this form
    Set rstTimeSheet = ...
End Sub

Most of the time, you use an Object object or a Recordset object without being aware because the database engine handles all or most of the necessary routines behind the scenes. In some other cases, you must create or initialize an Object object or a Recordset object in order to use it. To do this, you have various alternatives. In fact, the way you create a Recordset object may depend on the library you are using at the time you need to "grab" the set of records.

Practical Learning: Introducing Record Sets

Start Microsoft Access
Open the KoloBank1 database from Lesson 20

The Type of Recordset Objects

A record set primarily contains records. The records can be retrieved from a table or gotten directly from a table. When creating a record set (as we will see in the next few sections), you can specify whether to use one or more tables and what record(s) would be included. How the record set is created, the number of tables, the number of records, and the types of operations that can be performed on the records lead to various types of record sets:

If your Recordset object includes only one table, it is referred to as a Table record set
If a record set includes one or more tables, it is called a Dynaset. This type allows adding, editing, updating, or deleting records that are part of the Recordset object
A record set is called a Snapshot if it allows you to view the records of one or more tables, navigating back and forth in the set, but you cannot make changes to the records
A Recordset object is referred to as Forward-Only if you can view its records without changing them but you can only move forward. This means that, if you get to a record, examine it, and move to the next record, you cannot refer back to a record you left behind
If you create a query based on data from one or more tables and allows the users to navigate back and forth with the ability to make changes to records, this type of record set is referred to as Dynamic

The Recordset Object of a Control

Some controls, such as the combo box or the list box, are meant to hold a list of values. We also know that a form or a report is primarily created to show one or more records. Such controls hold their own record set. If you create a Recordset object and want to initialize it with the values held in the form where it is called, you can simply assign it Me.Recordset. Here is an example:

Private Sub cmdRecordset_Click()
    ' Create a recordset
    Dim rstTimeSheet As Recordset
    
    ' Specify that the record set points to the records of this form
    Set rstTimeSheet = Me.Recordset
End Sub

When a form is equipped to display the values of a list, that form has a Recordset object that represents its records. Once again, remember that there are various other ways you can initialize a Recordset object.

Opening a Record Set

With the Microsoft Access Object Library, to create a Recordset object using a table or a query that is associated with a database, you can call the OpenRecordset() method of that database. The syntax of this method is:

Set Variable = Database.OpenRecordset(Source[, Type [, Options [, Lockedits ]]])

The Variable can be an Object variable you would have declared as a placeholder for a Recordset object. Database must represent a valid database. It can be the current database or another one. The only required argument of this method is the Source, which is passed as a string. This can be the name of a table or a query. Here is an example:

Private Sub cmdGetEmployees_Click()
    Dim dbKoloBank As Object
    Dim rstEmployees As Object

    Set dbKoloBank = CurrentDb
    Set rstEmployees = dbKoloBank.OpenRecordset("Employees")
End Sub

Using a Table or a Query

The above code supposes that you would go through a database to create a record set. It can be used to create a record set from the current database or from a closed database. If you are working in a database that has its own objects and you want to create a record set using one of these objects, each database object that can act as a data source, including tables and queries, is equipped with an OpenRecordset() method. Its syntax is:

Set Variable = object.OpenRecordset([Type [, Options [, Lockedits ]]])

Notice that, this time, you specify neither the database nor the name of the object.

Closing a Record Set

After using a Recordset object, you should (strongly) close it. To close a record set, you can call its Close() method. Here is an example:

Private Sub cmdTableReference_Click()
    Dim rstEmployees As Object
    
    . . . Use the record set here
    
    rstEmployees.Close
End Sub

After closing the record set, you should release the resources it was using and make them available to other applications that would need them. To do this, assign the Nothing value to the Recordset object. Here is an example:

Private Sub cmdTableReference_Click()
    Dim rstEmployees As Recordset
    
    . . . Use the record set here
    
    rstEmployees.Close
    Set rstEmployees = Nothing
End Sub

If you were using the ActiveConnection property to connect to the current database, to close it, the Recordset object is equipped with a property named ActiveConnection. Before closing the Recordset object, you should first assign Nothing to its ActiveConnection property.

Opening a Record Set in the Current Database

The values held by a table are referred to as a record set. Before performing data entry, you must programmatically open the table. That is, you must open the record set. To make this possible in the Microsoft Access Object Library, its Database class is equipped with a method named OpenRecordset. This method can take as argument the name of the table on which you want to perform data entry. Based on this, here is an example of calling the method:

Private Sub cmdDataEntry_Click()
    curDatabase.OpenRecordset("Employees")
End Sub

Once you have the record set, you can perform data entry on it. To support this, the OpenRecordset() method returns an object named Recordset. If you want to use the table after opening the record set, get the return value of the method. To do this, declare a variable of type Object or Recordset and assign it to the return value of this method call. Here is an example:

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
End Sub

After using the record set, free the memory it was using by assigning Nothing to it. This would be done as follows:

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    . . . Use the record set here

    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Introduction to the Records of a Record Set

Introduction to Data Entry

After opening then getting a record set, you can create a new record. To support the creation of a record, the Recordset class is equipped with a method named AddNew. Here is an example of calling it:

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew

    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

This method only indicates that you want to create a new record. To actually create a record, you must specify a value for each column of the table. To support this, the Recordset class is equipped with an indexed property. The indexed property of a record set represents an object of type Field. One of the properties of the Field class is the Name, which is the name of a column. Therefore, when accessing the indexed property of the Recordset class, pass the name of the column as argument (the name of the column whose value you want to specify).

Another property of the Field class is named Value. To specify the new value of a column, use this property to assign the desired value to the column. Here is an example of specifying a new value for a column named FirstName from a table named Employees:

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("FirstName").Value = "Helene"
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

If you had configured the field to automatically increment its values, make sure you don't provide a value for that field.

Updating a Record

After adding a new record, you must ask the record set to receive the new value. To support this, the Recordset class is equipped with a method named Update. Call this method after specifying a value for each column. Here is example of calling this method:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim fldFirstName As Object, fldLastName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Employees
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set fldFirstName = tblEmployees.CreateField("FirstName", dbText)
    tblEmployees.Fields.Append fldFirstName
    
    Set fldLastName = tblEmployees.CreateField("LastName", dbText)
    tblEmployees.Fields.Append fldLastName
    
    ' Add the Employees table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("FirstName").Value = "Helene"
    rstEmployees("LastName").Value = "Mukoko"
    rstEmployees.Update
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Practical Learning: Introducing Data Entry in a Record Set

In the Navigation Pane, right-click NewTimeSheet and click Design View
On the form, right-click the Submit Time Sheet button and click Build Event
In the Choose Builder dialog box, click Code Builder and click OK