Lesson 23: Using and Maintaining a Record Set

	Using and Maintaining a Record Set

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. You may already know how to create a table and its columns, and how to populate a table with data. Once the table has been created, it is considered a record set. You can use it as the contents of a Recordset object.

Practical Learning: Introducing the Characteristics of a Record Set

Start Microsoft Access
Open the KoloBank1 database from the previous lesson

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 could be done as follows:

RecordsetClone.RecordCount

This would produce the total number of records that a form holds.

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 Recordset class also supports record navigation through various methods.

Moving to the First Record

Whenever 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 Recordset object is equipped with a method named MoveFirst. This method takes no argument. Here is an example of calling it:

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

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 Recordset object.

Moving to the Next Record

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

Moving to the Previous Record

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

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 syntax is:

recordset.Move NumRecords, Start

The first argument is required. Its specifies the number of records to jump to. If you pass a positive value, the position would be moved ahead by that number. 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 of a table named Customers. You can also pass a negative value. In this case the position would be moved behind the current record by the value passed. 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 Recordset object provides 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

Practical Learning: Locating a Record

The KoloBank1 database should still be opened.
In the Navigation Pane, right-click NewWithdrawal and click Design View
In the Form Footer section, right-click the Submit button and click Build Event
In the Choose Builder dialog box, double-click Code Builder

Implement the event as follows:

Private Sub cmdSubmit_Click()
    Dim fldCustomer As Field
    Dim curDatabase As Database
    Dim rstCustomers As Recordset
    Dim strAccountStatus As String
    Dim AccountSuspended As Boolean
    
    ' When the customer is about to withdraw money,
    ' let's assume that the account is active
    AccountSuspended = False
    strAccountStatus = "Active"
    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get the records from the Customers table
    Set rstCustomers = curDatabase.OpenRecordset("Customers")
    
    ' Check all Customers records, ...
    With rstCustomers
        ' ... from beginning to end.
        Do Until .EOF
            ' When you get to a record, ...
            For Each fldCustomer In .Fields
                ' ..., start from the AccountNumber column.
                If fldCustomer.Name = "AccountNumber" Then
                    ' Check if the current account number is the one on the form.
                    If fldCustomer.Value = txtAccountNumber Then
                        ' If it is, get the value of the
                        ' corresponding AccountStatus column.
                        strAccountStatus = CStr(rstCustomers("AccountStatus").Value)
                        ' Since you found the account number, stop looking for it.
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
    
    ' Using the reserved value of the status, check its value.
    ' If the account is currently suspended, ...
    If strAccountStatus = "Suspended" Then
        ' make a note.
        AccountSuspended = True
        ' Since it looks like the account is suspended, let the customer know ....
        MsgBox "Money withdrawal is not allowed because the account is currently suspended." & vbCrLf & _
               "Either deposit the correct amount to bring the account to at least a 0 " & _
               "balance or contact the Customer Service at 1-800-IMDOOMD", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        ' ... and stop the whole thing
        Exit Sub
    ElseIf strAccountStatus = "Closed" Then
        ' If the account is closed, ...
        AccountSuspended = True
        ' let the customer know ...
        MsgBox "Money withdrawal is not allowed because the acount has already been closed.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        ' ... and stop the transaction
        Exit Sub
    Else
        ' If the account is not suspended, make a note
        AccountSuspended = False
    End If
    
    ' At this time, based on the account status,
    ' we know if the withdrawal can continue or not
    If AccountSuspended = False Then
        MsgBox "Withdrawal possible"
    End If
    
    Set rstCustomers = Nothing
    Set curDatabase = Nothing
    
    cmdReset_Click
End Sub

In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Return to Microsoft Access
Close the form
When asked whether you want to saVe, click Yes

Editing a Value in a Recordset

To support the ability to update a record, the Recordset class is equipped with a method named Edit. Once you have located the record, call the Edit method. After calling the 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 Recordset class. This would be done as follows:

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
                        .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

Practical Learning: Editing a Value

In the Navigation Pane, right-click NewDeposit and click Design View
On the form, right-click the Submit button and click Build Event...
In the Choose Builder dialog box, double-click Code Builder

