Introduction to Record Sets in the Microsoft Access Object Library

Introduction to a Record Set

Overview

The records of a table or a query are grouped in an object referred to as a set of records also called a record set. A record set is of type Object. To use it, declare a variable of type Object.  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 Object
    
    ' 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 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 in order to use it. You have various options.

Practical Learning: Introducing Record Sets

1. Start Microsoft Access
2. In the list of files, click Payroll System1 from the previous lesson
3. In the Navigation Pane, right-click the Application Preparation form and click Design View

The Type of Recordset Objects

Based on their behaviors, there are various categories of record sets:

• A table-type is a record set whose values come straight from a table
• A dynaset is a record set created from a query. Actually, the idea is that, after creating the record set, if the (any) value in the set changes, the record set is "dynamically" updated, which means the result you see in the record is the actual data
• A snapshot is a list of records based on the way the records were when the set was created or generated. If changes were made on the records (such as somebody changing a value of a field or somebody deleting a value in a field) after you created the record set, the snapshot record set would not show the changes. If you want to see the changes, you must refresh the record set, which is equivalent to rebuilding the record set

The Record Set 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 record set 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 Object
    
    ' 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 record set that represents its records. Once again, remember that there are various other ways you can initialize a record set.

Opening a Record Set

To let you create a record set, the database class of the Microsoft Access Object Library provides a method named OpenRecordset. The syntax of this method is:

Public Function OpenRecordset(ByVal Expression As String,
			      ByVal Optional Type As Variant,
			      ByVal Optional OptionsAs Variant,
			      ByVal Option Lockedits As Variant) As Object

The variable that calls this method can be of type Object. It can be the current database or another database. The only required argument of this method is the Expression, 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

Closing a Record Set

After using a record set, you should (strongly) close it. To let you close a record set, its class is equipped with a method named Close. Here is an example of calling it:

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

As an option, after closing the record set, you can 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 record set variable. Here is an example:

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

Introduction to the Records of a Record Set

Introduction to Data Entry

After opening and then getting a record set, you can create a new record. To support the creation of a record, the record set is equipped with a method named AddNew method. 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, specify a value for the columns of the table. To do this, you can access a field using its name indexed property. It is equipped with a property named Value. To specify the new value of a column, assign the desired value to this property applied 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("Something").Value =  ...
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Updating a Record

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

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 = ...
    rstEmployees("LastName").Value = ...
    rstEmployees.Update
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Data Entry in a Record Set Based on Type

Data Entry With Strings

In a record set, the value of a string-based field is provided in double-quotes. Here are examples:

Private Sub cmdAddEmployee_Click()
On Error GoTo cmdAddEmployee_Click_Error

    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        .AddNew
        .Fields("EmployeeNumber").Value = "608-285"
        .Fields("FirstName").Value = "Elizabeth"
        .Fields("LastName").Value = "Eloundou"
       .Update
    End With

    Set rstEmployees = Nothing
    Set curDatabase = Nothing
    
    MsgBox "The employee has been hired.", _
           vbOKOnly Or vbInformation, "Kolo Bank"

cmdAddEmployee_Click_Exit:
    Exit Sub
    
cmdAddEmployee_Click_Error:
    MsgBox "For some strange reason, the employee was not hired.", _
           vbOKOnly Or vbInformation, "Kolo Bank"
    Resume cmdAddEmployee_Click_Exit
End Sub

Data Entry With Numeric Values

To provide the value of a number-based field, simply assign it. Here is an example:

Private Sub cmdAddEmployee_Click()
On Error GoTo cmdAddEmployee_Click_Error

    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        .AddNew
        .Fields("EmployeeNumber").Value = "204-058"
        .Fields("FirstName").Value = "Richard"
        .Fields("LastName").Value = "Kelsun"
        .Fields("HourlySalary").Value = 16.85
        .Update
    End With

    Set rstEmployees = Nothing
    Set curDatabase = Nothing
    
    MsgBox "The employee has been hired.", _
           vbOKOnly Or vbInformation, "Kolo Bank"

cmdAddEmployee_Click_Exit:
    Exit Sub
    
cmdAddEmployee_Click_Error:
    MsgBox "For some strange reason, the employee was not hired.", _
           vbOKOnly Or vbInformation, "Kolo Bank"
    Resume cmdAddEmployee_Click_Exit
