Microsoft Access Database Development With VBA

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 LearningPractical Learning: Introducing the Characteristics of a Record Set

  1. Start Microsoft Access
  2. 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 LearningPractical Learning: Locating a Record

  1. The KoloBank1 database should still be opened.
    In the Navigation Pane, right-click NewWithdrawal and click Design View
  2. In the Form Footer section, right-click the Submit button and click Build Event
  3. In the Choose Builder dialog box, double-click Code Builder
  4. 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
  5. In the Object combo box, select cmdClose
  6. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  7. Return to Microsoft Access
  8. Close the form
  9. 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 LearningPractical Learning: Editing a Value

  1. In the Navigation Pane, right-click NewDeposit and click Design View
  2. On the form, right-click the Submit button and click Build Event...
  3. In the Choose Builder dialog box, double-click Code Builder
  4. 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
  5. In the Object combo box, select cmdClose
  6. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  7. Return to Microsoft Access
  8. Close the form
  9. When asked whether you want to save it, click Yes
  10. In the Navigation Pane, right-click NewWithdrawal and click Design View
  11. On the form, right-click Submit and click Build Event...
  12. Double-click Code Builder
  13. 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
  14. Return to Microsoft Access
  15. Close the form
  16. 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 LearningPractical Learning: Deleting a Record

  1. In the Navigation Pane, right-click the Customers form and click Design View
  2. On the form, right-click the Transfer Account to Previous Customers and click Design View
  3. 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
  4. In the Object combo box, select cmdClose
  5. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Enter a rental order in the top text box and click the Open Rental Order button
  8. Click the Delete button 
  9. Click No
  10. Close the Existing Rental Order form
  11. 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 LearningPractical Learning: Using a Filter in a Record Set

  1. On the form, click the Account # text box
  2. In the Properties window, click Event and double-click On Lost Focus
  3. 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 LearningPractical Learning: Controlling a Sub-Form Using a Record Set

  1. In the Object combo box, select cmdShowTransactions
  2. 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
  3. In the Object combo box, select cmdClose
  4. Implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  5. Close Microsoft Visual Basic and return to Microsoft Access
  6. Save and close the form
  7. In the Navigation Pane, double-click NewDeposit
  8. 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  
  9. Close the NewDeposit form
  10. In the Navigation Pane, double-click NewWithdrawal
  11. 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
  12. Click Submit
  13. Close the NewWithdrawal form
  14. In the Navigation Pane, double-click AccountsTransactions to see the record of the 20-304042-49 account
  15. Close the AccountsTransactions form
  16. In the Navigation Pane, double-click NewDeposit
  17. 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.
  18. Close the NewDeposit form
  19. In the Navigation Pane, double-click NewWithdrawal
  20. 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
  21. Close the NewWithdrawal form
  22. In the Navigation Pane, double-click AccountsTransactions
  23. Navigate to the record of the 27-314257-84 account
  24. Close the AccountsTransactions form
  25. In the Navigation Pane, double-click NewWithdrawal
  26. 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
  27. Close the NewWithdrawal form
  28. In the Navigation Pane, double-click AccountsTransactions
  29. Navigate to the record of the 27-314257-84 account. Notice that the account has been suspended
  30. Close the AccountsTransactions form
  31. In the Employee # text box, type 000-100 and press Tab
  32. In the Withdrawal Date text box, type 01/22/2012 and press Tab twice
  33. In the Location Code text box, type 0MNGML and press Tab
  34. In the Account # text box, type 27-314257-84 and press Tab. Notice that you receive a message that money withdrawal is not allowed
  35. Click OK on the message box
  36. Close the NewWithdrawal form
  37. In the Navigation Pane, double-click NewDeposit
  38. 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  
  39. Close the NewDeposit form
  40. In the Navigation Pane, double-click NewWithdrawal
  41. 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
  42. Close the NewWithdrawal form
  43. In the Navigation Pane, double-click AccountsTransactions
  44. Navigate to the record of the 27-314257-84 account. Check the account history section
  45. Close the AccountsTransactions form
  46. In the Navigation Pane, double-click Deposit
  47. 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.
  48. Click Submit
  49. Close the NewDeposit form
  50. In the Navigation Pane, double-click NewCharge
  51. 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
  52. Close the NewCharge form
  53. In the Navigation Pane, double-click AccountsTransactions to see the balance of the 20-304042-49 account
  54. Close the AccountsTransactions form
  55. In the Navigation Pane, double-click TransactionsReview
  56. In the Account # text box, type 27-314257-84
  57. In the Transactions From box, type 01/15/2012
  58. In the To text box, type 01/25/2012
  59. Click the Show Transactions button
  60. Close the AccountsTransactions form
  61. In the Navigation Pane, double-click NewWithdrawal
  62. 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
  63. Close the NewWithdrawal form
  64. In the Navigation Pane, double-click NewCharge
  65. 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
  66. Close the NewCharge form
  67. In the Navigation Pane, double-click NewDeposit
  68. 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
  69. Close the NewCharge form
  70. In the Navigation Pane, double-click NewDeposit
  71. 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
  72. Close the NewCharge form
  73. Close Microsoft Access
 
 
   
 

Previous Copyright © 2000-2013 FunctionX, Inc. Next