Implement the event as follows:

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_ClickError

    Dim fldCustomer As Field
    Dim fldTransaction As Field
    Dim curDatabase As Database
    Dim rstCustomers As Recordset
    Dim strAccountStatus As String
    Dim rstTransactions As Recordset
    Dim BalanceAfterDeposit As Double
    Dim BalanceBeforeDeposit As Double
    Dim rstAccountsHistories As Recordset

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    
    ' We will need to find out whether the account is suspended and update it.
    strAccountStatus = ""
    ' First, get the records of customers
    Set rstCustomers = curDatabase.OpenRecordset("Customers")
    
    If IsNull(txtEmployeeName) Then
        MsgBox "Please enter a valid employee number to identity " & _
               "the employee who is performing the transaction.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
              
    If IsNull(txtDepositDate) Then
        MsgBox "Please specify the date the transaction is occurring.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
       
    If IsNull(txtLocationCode) Then
        MsgBox "Enter the location code where the transaction is taking place.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    
    If IsNull(txtCustomerName) Then
        MsgBox "Enter the valid account number of the bank account where money is deposited.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    
    If IsNull(cbxCurrencyTypes) Then
        MsgBox "Select the type of currency (cash, check, etc).", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    
    If IsNull(txtAmount) Then
        MsgBox "Type the amount of money that is deposited.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    
    If IsNull(txtBalance) Then
        MsgBox "You must indicate the new balance of the bank account.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If

    ' Before doing anything on the Customers table, get to the first record
    rstCustomers.MoveFirst
    
    ' Check all Customers records, ...
    With rstCustomers
        ' ... from beginning to end
        Do While Not .EOF
            ' When you get to a record, ...
            For Each fldCustomer In .Fields
                ' ... start with the customer's bank account number
                ' If the account number is the same on the form, ...
                If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                    ' ... get the status of that account and reserve it
                    strAccountStatus = .Fields("AccountStatus").Value
                    ' Since you have found the account number, stop looking for it
                    Exit For
                End If
            ' If you have not yet found the account number, keep looking
            Next
            .MoveNext
        Loop
    End With
    
    ' First get a reference to the Transactions table
    Set rstTransactions = curDatabase.OpenRecordset("Transactions")
    
    ' If the deposit is ready, create its record in the Transactions table
    rstTransactions.AddNew
    rstTransactions("EmployeeNumber").Value = txtEmployeeNumber
    rstTransactions("LocationCode").Value = txtLocationCode
    rstTransactions("TransactionDate").Value = txtDepositDate
    rstTransactions("TransactionTime").Value = txtDepositTime
    rstTransactions("AccountNumber").Value = txtAccountNumber
    rstTransactions("TransactionType").Value = "Deposit"
    rstTransactions("CurrencyType").Value = cbxCurrencyTypes
    rstTransactions("DepositAmount").Value = CDbl(txtAmount)
    rstTransactions("Balance").Value = txtBalance
    rstTransactions("Notes").Value = txtNotes
    rstTransactions.Update
    
    ' Let the customer know that the deposit was done.
    MsgBox "The deposit has been made.", _
           vbOKOnly Or vbInformation, "Kolo Bank"
    
    ' Before scanning the list of transactions, get back to the first record
    rstTransactions.MoveFirst
    
    ' Scanning the table of transactions to look for the current account number
    With rstTransactions
        Do While Not .EOF
            For Each fldTransaction In .Fields
                ' If you find the account number of the form, ...
                If (fldTransaction.Name = "AccountNumber") And (fldTransaction.Value = txtAccountNumber) Then
                    ' ... get the account's current balance
                    BalanceAfterDeposit = CDbl(.Fields("Balance").Value)
                    
                    ' Since you found the account number, stop looking for it.
                    Exit For
                End If
            Next
            .MoveNext
        Loop
    End With
        
    ' We need to find out if the account is currently suspended.
    ' Before going through the customers' records, get on the first record
    rstCustomers.MoveFirst
    
    ' If the current bank account suspended?
    ' If it is, did the customer bring the balance to at least 0?
    If (strAccountStatus = "Suspended") And (BalanceAfterDeposit >= 0#) Then
        ' If so, sacn the Customers table to look for our account number
        With rstCustomers
            Do While Not .EOF
                For Each fldCustomer In .Fields
                    ' If you find the account number of the form, ...
                    If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                        ' ... change its status to Active
                        .Edit
                        .Fields("AccountStatus").Value = "Active"
                        .Update
                        
                        ' Announce the good news to the customer
                        MsgBox "The account has been re-activated.", _
                                vbOKOnly Or vbInformation, "Kolo Bank"
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        ' Since the account's status has changed, create a new record in the history list
        Set rstAccountsHistories = curDatabase.OpenRecordset("AccountsHistories")
    
        rstAccountsHistories.AddNew
        rstAccountsHistories("AccountNumber").Value = txtAccountNumber
        rstAccountsHistories("AccountStatus").Value = "Active"
        rstAccountsHistories("DateChanged").Value = CDate(txtDepositDate)
        rstAccountsHistories("ShortNote").Value = "The account has been re-actived."
        rstAccountsHistories.Update
    End If
    
    Set rstAccountsHistories = Nothing
    Set rstTransactions = Nothing
    Set rstCustomers = Nothing
    Set curDatabase = Nothing
    
    ' Reset the form in case there is another deposit to make
    cmdReset_Click
    
    Exit Sub
    
cmdSubmit_ClickError:
    MsgBox "The withdrawal was not processed because of an error." & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, _
           vbOKOnly Or vbInformation, "Kolo Bank"
    Resume Next
End Sub

In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Return to Microsoft Access
Close the form
When asked whether you want to save it, click Yes
In the Navigation Pane, right-click NewWithdrawal and click Design View
On the form, right-click Submit and click Build Event...
Double-click Code Builder

Implement the event as follows:

Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_ClickError

    Dim curDatabase As Database
    Dim fldCustomer As Field
    Dim fldTransaction As Field
    Dim rstCustomers As Recordset
    Dim AmountWithdrawn As Double
    Dim strAccountStatus As String
    Dim bAccountSuspended As Boolean
    Dim rstTransactions As Recordset
    Dim BalanceAfterWithdrawal As Double
    Dim BalanceBeforeWithdrawal As Double
    Dim rstAccountsHistories As Recordset

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    
    If IsNull(txtEmployeeName) Then
        MsgBox "You must specify the name of the employee who is performing the transaction.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
              
    If IsNull(txtWithdrawalDate) Then
        MsgBox "You must indicate the date the transaction is occurring.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
       
    If IsNull(txtLocationCode) Then
        MsgBox "You must specify where the transaction is taking place.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    If IsNull(txtCustomerName) Then
        MsgBox "You must provide a valid account number.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    If IsNull(cbxCurrencyTypes) Then
        MsgBox "You must indicate what category of money is withdrawn (cash, check, etc).", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    If IsNull(txtAmount) Then
        MsgBox "You must specify the amount of money to withdraw.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    Else
        AmountWithdrawn = CDbl(Nz(txtAmount))
    End If
    
    If IsNull(txtBalance) Then
        MsgBox "You must indicate the new balance of the bank account.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If

    Set rstTransactions = curDatabase.OpenRecordset("Transactions")
    
    With rstTransactions
        Do Until .EOF
            For Each fldTransaction In .Fields
                ' Check the AccountNumber column.
                ' If the current account number is the same as the one on the form,
                If rstTransactions("AccountNumber").Value = txtAccountNumber Then
                    ' Get the account's (latest) balance
                    BalanceBeforeWithdrawal = CStr(rstTransactions("Balance").Value)
                    
                    ' Stop looking for the name
                    Exit For
                End If
            Next
            .MoveNext
        Loop
    End With
    
    BalanceAfterWithdrawal = BalanceBeforeWithdrawal - AmountWithdrawn
    
    ' We will not allow a type of withdrawal that would bring the account balance to less than $-20
    If BalanceAfterWithdrawal < -10 Then
        MsgBox "Money withdrawal is not allowed because of non-sufficient fund (NSF).", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    Else
        ' Get a reference to the Transactions table
        Set rstTransactions = curDatabase.OpenRecordset("Transactions")
    
        ' We will allow a customer to withdraw money as long as
        ' the new balance does not leave less than $-10 in the account
        ' If CDbl(txtAmount) > (CurrentBalance - 10) Then
        ' Add a new entry in the Transactions table to show the new balance
        rstTransactions.AddNew
        rstTransactions("EmployeeNumber").Value = txtEmployeeNumber
        rstTransactions("LocationCode").Value = txtLocationCode
        rstTransactions("TransactionDate").Value = txtWithdrawalDate
        rstTransactions("TransactionTime").Value = txtWithdrawalTime
        rstTransactions("AccountNumber").Value = txtAccountNumber
        rstTransactions("TransactionType").Value = "Withdrawal"
        rstTransactions("CurrencyType").Value = cbxCurrencyTypes
        rstTransactions("WithdrawalAmount").Value = -CDbl(txtAmount)
        rstTransactions("Balance").Value = txtBalance
        rstTransactions("Notes").Value = txtNotes
        rstTransactions.Update
    End If
    
    Set rstTransactions = curDatabase.OpenRecordset("Transactions")
    
    With rstTransactions
        Do Until .EOF
            For Each fldTransaction In .Fields
                ' Check the AccountNumber column.
                ' If the current account number is the same as the one on the form,
                If rstTransactions("AccountNumber").Value = txtAccountNumber Then
                    ' Get the account's (latest) balance
                    BalanceAfterWithdrawal = CStr(rstTransactions("Balance").Value)
                    
                    ' Stop looking for the name
                    Exit For
                End If
            Next
            .MoveNext
        Loop
    End With
    
    ' Get the records from the Customers table
    Set rstCustomers = curDatabase.OpenRecordset("Customers")
    
    If BalanceAfterWithdrawal < 0 Then
        ' Suspend account.
        ' First check each bank account to find the one on the form
        With rstCustomers
            Do While Not .EOF
                For Each fldCustomer In .Fields
                    If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                        ' Once you have found the record, start editing it
                        .Edit
                        ' Change the status to Suspended
                        .Fields("AccountStatus").Value = "Suspended"
                        .Update
                        
                        MsgBox "The account has been suspended because its balance became negative.", _
                               vbOKOnly Or vbInformation, "Kolo Bank"
                        ' Since the account has been found and updated, stop looking for it
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        ' Since the status of the account has changed,
        ' add a new entry in the AccountsHistories table
        Set rstAccountsHistories = curDatabase.OpenRecordset("AccountsHistories")
    
        rstAccountsHistories.AddNew
        rstAccountsHistories("AccountNumber").Value = txtAccountNumber
        rstAccountsHistories("AccountStatus").Value = "Suspended"
        rstAccountsHistories("DateChanged").Value = CDate(txtWithdrawalDate)
        rstAccountsHistories("ShortNote").Value = "The account was suspended because it became negative."
        rstAccountsHistories.Update
    End If
    
    MsgBox "=-= Money Withdrawal Completed =-=" & vbCrLf & _
           "Balance before withdrawal: " & CStr(BalanceBeforeWithdrawal) & vbCrLf & _
           "Amount withdrawn: " & CStr(AmountWithdrawn) & vbCrLf & _
           "Balance after withdrawal = " & CStr(BalanceAfterWithdrawal)
    
    Set rstCustomers = Nothing
    Set curDatabase = Nothing
    
    cmdReset_Click
    
    Exit Sub
    
cmdSubmit_ClickError:
    MsgBox "The withdrawal was not processed because of an error." & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, _
           vbOKOnly Or vbInformation, "Kolo Bank"
    Resume Next
End Sub

Return to Microsoft Access
Close the form
When asked whether you want to save, click Yes

Deleting a Record in the Microsoft Access Libraries

To give you the ability to remove a record, the Recordset class is equipped with 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: Deleting a Record

In the Navigation Pane, right-click the Customers form and click Design View
On the form, right-click the Transfer Account to Previous Customers and click Design View

Change the event as follows:

Private Sub cmdMoveRecord_Click()
On Error GoTo cmdMoveRecord_ClickError

    Dim fldCustomer As Field
    Dim dbKoloBank As Database
    Dim rstCustomers As Recordset
    Dim rstPreviousCustomers As Recordset
    
    Set dbKoloBank = CurrentDb
    Set rstCustomers = dbKoloBank.OpenRecordset("Customers")
    Set rstPreviousCustomers = dbKoloBank.OpenRecordset("PreviousCustomers")
    
    If MsgBox("It appears that this account has been closed." & vbCrLf & _
              "Do you want to move it to the table of previous customers?", _
              vbInformation Or vbYesNo, _
              "Kolo Bank") = vbYes Then

        ' The customer account is supposed to be deleted.
        ' Instead of completely deleting it, for archive reasons, we will
        ' first copy and add it to the table of previous customers.
        With rstPreviousCustomers
            .AddNew
            .Fields("AccountNumber").Value = AccountNumber
            .Fields("EmployeeNumber").Value = EmployeeNumber
            .Fields("DateCreated").Value = CDate(DateCreated)
            .Fields("AccountType").Value = AccountType
            .Fields("CustomerName").Value = CustomerName
            .Fields("Address").Value = Address
            .Fields("City").Value = City
            .Fields("State").Value = State
            .Fields("ZIPCode").Value = ZIPCode
            .Fields("Country").Value = Country
            .Fields("HomePhone").Value = HomePhone
            .Fields("WorkPhone").Value = WorkPhone
            .Fields("EmailAddress").Value = EmailAddress
            .Fields("Username").Value = Username
            .Fields("Password").Value = Password
            .Fields("AccountStatus").Value = AccountStatus
            .Fields("Notes").Value = Notes
           .Update
        End With
        
        ' After adding the account to the table of previous
        ' customers, we can now delete it (from the Customers table).
        rstCustomers.MoveFirst
        
        With rstCustomers
            Do Until .EOF
                For Each fldCustomer In .Fields
                    If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = AccountNumber) Then
                        .Delete
                        Exit For
                    End If
                Next
                .MoveNext
            Loop
        End With
        
        MsgBox "The account has been moved to the table of previous customers.", _
               vbInformation Or vbYesNo, "Kolo Bank"
    End If
    
    Set rstPreviousCustomers = Nothing
    Set rstCustomers = Nothing
    Set dbKoloBank = Nothing
    
    Exit Sub
    