End Sub

Data Entry With Boolean Values

To specify the value of a dbBoolean or DB_BOOLEAN Boolean field, assign True or False to it. Here is an example:

Private Sub cmdAddEmployee_Click()
On Error GoTo cmdAddEmployee_Click_Error

    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        .AddNew
        .Fields("EmployeeNumber").Value = "942-9274"
        .Fields("FirstName").Value = "Frank"
        .Fields("LastName").Value = "Alley"
        .Fields("HourlySalary").Value = 32.85
        .Fields("CanCreateNewAccount").Value = True
       .Update
    End With

    Set rstEmployees = Nothing
    Set curDatabase = Nothing
    
    MsgBox "The employee has been hired.", _
           vbOKOnly Or vbInformation, "Kolo Bank"

cmdAddEmployee_Click_Exit:
    Exit Sub
    
cmdAddEmployee_Click_Error:
    MsgBox "For some strange reason, the employee was not hired.", _
           vbOKOnly Or vbInformation, "Kolo Bank"
    Resume cmdAddEmployee_Click_Exit
End Sub

The Visual Basic language, the compiler can consider numeric values for Boolean fields. This means that you can assign any number (positive or negative) in place of True and 0 in place of False for the Value property or a Boolean field. If you assign 0, the field would receive a value of False, equivalent to an empty check box. If you assign any other value, the field would receive a value of True, which is the same as the check box being checked.

Data Entry With Date/Time Values

To programmatically perform data entry on a column created for a date or a time type, you can provide the value as a date/time or as a string. If you are providing the value as date or time, use any of the formulas we reviewed for date/time value and include the value between # and #

You can provide the value of a date as a string. In this case, you should (must) use an appropriate formula with the year represented by 2 or 4 digits. If you want to specify the year with 2 digits, use the formula:

mm/dd/yyyy

or

mm-dd-yy

If you want to use a 4-year digit, apply the following formula:

mm/dd/yyyy

or

mm-dd-yyyy

The year with 4 digits is more precise as it properly expresses a complete year. A month from January to September can be represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the same logic. Here are examples:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim colFullName As Object
    Dim tblEmployees As Object
    Dim colStartVacationDate As Object
    Dim colEndVacationDate As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("Full Name", DB_TEXT, 80)
    tblEmployees.Fields.Append colFullName
    Set colStartVacationDate = tblEmployees.CreateField("Start Vacation Date", DB_DATE)
    tblEmployees.Fields.Append colStartVacationDate
    Set colEndVacationDate = tblEmployees.CreateField("End Vacation Date", DB_DATE)
    tblEmployees.Fields.Append colEndVacationDate
    
    curDatabase.TableDefs.Append tblEmployees
    
    MsgBox "A table named Employees has been created."
End Sub

Private Sub cmdCreateRecord_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("Full Name").Value = "Peter Mukoko"
    rstEmployees("Start Vacation Date").Value = "04/26/2009"
    rstEmployees("End Vacation Date").Value = "04-26-2009"
    rstEmployees.Update
    
    MsgBox "A record has been added to the Employees table."
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Practical Learning: Introducing Data Entry in a Record Set

1. On the Application Preparatiion form, under the Records label, right-click Maritals Status and click Build Event...
2. In the Choose Builder dialog box, click Code Builder and click OK
3. Type the follow code:

   Private Sub cmdMaritalsStatusRecords_Click()
       Dim dbDepartmentStore As Object
       Dim rstMaritalsStatus As Object
       
       Set dbDepartmentStore = CurrentDb
       Set rstMaritalsStatus = dbDepartmentStore.OpenRecordset("MaritalsStatus")
       
       rstMaritalsStatus.AddNew
       rstMaritalsStatus("MaritalStatusID").Value = 1
       rstMaritalsStatus("MaritalStatus").Value = "Single"
       rstMaritalsStatus.Update
       
       rstMaritalsStatus.AddNew
       rstMaritalsStatus("MaritalStatusID").Value = 2
       rstMaritalsStatus("MaritalStatus").Value = "Married"
       rstMaritalsStatus.Update
       
       rstMaritalsStatus.AddNew
       rstMaritalsStatus("MaritalStatusID").Value = 3
       rstMaritalsStatus("MaritalStatus").Value = "Unknown"
       rstMaritalsStatus.Update
       
       Set rstMaritalsStatus = Nothing
       Set dbDepartmentStore = Nothing
   End Sub