cmdMoveRecord_ClickError:
    ' Error #3167 causes the controls to display #Deleted.
    ' If (when) this happen, one remedy is to refresh the form
    If Err.Number = 3167 Then
        Me.Requery
    Else
        MsgBox "Invalid Operation: The customer account could not be " & _
               "transferred and the operation was cancelled." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Kolo Bank"
    End If
    
    Resume Next
End Sub

In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Return to Microsoft Access and switch the form to Form View
Enter a rental order in the top text box and click the Open Rental Order button
Click the Delete button
Click No
Close the Existing Rental Order form
When asked whether you want to save the form, click Yes

Field Selection Into a Record Set

Introduction

So far, we were passing the name of a table or query to a Recordset object. Here is an example:

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

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

Instead of passing a table or a query by name to the OpenRecordset() method, you can pass a SELECT statement. Here is an example:

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

   Set dbKoloBank = CurrentDb
   Set rstEmployees = dbKoloBank.OpenRecordset("SELECT * FROM Employees;")
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Of course, you can list only the fields you want in the record set. Here is an example:

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

   Set dbKoloBank = CurrentDb
   Set rstEmployees = _
   	       dbKoloBank.OpenRecordset("SELECT FirstName, LastName FROM Employees;")

   Set rstEmployees = Nothing
   Set curDatabase = Nothing
End Sub

With this technique, only the indicated columns would be considered in the Recordset object. The value of each of the columns produced can then be assigned to a control on a form or report. Here is an example:

Private Sub cmdGetEmployee_Click()
On Error GoTo cmdFindEmployee_Error

    Dim dbExercise As Database
    Dim rsEmployees As Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = _
        dbExercise.OpenRecordset("SELECT EmployeeNumber, " & _
                                 "       DateHired, " & _
                                 "       FirstName, " & _
                                 "       LastName, " & _
                                 "       HourlySalary, " & _
                                 "       DepartmentID, " & _
                                 "       EmploymentStatus " & _
                                 "FROM Employees")
    
    txtEmployeeNumber = rsEmployees("EmployeeNumber").Value
    txtDateHired = rsEmployees("DateHired").Value
    txtFirstName = rsEmployees("FirstName").Value
    txtLastName = rsEmployees("LastName").Value
    txtHourlySalary = rsEmployees("HourlySalary").Value
    cbxDepartments = rsEmployees("DepartmentID").Value
    cbxEmploymentsStatus = rsEmployees("EmploymentStatus").Value
    
    Exit Sub
    
cmdFindEmployee_Error:
    MsgBox "There was a problem when processing the operation. Please report the error as" & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
End Sub

Creating a Query From Record Set