4. In the Object combo box, select cmdFilingsStatusRecords
5. Implement the event as follows:

   Private Sub cmdFilingsStatusRecords_Click()
       Dim rstFilingsStatus As Object
       Dim dbDepartmentStore As Object
       
       Set dbDepartmentStore = CurrentDb
       Set rstFilingsStatus = dbDepartmentStore.OpenRecordset("FilingsStatus")
       
       rstFilingsStatus.AddNew
       rstFilingsStatus("FilingStatusID").Value = 1
       rstFilingsStatus("FilingStatus").Value = "Head of Household"
       rstFilingsStatus.Update
       
       rstFilingsStatus.AddNew
       rstFilingsStatus("FilingStatusID").Value = 2
       rstFilingsStatus("FilingStatus").Value = "Married Filing Jointly"
       rstFilingsStatus.Update
       
       rstFilingsStatus.AddNew
       rstFilingsStatus("FilingStatusID").Value = 3
       rstFilingsStatus("FilingStatus").Value = "Unknown"
       rstFilingsStatus.Update
   
       Set rstFilingsStatus = Nothing
       Set dbDepartmentStore = Nothing
   End Sub

6. Return to Microsoft Access and switch the form to Form View
7. In the Records section of the form, click Maritals Status
8. In the Records section of the form, click Filings Status
9. Close the Application Preparatiion form
10. When asked whether you want to save, click Yes
11. On the Ribbon, click Create and click Form Design
12. In the Property Sheet, change the following characteristics:
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
13. Save the form as New Employee
14. In the Controls section of the Ribbon, click the Option Group control and click the Detail section of the form
15. In the first page of the wizard, click the cell below Label Names and type Single
16. Press the down arrow key and type Married
17. Press the down arrow key and type Unknown
18. Click Next
19. In the second page of the wizard, accept to set the default choice as Single and click Next
20. Click Next

    

21. Click Next
22. Click Finish
23. In the Controls section of the Ribbon, click the Option Group control and click the Detail section of the form
24. In the first page of the wizard, click the cell below Label Names and type Head of Household
25. Press the down arrow key and type Married Filing Jointly
26. Click Next
27. In the second page of the wizard, accept to have the default choice as Head of Household and click Finish
28. On the Ribbon, click Create and, in the Forms section, click Form Design
29. Right-click the form and click Form Header/Footer
30. Complete the design of the form as follows:

    

    Control		Caption	Name
    Label		Fun Department Store
    Line
    Label		New Employee
    Text Box		Employee #:	txtEmployeeNumber
    Text Box		First Name:	txtFirstName
    Text Box		Last Name:	txtLastName
    Text Box		Address:	txtAddress
    Text Box		City:	txtCity
    Text Box		County:	txtCounty
    Text Box		State:	txtState
    Text Box		ZIP Code:	txtZIPCode
    Option Group		Marital Status	fraMaritalsStatus
    Text Box		Exemptions:	txtExemptions
    Text Box		Hourly Salary:	txtHourlySalary
    Option Group		Filing Status	fraFilingsStatus
    Button		Submit	cmdSubmit
    Button		Close	cmdClose

31. Close the form
32. When asked whether you want to save, click Yes

The Characteristics of a Record Set

The Source

To work on a record set, you must communicate to the database engine what rules your set will follow. Once the table has been created, it is considered a record set. You can use it as the contents of a record set.

The Number of Records of a Record Set

After creating a record set, you may want to know the actual number of records it contains. This information is stored in a property named RecordCount. On a form, to get the number of records it contains, you can access the RecordCount property of its RecordsetClone object. This can be done as follows:

RecordsetClone.RecordCount

Record Navigation in a Record Set

Introduction

Navigating through a record set consists of visiting its records. This is similar to using the navigation buttons at the bottom of a table, a query, a form, or a report. We know how to programmatically do this using the DoCmd object. The record set also supports record navigation through various methods.

Moving to the First Record

When performing an operation on a record, you should know your position in the set. Whenever in doubt, you can reset your position by moving to the first record. To support this, the record set is equipped with a method named MoveFirst. This method takes no argument. Here is an example of calling it:

Private Sub cmdVideoAnalyze_Click()
    Dim fldEach As Object
    Dim rstTimeSheet As Object
    
    REM Blah Blah Blah
    
    rstTimeSheet.MoveFirst
    
    . . .
    
    rstTimeSheet.Close
    Set rstTimeSheet = Nothing
End Sub

Moving to the Previous Record

To move to the previous record in the set, call the MovePrevious() method of the record set.

Moving to the Next Record

To move from one record to the next, you can call the MoveNext() method of the record set.

Moving to the Last Record

Besides the first record, another extreme position you can move to is the last record. To do this, you can call the MoveLast() method of the record set.

Moving to a Specific Record

The MoveFirst() and MoveLast() methods allow you to navigate one record at a time until you get to a certain record. If you are positioned at a certain record and you want to jump a certain number of records ahead or you want to move back by a certain number of records, you can call the Move() method. Its first argument specifies the number of records by which to jump. Here is an example:

Private Sub cmdEditRecord_Click()
   Dim dbCustomers As Object
   Dim rstCustomers As Object

   Set dbCustomers = CurrentDb
   Set rstCustomers = dbCustomers.OpenRecordset("Customers")

   rstCustomers.Move 4
End Sub

When this code executes, it would jump 4 records ahead of the current record. You can also pass a negative value to move behind the current record. If the record set doesn't contain any record when you call the Move() method, you would get a 3021 error:

Don't Move Beyond the Extremes

Some, if not most, operations require that you remain within the range of values of the record set. If you move below the first record, you (actually the user) may receive an error. In the same way, if you move beyond the last record, you would receive an error. To assist you with checking whether you are in the first record, the record set uses the BOF() method. This method returns a Boolean value as follows:

• If it returns TRUE, then you are currently positioned below the first record
• If it returns FALSE, then you are at or above the first record

On the other hand, if you want to check whether you are at the highest position of the records, you can call the EOF() method of the Recordset object. It also returns a Boolean value as follows:

• If it returns TRUE, then you are currently positioned after the last record
• If it returns FALSE, then you are at or below the last record

Record Location

Editing a record consists of changing the value of one or more columns. Editing a record is done in various steps. First, you must locate the record.

If you know exactly the index of the record that contains the value you want to edit, you can call the Move() method to jump to it. Here is an example:

Private Sub cmdMovePosition_Click()
   Dim dbKoloBank As Object
   Dim rstTimeSheet As Object

   Set dbKoloBank = CurrentDb
   Set rstTimeSheet = dbKoloBank.OpenRecordset("Videos")

   rstTimeSheet.Move 6
End Sub

We also saw that you could call one of the other Move-related methods (MoveFirst(), MovePrevious(), MoveNext(), or MoveLast()). Once you get to a record, you can then perform the necessary operation(s). For example, you can retrieve the values held by that record.

Record Maintenance

Locating a Value in the Microsoft Access Libraries

Before editing a record, you must locate it to indicate to the record set what record is involved. Here is an example of locating a record:

Private Sub cmdUpdate_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be edited has been located

                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
End Sub

Editing a Value in a Recordset

To support the ability to update a record, the record set uses a method named Edit. After calling this method, access the field or each field that needs to be edited and assign the desired value to it (or them). After calling the Edit() method and assigning the value(s) to the appropriate field(s), you must call the Update method of the record set. This can be done as follows:

Private Sub cmdUpdate_Click()
    Dim curDatabase As Object
    Dim fldEmployee As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be edited has been located
                        .Edit
                        .Fields("DateHired").Value = txtDateHired
                        .Fields("FirstName").Value = txtFirstName
                        .Fields("LastName").Value = txtLastName
                        .Fields("HourlySalary").Value = txtHourlySalary
                        .Update
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
End Sub

Deleting a Record in the Microsoft Access Libraries

To give you the ability to remove a record, the record set uses a method named Delete. Of course, before performing this operation, first locate the record you want to delete. Once you have found the record, call the Delete() method. Here is an example:

Private Sub cmdDeleteRecord_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    Dim fldEmployee As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be deleted has been found
                        .Delete
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
End Sub

Practical Learning: Ending the Lesson

• Close Microsoft Access