We know different ways of creating and using simple SELECT statements. Using fields from a table, we can create a SELECT statement and use it to generate a query. In Microsoft Access Object Library, a query is based on a class named QueryDef. The group of queries of a database is stored in a collection named QueryDefs. If/When you create a new query, it is added as the new item in the collection. This means that the queries are cumulative and each can be located either by its name or its index in the collection.

To let you create a new query in the Microsoft Access Object Library, the Database class provides a method named CreateQueryDef. Its syntax is:

Function expression.CreateQueryDef(Name, SQLText) As QueryDef

expression can be a variable of type Database, previously declared, and that references either the current or an existing database. Both arguments are semi-optional. This means that you can call the method without these arguments and you would not receive an error, but it is not a good idea. After the CreateQueryDef() method has performed its action, it returns an object of type QueryDef that you can use as you see fit. For example, you can analyze of you can use it as the Record Source of a form or report.

The first argument specifies the name of the query that will be created. If you call the method without this argument, the database engine would create a query in memory with an insignificant name (the name is made of random characters) and it would not show in the Navigation Pane. This means that it's a good idea to pass this argument. The name follows all the rules you know about naming objects in Microsoft Access. If a query exists already and you want to get its name, you can access the it using a read-only property named Name that belongs to the QueryDef class. Here is an example:

Private Sub cmdGetQueryName_Click()
    Dim dbExercise As Database
    Dim qryEmployees As QueryDef

    ' Get a reference to the current database
    Set dbExercise = CurrentDb

    ' Get a reference to the third query in the queries collection
    Set qryEmployees = dbExercise.QueryDefs(2)

    MsgBox "Name of 3rd query: " & qryEmployees.Name
End Sub

The second argument also is optional. It represents the SQL statement that is the subject of the query. Here is an example:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As Object
    Dim qryEmployees As Object
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SELECT FirstName, LastName FROM Employees;"
    ' Create a new query named EmployeesInfo
    Set qryEmployees = curDatabase.CreateQueryDef("EmployeesInfo", strStatement)
End Sub

If you don't pass the second argument, to let you formula the SQL statement, the QueryDef class is equipped with a string-based property named SQL. You can simply assign the desired statement to this property. Here is an example:

Private Sub cmdCreateQuery_Click()
    Dim dbExercise As Database
    Dim qryEmployees As QueryDef

    ' Get a reference to the current database
    Set dbExercise = CurrentDb

    ' Create a new query named EmployeesInfo
    Set qryEmployees = dbExercise.CreateQueryDef("EmployeesInfo")
    
    qryEmployees.SQL = "SELECT FirstName, LastName FROM Employees;"

End Sub

Data Analysis With a Record Set

Introduction

Data analysis is available in a record set. In fact, as opposed to the DoCmd.RunSQL() method, the record set presents some advantages. For example, if an error occurs when you execute a SQL statement in DoCmd.RunSQL, you may have difficulties finding out what type or error and why. On the other hand, the record set can give you some information about what is going on.

As seen already, you can pass a SQL statement as argument to a record set. After opening the record set, it returns a list of records. This time, instead of visiting each field of the record set to check its name, you would know with certainty what fields are in the set.

As you should know already, both the DoCmd.RunSQL() and the OpenRecordset() methods produce a list of records. One of the advantages that a record set presents over DoCmd.RunSQL() is that you can find out if the OpenRecordset() did not produce any record at all, which is difficult information to get with the former. When the OpenRecordset() method is called, if it produces an empty list, the database interpreter produces (throws) a 3021 error. This allows you to take appropriate actions.

Using a Filter in a Record Set

As you may suspect, the record set relies on a SQL statement to get a list of records. In fact, we saw already that you could pass the name of a table or query to OpenRecordset(). In reality, this is equivalent to a SELECT * FROM ... statement. We also saw that you could provide a specific list of fields in the statement. When creating the SQL statement, you can provide a condition by which the necessary records would be isolated. Here is an example:

Private Sub cmdGetEmployee_Click()
    Dim dbExercise As Database
    Dim rsEmployees As Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = _
        dbExercise.OpenRecordset("SELECT EmployeeNumber, " & _
                                 "       DateHired, " & _
                                 "       FirstName, " & _
                                 "       LastName, " & _
                                 "       HourlySalary, " & _
                                 "       DepartmentID, " & _
                                 "       EmploymentStatus " & _
                                 "FROM Employees " & _
                                 "WHERE EmployeeNumber = '" & txtEmployeeNumber & "'")
    
    With rsEmployees
        txtDateHired = .Fields("DateHired").Value
        txtFirstName = .Fields("FirstName").Value
        txtLastName = .Fields("LastName").Value
        txtHourlySalary = .Fields("HourlySalary").Value
        cbxDepartments = .Fields("DepartmentID").Value
        cbxEmploymentsStatus = .Fields("EmploymentStatus").Value
    End With
End Sub

When creating the SQL statement, you can use everything you know about SQL to write a statement as effective as possible. The condition in the statment can use any of the Boolean or SQL operators you know already.

Practical Learning: Using a Filter in a Record Set

On the form, click the Account # text box
In the Properties window, click Event and double-click On Lost Focus

Click its ellipsis button and implement the event as follows:

Private Sub txtAccountNumber_LostFocus()
On Error GoTo txtAccountNumber_LostFocusError

    Dim dbKoloBank As Database
    Dim rstCustomers As Recordset
    Dim strCustomerName As String
    
    ' Get a reference to the current database
    Set dbKoloBank = CurrentDb
    ' Get the records from the Customers table
    Set rstCustomers = dbKoloBank.OpenRecordset("SELECT FirstName, MiddleName, LastName, AccountType, DateCreated " & _
                                                "FROM Customers " & _
                                                "WHERE AccountNumber = '" & txtAccountNumber & "'")
    
    If IsNull(txtAccountNumber) Then
        Exit Sub
    End If
        
    If IsNull(rstCustomers("MiddleName").Value) Then
        strCustomerName = CStr(rstCustomers("LastName").Value) & ", " & CStr(rstCustomers("FirstName").Value)
    Else
        strCustomerName = CStr(rstCustomers("LastName").Value) & ", " & CStr(rstCustomers("FirstName").Value) & " " & CStr(rstCustomers("MiddleName").Value)
    End If
    
    txtCustomerName = strCustomerName
    txtDateCreated = rstCustomers("DateCreated").Value
    txtAccountType = rstCustomers("AccountType").Value
    
    Exit Sub
    
txtAccountNumber_LostFocusError:
    If Err.Number = 3021 Then
        MsgBox "Invalid Account Number: The account number you entered was not found in the database.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
           Exit Sub
    Else
        MsgBox "A problem occurred when trying to retrieve account information." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Kolo Bank"
    End If
    
    Resume Next
End Sub

Controlling a Sub-Form Using a Record Set

You can create a record set from a form that acts as the parent of a sub-form, populate that record set with records, and bind the record set to the sub-form. To make this possible, remember that every form has a property named Recordset and this property is of type Recordset. Instead of using the name of a table or query, you can create a SQL statement as complex as you want, pass it to the OpenRecordset() method of a Recordset object, then assign that record set to the subform.

Practical Learning: Controlling a Sub-Form Using a Record Set

In the Object combo box, select cmdShowTransactions

Implement the event as follows:

Private Sub cmdShowTransactions_Click()
    Dim dbKoloBank As Database
    Dim rsTransactions As Recordset
    
    Set dbKoloBank = CurrentDb
    Set rsTransactions = _
        dbKoloBank.OpenRecordset("SELECT ALL * " & _
                                 "FROM Transactions " & _
                                 "WHERE (AccountNumber = '" & txtAccountNumber & "')" & _
                                 "  AND  TransactionDate BETWEEN (#" & CDate(txtStartDate) & "#) AND (#" & CDate(txtEndDate) & "#);")
    
    Set Me.sfTransactionsReview.Form.Recordset = rsTransactions
    
    Set rsTransactions = Nothing
    Set dbKoloBank = Nothing
End Sub

In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Close Microsoft Visual Basic and return to Microsoft Access
Save and close the form
In the Navigation Pane, double-click NewDeposit

Create the following records so that, at the end of each record, click Submit:

Employee #	Deposit Date	Time	Location	Account #	Transaction Type	Currency Type	Amount	Notes
294-075	1/4/2012	10:22 AM	ALXJPZ	68-304605-84	Deposit	Check	1128.64	The customer opened a new bank account and money was deposited.
952-846	1/4/2012	10:26 AM	SLVSSL	20-304042-49	Deposit	Cash	325.00	A new bank account was created for the customer and the first deposit was made.
507-728	1/5/2012	2:02 PM	GTWMST	94-477085-03	Deposit	Check	220.00

Close the NewDeposit form
In the Navigation Pane, double-click NewWithdrawal

Create the following record:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Withdrawal
000-100	1/10/2012	9:14 AM	0SSCTPL	20-304042-49	Amount	Cash	100.00

Click Submit
Close the NewWithdrawal form
In the Navigation Pane, double-click AccountsTransactions to see the record of the 20-304042-49 account
Close the AccountsTransactions form
In the Navigation Pane, double-click NewDeposit

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Deposit	Notes
482-799	1/12/2012	3:05 PM	GTWMST	68-304605-84	Deposit	Check	885.27
484-050	1/12/2012	9:12 AM	WHTFLT	27-314257-84	Deposit	Check	500.00	This is deposit for a new bank account.
533-825	1/12/2012	9:14 AM	WHTFLT	28-370082-80	Deposit	Check	2500.00	Deposit for new account
507-728	1/12/2012	10:02 AM	GTWMST	38-402217-59	Deposit	Check	975.35	This was a deposit for a newly created account.
284-005	1/16/2012	1:52 PM	SLVSSL	27-314257-84	Deposit	Check	338.92
492-081	1/16/2012	1:24 PM	GTWMST	68-640304-15	Deposit	Check	842.46	This is the first deposit for the new bank account.

Close the NewDeposit form
In the Navigation Pane, double-click NewWithdrawal

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Withdrawal
000-100	1/16/2012	7:16 AM	0GRNML	27-314257-84	Withdrawal	Cash	300.00
000-100	1/18/2012	7:16 AM	CPKUMD	27-314257-84	Withdrawal	Cash	300.00
736-626	1/18/2012	9:22 AM	DCK10S	20-304042-49	Withdrawal	Check	148.84

Close the NewWithdrawal form
In the Navigation Pane, double-click AccountsTransactions
Navigate to the record of the 27-314257-84 account
Close the AccountsTransactions form
In the Navigation Pane, double-click NewWithdrawal

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Withdrawal
248-552	1/20/2012	9:14 AM	CPKUMD	27-314257-84	Withdrawal	Cash	40.00
482-799	1/20/2012	5:51 PM	GTWMST	38-402217-59	Withdrawal	Cash	200.00
000-100	1/20/2012	6:12 AM	0LNFPL	27-314257-84	Withdrawal	Cash	200.00

Close the NewWithdrawal form
In the Navigation Pane, double-click AccountsTransactions
Navigate to the record of the 27-314257-84 account. Notice that the account has been suspended
Close the AccountsTransactions form
In the Employee # text box, type 000-100 and press Tab
In the Withdrawal Date text box, type 01/22/2012 and press Tab twice
In the Location Code text box, type 0MNGML and press Tab
In the Account # text box, type 27-314257-84 and press Tab. Notice that you receive a message that money withdrawal is not allowed
Click OK on the message box
Close the NewWithdrawal form
In the Navigation Pane, double-click NewDeposit

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Deposit	Notes
829-313	1/24/2012	10:05 AM	WHTFLT	38-402217-59	Deposit	Check	186.24
712-083	1/24/2012	3:14 PM	WHTFLT	27-314257-84	Deposit	Cash	100.00
461-842	1/25/2012	9:14 AM	CPKUMD	29-425806-46	Deposit	Cash	40.00	The deposit wa made for the new bank account.
484-050	1/25/2012	11:28 AM	WHTFLT	40-460582-63	Deposit	Cash	2000.00	The customer made a deposit for the new account and was happy.
284-005	1/25/2012	11:59 AM	SLVSSL	68-640304-15	Deposit	Cash	400.00
000-100	1/25/2012	6:22 AM	0SSCTPL	38-402217-59	Deposit	Check	448.62

Close the NewDeposit form
In the Navigation Pane, double-click NewWithdrawal

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Withdrawal
000-100	1/25/2012	5:12 AM	0GRNML	20-304042-49	Withdrawal	Cash	80.00
736-626	1/26/2012	2:41 PM	SLVSSL	30-514090-26	Withdrawal	Cash	200.00
111-111	1/26/2012	10:06 PM	1ONLIN	27-314257-84	Withdrawal	Check	105.86

Close the NewWithdrawal form
In the Navigation Pane, double-click AccountsTransactions
Navigate to the record of the 27-314257-84 account. Check the account history section
Close the AccountsTransactions form
In the Navigation Pane, double-click Deposit

Create the following record:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Deposit	Notes
294-075	1/26/2012	10:26 AM	ALXJPZ	82-370863-62	Deposit	Check	765.00	The customer opened a new bank account with us. This was the first deposit.

Click Submit
Close the NewDeposit form
In the Navigation Pane, double-click NewCharge

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Charge	Charge Reason
000-200	1/30/2012	00:00	SLVSSL	20-304042-49	Service Charge	Cash	35.00	Overdraft
000-200	1/30/2012	00:00	SLVSSL	20-304042-49	Service Charge	Cash	6.00	Monthly Charge

Close the NewCharge form
In the Navigation Pane, double-click AccountsTransactions to see the balance of the 20-304042-49 account
Close the AccountsTransactions form
In the Navigation Pane, double-click TransactionsReview
In the Account # text box, type 27-314257-84
In the Transactions From box, type 01/15/2012
In the To text box, type 01/25/2012
Click the Show Transactions button
Close the AccountsTransactions form
In the Navigation Pane, double-click NewWithdrawal

Create the following record:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Withdrawal
533-825	1/30/2012	9:39 AM	GTWMST	68-304605-84	Withdrawal	300.00

Close the NewWithdrawal form
In the Navigation Pane, double-click NewCharge

Create the following records so that, at the end of each record, click Submit:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Charge	Charge Reason
000-200	1/30/2012	00:00	SLVSSL	30-514090-26	Service Charge	Cash	6.00	Monthly Charge
000-200	1/30/2012	00:00	SLVSSL	29-425806-46	Service Charge	Cash	6.00	Monthly Charge

Close the NewCharge form
In the Navigation Pane, double-click NewDeposit

Create the following record:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Deposit	Notes
503-938	1/30/2012	3:17 PM	SLVSSL	84-697064-28	Deposit	Cash	2000.00	New CD account

Close the NewCharge form
In the Navigation Pane, double-click NewDeposit

Create the following record:

Employee #	Trans Date	Time	Location	Account #	Transaction Type	Currency Type	Deposit	Notes
503-938	1/30/2012	3:17 PM	SLVSSL	84-697064-28	Deposit	Cash	2000.00	New CD account

Close the NewCharge form
Close Microsoft Access