Introduction to Record Sets in the DAO

Record Sets in DAO

Overview

A record set is a group of records of a table, a form, a query, a report, or a combination of different objects. To support record sets, the DAO library provides a class named Recordset.

Practical Learning: Introducing Records in DAO

1. Start Microsoft Access
2. In the list of files, click Kolo Bank from the previous lesson
3. In the Navigation Pane, right-click the Employees table and click Delete
4. Read the message box, then click Yes and click Yes
5. In the Navigation Pane, right-click the Locations table and click Delete
6. Read the message box and click Yes
7. In the Navigation Pane, right-click Central Processing and click Design View

The Type of a Record Set

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

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

Creating a Recordset

The primary way to start a record set is to declare a variable of type DAO.Recordset. How you get the set of records depends on the technique you use.

Once again, remember that the MAOL and DAO do things the same way:

• To create a record set that uses the records of a table, declare a variable of type DAO.Recordset and assign the name of the table to it. To support this technique, the DAO.Database class is equipped with a method named OpenRecordset. Its syntax is:

  expression.OpenRecordset(Name, Type, Options, LockEdit)

  Only the first argument is required. Therefore, to create a record set from a table, assign the name of that table to the DAO.Database.OpenRecordset() method and use the Set operator to get the returned value. Here is an example:

  Private Sub cmdGetRecordSet_Click()
      Dim dbExercise As DAO.Database
      Dim rsEmployees As DAO.Recordset
  
      Set dbExercise = CurrentDb
      Set rsEmployees = dbExercise.OpenRecordset("Employees")
      
      dbExercise.Close
      Set dbExercise = Nothing
  End Sub

• In DAO, the TableDef class is equipped with a method named OpenRecordset that holds its record set. To retrieve the record set of the table, first get a reference to that table. Then use the Set operator to assign a call to TableDef.OpenRecordset(). Here is an exampleP:

  Private Sub cmdCreateRecordSet_Click()
      Dim dbExercise As DAO.Database
      Dim rsCustomers As DAO.Recordset
      Dim tblCustomers As DAO.TableDef
  
      ' Get a reference to the current database
      Set dbExercise = CurrentDb
      ' Get a reference to a table named Customers
      Set tblCustomers = dbExercise.TableDefs("Customers")
      ' Create a Recordset object from the specified table
      Set rsCustomers = tblCustomers.OpenRecordset
  
      dbExercise.Close
      Set dbExercise = Nothing
  End Sub

• Some controls, like the combo box, the list box, the form, the query, or the report hold a list of records, which means they have their own record set. Such controls have a property named Recordset. To get a reference to that record set, declare a DAO.Recordset variable and assign the control's Recordset value to it. Here is an example:

  Private Sub cmdGetRecordSet_Click()
      Dim dbExercise As DAO.Database
      Dim rsEmployees As DAO.Recordset
  
      Set dbExercise = CurrentDb
  
      Set rsEmployees = Me.Recordset
  
      dbExercise.Close
      Set dbExercise = Nothing
  End Sub

• A form or report has a property named RecordSource that feeds it the values from a table. To get the set of records that the RecordSource property holds, access the RecordsetClone property of the form or report

Closing a Record Set

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

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsCustomers As DAO.Recordset
    Dim tblCustomers As DAO.TableDef

    Set dbExercise = CurrentDb
    Set tblCustomers = dbExercise.TableDefs("Customers")
    Set rsCustomers = tblCustomers.OpenRecordset
    
    rsCustomers.Close
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Alternatively, assign Nothing to the DAO.Recordset object. Here is an example:

Private Sub cmdCreateRecordSet_Click()
    Dim dbExercise As DAO.Database
    Dim rsCustomers As DAO.Recordset
    Dim tblCustomers As DAO.TableDef

    Set dbExercise = CurrentDb
    Set tblCustomers = dbExercise.TableDefs("Customers")
    Set rsCustomers = tblCustomers.OpenRecordset
    
    rsCustomers.Close
    Set rsCustomers = Nothing
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Introduction to Records

Introduction to Data Entry

Before performing actions on a table, you first need to get a reference to that table, which is equivalent to opening a record set. To do that, upi can call the OpenRecordset() method of the Database class. Remember that there are other ways to create or open a record set. Once you have the record set, you can perform data entry on it. This is done using the same steps as the MAOL. To start, call the AddNew() method of the Recordset class. Here is an example of calling it:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

To let you specify the value of a field, first access that field using either its index or its name applied to the Recordset variable. Use its Value property to assign the desired value. You can do this for the various fields whose values you want to create. To finalize the action, call the Update method of the Recordset class.

Data Entry on Strings

The way you provide a value for a field depends on its type. For example, the value of a text-based column must be provided in double-quotes. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees.Update
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

If the value is coming from a Windows control such as a text box, you can directly assign the name of the control to the field.

Practical Learning: Performing Data Entry on Text

1. The Kolo Bank database should still be opened with the Central Processing form in Design View.
   On the Central Processing form, right-click Accounts Types and click Build Event...
2. In the Choose Builder dialog box, click Code Builder and click OK
3. Type the follow code:

   Private Sub CreateAccountsTypesTable()
       Dim dbKoloBank As Database
       Dim fldAccountType As Field
       Dim fldDescription As Field
       Dim fldAccountIndex As Field
       Dim idxAccountsTypes As Index
       Dim tblAccountsTypes As TableDef
   
       ' Get a reference to the current database
       Set dbKoloBank = CurrentDb
       ' Create a new table named Students
       Set tblAccountsTypes = dbKoloBank.CreateTableDef("AccountsTypes")
       
       Set fldAccountType = tblAccountsTypes.CreateField("AccountType", dbText, 20)
       tblAccountsTypes.Fields.Append fldAccountType
       
       Set fldDescription = tblAccountsTypes.CreateField("Description", dbMemo)
       tblAccountsTypes.Fields.Append fldDescription
   
       ' Start creating an index that will be, or control, the primary key
       Set idxAccountsTypes = tblAccountsTypes.CreateIndex("PK_AccountsTypes")
       ' Specify this index as the primary key
       idxAccountsTypes.Primary = True
       ' Mak,e sure each value of the primary key will be unique
       idxAccountsTypes.Unique = True
       ' Make surre the user will provide a value for each record of the primary key
       idxAccountsTypes.Required = True
       
       Set fldAccountIndex = idxAccountsTypes.CreateField("AccountType")
       idxAccountsTypes.Fields.Append fldAccountIndex
       tblAccountsTypes.Indexes.Append idxAccountsTypes
       
       ' Add the Students table to the current database
       dbKoloBank.TableDefs.Append tblAccountsTypes
       
       dbKoloBank.Close
   End Sub
   
   Private Sub CreateAccountsTypesRecords()
       Dim dbKoloBank As Database
       Dim rsAccountsTypes As Recordset
       
       Set dbKoloBank = CurrentDb
       Set rsAccountsTypes = dbKoloBank.OpenRecordset("AccountsTypes")
   
       rsAccountsTypes.AddNew
       rsAccountsTypes("AccountType").Value = "CD"
       rsAccountsTypes("Description").Value = "Certificate of Deposit"
       rsAccountsTypes.Update
       
       rsAccountsTypes.AddNew
       rsAccountsTypes("AccountType").Value = "Saving"
       rsAccountsTypes("Description").Value = "This is the type of account where customers keep money for a relative amount of time without withdrawing it."
       rsAccountsTypes.Update
       
       rsAccountsTypes.AddNew
       rsAccountsTypes("AccountType").Value = "Checking"
       rsAccountsTypes("Description").Value = "This is the most common type of bank account where customers simply keep their money."
       rsAccountsTypes.Update
   
       dbKoloBank.Close
       Set dbKoloBank = Nothing
   End Sub
   
   Private Sub cmdAccountsTypes_Click()
       CreateAccountsTypesTable
       CreateAccountsTypesRecords
       
       Application.RefreshDatabaseWindow
       cmdAccountsTypes.Enabled = False
   End Sub

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

   Private Sub CreateTransactionsTypesTable()
       Dim dbKoloBank As Database
       Dim fldDescription As Field
       Dim fldTransactionType As Field
       Dim idxTransactionsTypes As Index
       Dim tblTransactionsTypes As TableDef
   
       Set dbKoloBank = CurrentDb
       Set tblTransactionsTypes = dbKoloBank.CreateTableDef("TransactionsTypes")
       
       Set fldTransactionType = tblTransactionsTypes.CreateField("TransactionType", DB_TEXT, 20)
       tblTransactionsTypes.Fields.Append fldTransactionType
       
       Set fldDescription = tblTransactionsTypes.CreateField("Description", DB_MEMO)
       tblTransactionsTypes.Fields.Append fldDescription
   
       Set idxTransactionsTypes = tblTransactionsTypes.CreateIndex("PK_TransactionsTypes")
       idxTransactionsTypes.Unique = True
       idxTransactionsTypes.Primary = True
       idxTransactionsTypes.Required = True
       
       Set fldTransactionType = idxTransactionsTypes.CreateField("TransactionType")
       idxTransactionsTypes.Fields.Append fldTransactionType
       tblTransactionsTypes.Indexes.Append idxTransactionsTypes
       
       dbKoloBank.TableDefs.Append tblTransactionsTypes
       
       dbKoloBank.Close
   End Sub
   
   Private Sub AddTransactionsTypesRecords()
       Dim dbKoloBank As Database
       Dim rsTransactionsTypes As Recordset
       
       Set dbKoloBank = CurrentDb
       Set rsTransactionsTypes = dbKoloBank.OpenRecordset("TransactionsTypes")
       
       rsTransactionsTypes.AddNew
       rsTransactionsTypes("TransactionType").Value = "Deposit"
       rsTransactionsTypes("Description").Value = "This operation involves the customer adding money to his or her account."
       rsTransactionsTypes.Update
   
       rsTransactionsTypes.AddNew
       rsTransactionsTypes("TransactionType").Value = "Withdrawal"
       rsTransactionsTypes("Description").Value = "This operation involves the customer retrieving money from his or her account."
       rsTransactionsTypes.Update
   
       rsTransactionsTypes.AddNew
       rsTransactionsTypes("TransactionType").Value = "Money Order"
       rsTransactionsTypes("Description").Value = "This allows customers to purchase a money order. During this operation, if the customer has an account with the bank, there is no fee. Otherwise, the customer is charged a small fee, such as $1.58."
       rsTransactionsTypes.Update
       
       rsTransactionsTypes.AddNew
       rsTransactionsTypes("TransactionType").Value = "Fund Transfer"
       rsTransactionsTypes("Description").Value = "In this operation, a customer transfers from one account to another."
       rsTransactionsTypes.Update
       
       rsTransactionsTypes.AddNew
       rsTransactionsTypes("TransactionType").Value = "Service Charge"
       rsTransactionsTypes("Description").Value = "In this operation, a customer transfers from one account to another."
       rsTransactionsTypes.Update
   
       dbKoloBank.Close
       Set dbKoloBank = Nothing
   End Sub
   
   Private Sub cmdTransactionsTypes_Click()
       CreateTransactionsTypesTable
       AddTransactionsTypesRecords
       
       Application.RefreshDatabaseWindow
       cmdTransactionsTypes.Enabled = False
   End Sub

6. In the Object combo box, select cmdAccountsStatus
7. Implement the event as follows:

   Private Sub CreateAccountsStatusTable()
       Dim dbKoloBank As Database
       Dim fldDescription As Field
       Dim fldStatusIndex As Field
       Dim fldAccountStatus As Field
       Dim idxAccountsStatus As Index
       Dim tblAccountsStatus As TableDef
   
       Set dbKoloBank = CurrentDb
       Set tblAccountsStatus = dbKoloBank.CreateTableDef("AccountsStatus")
       
       Set fldAccountStatus = tblAccountsStatus.CreateField("AccountStatus", dbText, 20)
       tblAccountsStatus.Fields.Append fldAccountStatus
       
       Set fldDescription = tblAccountsStatus.CreateField("Description", dbMemo)
       tblAccountsStatus.Fields.Append fldDescription
   
       Set idxAccountsStatus = tblAccountsStatus.CreateIndex("PK_AccountsStatus")
       idxAccountsStatus.Unique = True
       idxAccountsStatus.Primary = True
       idxAccountsStatus.Required = True
       
       Set fldStatusIndex = idxAccountsStatus.CreateField("AccountStatus")
       idxAccountsStatus.Fields.Append fldStatusIndex
       tblAccountsStatus.Indexes.Append idxAccountsStatus
       
       dbKoloBank.TableDefs.Append tblAccountsStatus
       
       dbKoloBank.Close
   End Sub
   
   Private Sub AddAccountsStatusRecords()
       Dim dbKoloBank As Database
       Dim rsAccountsStatus As Recordset
       
       Set dbKoloBank = CurrentDb
       Set rsAccountsStatus = dbKoloBank.OpenRecordset("AccountsStatus")
       
       rsAccountsStatus.AddNew
       rsAccountsStatus("AccountStatus").Value = "Active"
       rsAccountsStatus("Description").Value = "The customer currently has an account with this bank."
       rsAccountsStatus.Update
       
       rsAccountsStatus.AddNew
       rsAccountsStatus("AccountStatus").Value = "Closed"
       rsAccountsStatus("Description").Value = "The customer bank account has been closed."
       rsAccountsStatus.Update
   
       rsAccountsStatus.AddNew
       rsAccountsStatus("AccountStatus").Value = "Suspended"
       rsAccountsStatus("Description").Value = "The account has been suspended (but is still active), for any reason."
       rsAccountsStatus.Update
   
       dbKoloBank.Close
       Set dbKoloBank = Nothing
   End Sub
   
   Private Sub cmdAccountsStatus_Click()
       CreateAccountsStatusTable
       AddAccountsStatusRecords
       
       Application.RefreshDatabaseWindow
       cmdAccountsStatus.Enabled = False
   End Sub

8. In the Object combo box, select cmdChargesReasons
9. Implement the event as follows:

   Private Sub CreateChargesReasonsTable()
       Dim dbKoloBank As Database
       Dim fldDescription As Field
       Dim fldReasonIndex As Field
       Dim fldChargeReason As Field
       Dim idxChargesReasons As Index
       Dim tblChargesReasons As TableDef
   
       Set dbKoloBank = CurrentDb
       Set tblChargesReasons = dbKoloBank.CreateTableDef("ChargesReasons")
       
       Set fldChargeReason = tblChargesReasons.CreateField("ChargeReason", DB_TEXT, 20)
       tblChargesReasons.Fields.Append fldChargeReason
       
       Set fldDescription = tblChargesReasons.CreateField("Description", DB_MEMO)
       tblChargesReasons.Fields.Append fldDescription
   
       Set idxChargesReasons = tblChargesReasons.CreateIndex("PK_ChargesReasons")
       idxChargesReasons.Unique = True
       idxChargesReasons.Primary = True
       idxChargesReasons.Required = True
       
       Set fldReasonIndex = idxChargesReasons.CreateField("ChargeReason")
       idxChargesReasons.Fields.Append fldReasonIndex
       tblChargesReasons.Indexes.Append idxChargesReasons
       
       dbKoloBank.TableDefs.Append tblChargesReasons
       
       dbKoloBank.Close
   End Sub
   
   Private Sub AddChargesReasonsRecords()
       Dim dbKoloBank As Database
       Dim rsChargesReasons As Recordset
       
       Set dbKoloBank = CurrentDb
       Set rsChargesReasons = dbKoloBank.OpenRecordset("ChargesReasons")
       
       rsChargesReasons.AddNew
       rsChargesReasons("ChargeReason").Value = "Other Fee"
       rsChargesReasons("Description").Value = "This is a general fee for any type of service, such as a monthly fee applied to some account. A service fee may also be charged when an account is overdraft."
       rsChargesReasons.Update
       
       rsChargesReasons.AddNew
       rsChargesReasons("ChargeReason").Value = "Overdraft Fee"
       rsChargesReasons("Description").Value = "An amount applied if a customer's account remains negative for 72 hours."
       rsChargesReasons.Update
       
       rsChargesReasons.AddNew
       rsChargesReasons("ChargeReason").Value = "Monthly Charge"
       rsChargesReasons("Description").Value = "An amount applied every month to each accounts"
       rsChargesReasons.Update
       
       rsChargesReasons.AddNew
       rsChargesReasons("ChargeReason").Value = "Service Charge"
       rsChargesReasons("Description").Value = "This is a general fee for any type of service, such as a monthly fee applied to some account. A service fee may also be charged when an account is overdraft."
       rsChargesReasons.Update
   
       dbKoloBank.Close
       Set dbKoloBank = Nothing
   End Sub
   
   Private Sub cmdChargesReasons_Click()
       CreateChargesReasonsTable
       AddChargesReasonsRecords
       
       Application.RefreshDatabaseWindow
       cmdChargesReasons.Enabled = False
   End Sub

10. In the Object combo box, select cmdCurrenciesTypes
11. Implement the event as follows:

    Private Sub CreateCurrenciesTypesTable()
        Dim dbKoloBank As Database
        Dim fldDescription As Field
        Dim fldCurrencyType As Field
        Dim idxCurrenciesTypes As Index
        Dim tblCurrenciesTypes As TableDef
    
        Set dbKoloBank = CurrentDb
        Set tblCurrenciesTypes = dbKoloBank.CreateTableDef("CurrenciesTypes")
        
        Set fldCurrencyType = tblCurrenciesTypes.CreateField("CurrencyType", dbText, 20)
        tblCurrenciesTypes.Fields.Append fldCurrencyType
        
        Set fldDescription = tblCurrenciesTypes.CreateField("Description", dbMemo)
        tblCurrenciesTypes.Fields.Append fldDescription
    
        Set idxCurrenciesTypes = tblCurrenciesTypes.CreateIndex("PK_CurrenciesTypes")
        idxCurrenciesTypes.Unique = True
        idxCurrenciesTypes.Primary = True
        idxCurrenciesTypes.Required = True
        
        Set fldCurrencyType = idxCurrenciesTypes.CreateField("CurrencyType")
        idxCurrenciesTypes.Fields.Append fldCurrencyType
        tblCurrenciesTypes.Indexes.Append idxCurrenciesTypes
        
        dbKoloBank.TableDefs.Append tblCurrenciesTypes
        
        dbKoloBank.Close
    End Sub
    
    Private Sub AddCurrenciesTypesRecords()
        Dim dbKoloBank As Database
        Dim rsCurrenciesTypes As Recordset
        
        Set dbKoloBank = CurrentDb
        Set rsCurrenciesTypes = dbKoloBank.OpenRecordset("CurrenciesTypes")
        
        rsCurrenciesTypes.AddNew
        rsCurrenciesTypes("CurrencyType").Value = "Cash"
        rsCurrenciesTypes("Description").Value = "The operation was performed using cash."
        rsCurrenciesTypes.Update
        
        rsCurrenciesTypes.AddNew
        rsCurrenciesTypes("CurrencyType").Value = "Check"
        rsCurrenciesTypes("Description").Value = "A personal or business check was used for the transaction."
        rsCurrenciesTypes.Update
    
        rsCurrenciesTypes.AddNew
        rsCurrenciesTypes("CurrencyType").Value = "Direct Deposit"
        rsCurrenciesTypes("Description").Value = "A deposit was made electronically from an external entity to this bank."
        rsCurrenciesTypes.Update
        
        rsCurrenciesTypes.AddNew
        rsCurrenciesTypes("CurrencyType").Value = "External Transfer"
        rsCurrenciesTypes("Description").Value = "The transfer is/was made from an account from another bank."
        rsCurrenciesTypes.Update
    
        rsCurrenciesTypes.AddNew
        rsCurrenciesTypes("CurrencyType").Value = "Local Transfer"
        rsCurrenciesTypes("Description").Value = "The transfer is/was made from another account of our bank."
        rsCurrenciesTypes.Update
    
        rsCurrenciesTypes.AddNew
        rsCurrenciesTypes("CurrencyType").Value = "Money Order"
        rsCurrenciesTypes("Description").Value = "The transaction was carried through a money order."
        rsCurrenciesTypes.Update
    
        dbKoloBank.Close
        Set dbKoloBank = Nothing
    End Sub
    
    Private Sub cmdCurrenciesTypes_Click()
        CreateCurrenciesTypesTable
        AddCurrenciesTypesRecords
    
        Application.RefreshDatabaseWindow
        cmdCurrenciesTypes.Enabled = False
    End Sub

12. In the Object combo box, select cmdLocations
13. Implement the event as follows:

    Private Sub BuildBankLocations()
        Dim fldLocation As Field
        Dim idxLocations As Index
        Dim dbKoloBank As Database
        Dim tblLocations As TableDef
    
        Set dbKoloBank = CurrentDb
        Set tblLocations = dbKoloBank.CreateTableDef("Locations")
        
        Set fldLocation = tblLocations.CreateField("LocationCode", dbText, 10)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("LocationName", DB_TEXT, 50)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("Address", dbText, 50)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("City", DB_TEXT, 40)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("State", dbText, 2)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("ZIPCode", DB_TEXT, 20)
        tblLocations.Fields.Append fldLocation
        
        Set fldLocation = tblLocations.CreateField("Notes", dbMemo)
        tblLocations.Fields.Append fldLocation
    
        Set idxLocations = tblLocations.CreateIndex("PK_Locations")
        idxLocations.Unique = True
        idxLocations.Primary = True
        idxLocations.Required = True
        
        Set fldLocation = idxLocations.CreateField("LocationCode")
        idxLocations.Fields.Append fldLocation
        tblLocations.Indexes.Append idxLocations
        
        dbKoloBank.TableDefs.Append tblLocations
        
        dbKoloBank.Close
    End Sub
    
    Private Sub AddBankLocations()
        Dim dbKoloBank As Database
        Dim rsLocations As Recordset
        
        Set dbKoloBank = CurrentDb
        Set rsLocations = dbKoloBank.OpenRecordset("Locations")
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "1ONLIN"
        rsLocations("LocationName").Value = "Online"
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0ALXML"
        rsLocations("LocationName").Value = "ATM Alexandria Mall West"
        rsLocations("City").Value = "Alexandria"
        rsLocations("State").Value = "VA"
        rsLocations("Notes").Value = "ATM located inside the Alexandria Mall, on the west side."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0CLGPK"
        rsLocations("LocationName").Value = "ATM UMD College Park - Math Building"
        rsLocations("City").Value = "College Park"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM located in front of the Math Building of the University of University of MD, College Park Campus."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0GRNML"
        rsLocations("LocationName").Value = "ATM Greenbelt Mall"
        rsLocations("City").Value = "Greenbelt"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM in Greenbelt Mall"
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0LNFPL"
        rsLocations("LocationName").Value = "ATM L'Enfant Plaza"
        rsLocations("City").Value = "Washington"
        rsLocations("State").Value = "DC"
        rsLocations("Notes").Value = "ATM located at the main entrance of the L'Enfant Plaza metro station."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0MNGML"
        rsLocations("LocationName").Value = "ATM Montgomery Mall - North Entrance"
        rsLocations("City").Value = "Bethesda"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM located in the North side entrance of the Montgomery Mall."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0SSCTPL"
        rsLocations("LocationName").Value = "ATM Silver Spring City Place"
        rsLocations("City").Value = "Silver Spring"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM located inside the City Place mall in downtown Silver Spring."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0SSMTR"
        rsLocations("LocationName").Value = "ATM Silver Spring Metro Station"
        rsLocations("City").Value = "Silver Spring"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM located inside the Silver Spring Metro Station."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0UNSDC"
        rsLocations("LocationName").Value = "ATM Union Station"
        rsLocations("City").Value = "Washington"
        rsLocations("State").Value = "DC"
        rsLocations("Notes").Value = "ATM inside Union Station in DC."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0WPLZM"
        rsLocations("LocationName").Value = "ATM Wheaton Plaza Mall"
        rsLocations("City").Value = "Silver Spring"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM inside Wheaton Plaza Mall."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0WTFML"
        rsLocations("LocationName").Value = "ATM White Flint Mall"
        rsLocations("City").Value = "Rockville"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM located in front of the branch on the first floor of the White Flint Mall."
        rsLocations.Update
        
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "0SLS7LCK"
        rsLocations("LocationName").Value = "ATM Silver Spring 7-Locks"
        rsLocations("City").Value = "Silver Spring"
        rsLocations("State").Value = "MD"
        rsLocations("Notes").Value = "ATM located outside the Kolo Bank branch in the Seven-Locks shopping center in Silver Spring."
        rsLocations.Update
    
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "ALXJPZ"
        rsLocations("LocationName").Value = "Junino Plaza"
        rsLocations("Address").Value = "3382 Junino Ave"
        rsLocations("City").Value = "Alexandria"
        rsLocations("State").Value = "VA"
        rsLocations("ZIPCode").Value = "22132"
        rsLocations.Update
    
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "CPKUMD"
        rsLocations("LocationName").Value = "University of Maryland"
        rsLocations("Address").Value = "808 Vieira Drive"
        rsLocations("City").Value = "College Park"
        rsLocations("State").Value = "MD"
        rsLocations("ZIPCode").Value = "20707"
        rsLocations.Update
    
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "DCK10S"
        rsLocations("LocationName").Value = "Kennedy & 10th Street"
        rsLocations("Address").Value = "10368 Sesame Lolane Rd N.E."
        rsLocations("City").Value = "Washington"
        rsLocations("State").Value = "DC"
        rsLocations("ZIPCode").Value = "20012"
        rsLocations.Update
    
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "GTWMST"
        rsLocations("LocationName").Value = "Georgetown M Street"
        rsLocations("Address").Value = "2602 M Street N.W."
        rsLocations("City").Value = "Washington"
        rsLocations("State").Value = "DC"
        rsLocations("ZIPCode").Value = "20008"
        rsLocations.Update
    
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "SLVSSL"
        rsLocations("LocationName").Value = "Seven Lock Corner"
        rsLocations("Address").Value = "3925 Euler Ave"
        rsLocations("City").Value = "Silver Spring"
        rsLocations("State").Value = "MD"
        rsLocations("ZIPCode").Value = "20904"
        rsLocations("Notes").Value = "Branch located in the Seven-Locks shopping center in Silver Spring."
        rsLocations.Update
    
        rsLocations.AddNew
        rsLocations("LocationCode").Value = "WHTFLT"
        rsLocations("LocationName").Value = "White Flint Mall"
        rsLocations("Address").Value = "12044 Rte 255"
        rsLocations("City").Value = "Rockville"
        rsLocations("State").Value = "MD"
        rsLocations("ZIPCode").Value = "20854"
        rsLocations("Notes").Value = "The branch is located in the first floor of the mall."
        rsLocations.Update
    
        rsLocations.Close
        dbKoloBank.Close
    End Sub
    
    Private Sub cmdBankLocations_Click()
        BuildBankLocations
        AddBankLocations
        
        Application.RefreshDatabaseWindow
        cmdBankLocations.Enabled = False
    End Sub

14. In the Object combo box, select cmdEmployeesTable
15. Implement the event as follows:

    Private Sub cmdEmployeesTable_Click()
        Dim fldEmployee As Field
        Dim idxEmployees As Index
        Dim dbKoloBank As Database
        Dim tblEmployees As TableDef
        Dim relEmployees As Relation
        
        Set dbKoloBank = CurrentDb
        Set tblEmployees = dbKoloBank.CreateTableDef("Employees")
    
        Set fldEmployee = tblEmployees.CreateField("EmployeeNumber", dbText, 10)
        tblEmployees.Fields.Append fldEmployee
        
        tblEmployees.Fields.Append tblEmployees.CreateField("FirstName", dbText, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("MiddleName", DB_TEXT, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("LastName", dbText, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("LocationCode", DB_TEXT, 10)
        tblEmployees.Fields.Append tblEmployees.CreateField("Title", dbText, 50)
        tblEmployees.Fields.Append tblEmployees.CreateField("CanCreateNewAccount", dbBoolean)
        tblEmployees.Fields.Append tblEmployees.CreateField("Address", dbText, 50)
        tblEmployees.Fields.Append tblEmployees.CreateField("City", dbText, 40)
        tblEmployees.Fields.Append tblEmployees.CreateField("State", DB_TEXT, 2)
        tblEmployees.Fields.Append tblEmployees.CreateField("ZIPCode", DB_TEXT, 20)
        tblEmployees.Fields.Append tblEmployees.CreateField("HourlySalary", dbDouble)
    
        Set idxEmployees = tblEmployees.CreateIndex("PK_Employees")
        idxEmployees.Unique = True
        idxEmployees.Primary = True
        idxEmployees.Required = True
        
        Set fldEmployee = idxEmployees.CreateField("EmployeeNumber")
        idxEmployees.Fields.Append fldEmployee
        tblEmployees.Indexes.Append idxEmployees
        
        dbKoloBank.TableDefs.Append tblEmployees
    
        Set relEmployees = dbKoloBank.CreateRelation("EmployeesLocations", "Locations", "Employees", _
                                                     dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
        relEmployees.Fields.Append relEmployees.CreateField("LocationCode")
        relEmployees.Fields!LocationCode.ForeignName = "LocationCode"
        dbKoloBank.Relations.Append relEmployees
        
        Application.RefreshDatabaseWindow
        dbKoloBank.Close
        
        cmdEmployeesTable.Enabled = False
    End Sub

1. In the Object combo box, select cmdCustomersTable
2. Implement the event as follows:

   Private Sub cmdCustomersTable_Click()
       Dim fldCustomer As Field
       Dim idxCustomers As Index
       Dim dbKoloBank As Database
       Dim relEmployees As Relation
       Dim tblCustomers As TableDef
       Dim relAccountsTypes As Relation
       Dim relAccountsStatus As Relation
   
       Set dbKoloBank = CurrentDb
       Set tblCustomers = dbKoloBank.CreateTableDef("Customers")
   
       tblCustomers.Fields.Append tblCustomers.CreateField("AccountNumber", dbText, 15)
       tblCustomers.Fields.Append tblCustomers.CreateField("EmployeeNumber", dbText, 10)
       tblCustomers.Fields.Append tblCustomers.CreateField("DateCreated", dbDate)
       tblCustomers.Fields.Append tblCustomers.CreateField("AccountType", DB_TEXT, 20)
       tblCustomers.Fields.Append tblCustomers.CreateField("FirstName", DB_TEXT, 20)
       tblCustomers.Fields.Append tblCustomers.CreateField("MiddleName", DB_TEXT, 20)
       tblCustomers.Fields.Append tblCustomers.CreateField("LastName", dbText, 20)
       tblCustomers.Fields.Append tblCustomers.CreateField("Address", dbText, 50)
       tblCustomers.Fields.Append tblCustomers.CreateField("City", dbText, 40)
       tblCustomers.Fields.Append tblCustomers.CreateField("State", dbText, 2)
       tblCustomers.Fields.Append tblCustomers.CreateField("ZIPCode", DB_TEXT, 20)
       tblCustomers.Fields.Append tblCustomers.CreateField("AccountStatus", DB_TEXT, 20)
   
       Set idxCustomers = tblCustomers.CreateIndex("PK_Customers")
       idxCustomers.Unique = True
       idxCustomers.Primary = True
       idxCustomers.Required = True
       
       Set fldCustomer = idxCustomers.CreateField("AccountNumber")
       idxCustomers.Fields.Append fldCustomer
       tblCustomers.Indexes.Append idxCustomers
   
       dbKoloBank.TableDefs.Append tblCustomers
   
       Set relEmployees = dbKoloBank.CreateRelation("AccountsCreators", "Employees", "Customers", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relEmployees.Fields.Append relEmployees.CreateField("EmployeeNumber")
       relEmployees.Fields!EmployeeNumber.ForeignName = "EmployeeNumber"
       dbKoloBank.Relations.Append relEmployees
   
       Set relAccountsTypes = dbKoloBank.CreateRelation("CustomersAccountsTypes", "AccountsTypes", "Customers", _
                                                        dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relAccountsTypes.Fields.Append relAccountsTypes.CreateField("AccountType")
       relAccountsTypes.Fields!AccountType.ForeignName = "AccountType"
       dbKoloBank.Relations.Append relAccountsTypes
   
       Set relAccountsStatus = dbKoloBank.CreateRelation("CustomersAccountsStatus", "AccountsStatus", "Customers", _
                                                        dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relAccountsStatus.Fields.Append relAccountsStatus.CreateField("AccountStatus")
       relAccountsStatus.Fields!AccountStatus.ForeignName = "AccountStatus"
       dbKoloBank.Relations.Append relAccountsStatus
   
       Application.RefreshDatabaseWindow
       dbKoloBank.Close
       cmdCustomersTable.Enabled = False
   End Sub

3. In the Object combo box, select cmdAccountsHistoriesTable
4. Implement the event as follows:

   Private Sub cmdAccountsHistoriesTable_Click()
       Dim dbKoloBank As Database
       Dim fldAccountHistory As Field
       Dim idxAccountsHistories As Index
       Dim tblAccountsHistories As TableDef
       Dim relCustomers As Relation, relAccountsStatus As Relation
   
       Set dbKoloBank = CurrentDb
       Set tblAccountsHistories = dbKoloBank.CreateTableDef("AccountsHistories")
       
       Set fldAccountHistory = tblAccountsHistories.CreateField("AccountHistoryID", dbLong)
       fldAccountHistory.Attributes = dbAutoIncrField
       tblAccountsHistories.Fields.Append fldAccountHistory
       
       tblAccountsHistories.Fields.Append tblAccountsHistories.CreateField("AccountNumber", dbText, 20)
       tblAccountsHistories.Fields.Append tblAccountsHistories.CreateField("AccountStatus", DB_TEXT, 20)
       tblAccountsHistories.Fields.Append tblAccountsHistories.CreateField("DateChanged", dbDate)
       tblAccountsHistories.Fields.Append tblAccountsHistories.CreateField("TimeChanged", DB_DATE)
       tblAccountsHistories.Fields.Append tblAccountsHistories.CreateField("ShortNote", dbText, 150)
       tblAccountsHistories.Fields.Append tblAccountsHistories.CreateField("DetailedNotes", dbMemo)
   
       Set idxAccountsHistories = tblAccountsHistories.CreateIndex("PK_AccountsHistories")
       idxAccountsHistories.Unique = True
       idxAccountsHistories.Primary = True
       idxAccountsHistories.Required = True
       
       Set fldAccountHistory = idxAccountsHistories.CreateField("AccountHistoryID")
       idxAccountsHistories.Fields.Append fldAccountHistory
       tblAccountsHistories.Indexes.Append idxAccountsHistories
   
       dbKoloBank.TableDefs.Append tblAccountsHistories
   
       Set relCustomers = dbKoloBank.CreateRelation("AccountsSummaries", "Customers", "AccountsHistories", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relCustomers.Fields.Append relCustomers.CreateField("AccountNumber")
       relCustomers.Fields!AccountNumber.ForeignName = "AccountNumber"
       dbKoloBank.Relations.Append relCustomers
   
       Set relAccountsStatus = dbKoloBank.CreateRelation("StatusOfHistories", "AccountsStatus", "AccountsHistories", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relAccountsStatus.Fields.Append relAccountsStatus.CreateField("AccountStatus")
       relAccountsStatus.Fields!AccountStatus.ForeignName = "AccountStatus"
       dbKoloBank.Relations.Append relAccountsStatus
       
       Application.RefreshDatabaseWindow
       dbKoloBank.Close
       cmdAccountsHistoriesTable.Enabled = False
   End Sub

5. In the Object combo box, select cmdTransactionsTable
6. Implement the event as follows:

   Private Sub cmdTransactionsTable_Click()
       Dim dbKoloBank As Database
       Dim fldTransaction As Field
       Dim idxTransactions As Index
       Dim tblTransactions As TableDef
       Dim relTransactions As Relation
   
       Set dbKoloBank = CurrentDb
       Set tblTransactions = dbKoloBank.CreateTableDef("Transactions")
       
       Set fldTransaction = tblTransactions.CreateField("TransactionNumber", dbLong)
       fldTransaction.Attributes = dbAutoIncrField
       tblTransactions.Fields.Append fldTransaction
       
       tblTransactions.Fields.Append tblTransactions.CreateField("EmployeeNumber", dbText, 10)
       tblTransactions.Fields.Append tblTransactions.CreateField("LocationCode", DB_TEXT, 10)
       tblTransactions.Fields.Append tblTransactions.CreateField("TransactionDate", dbDate)
       tblTransactions.Fields.Append tblTransactions.CreateField("TransactionTime", DB_DATE)
       tblTransactions.Fields.Append tblTransactions.CreateField("AccountNumber", dbText, 20)
       tblTransactions.Fields.Append tblTransactions.CreateField("TransactionType", DB_TEXT, 20)
       tblTransactions.Fields.Append tblTransactions.CreateField("CurrencyType", dbText, 20)
       tblTransactions.Fields.Append tblTransactions.CreateField("Deposit", dbDouble)
       tblTransactions.Fields.Append tblTransactions.CreateField("Withdrawal", DB_DOUBLE)
       tblTransactions.Fields.Append tblTransactions.CreateField("Charge", dbDouble)
       tblTransactions.Fields.Append tblTransactions.CreateField("ChargeReason", dbText, 20)
       tblTransactions.Fields.Append tblTransactions.CreateField("Balance", DB_DOUBLE)
       tblTransactions.Fields.Append tblTransactions.CreateField("Notes", DB_MEMO)
   
       Set idxTransactions = tblTransactions.CreateIndex("PK_Transactions")
       idxTransactions.Unique = True
       idxTransactions.Primary = True
       idxTransactions.Required = True
       
       Set fldTransaction = idxTransactions.CreateField("TransactionNumber")
       idxTransactions.Fields.Append fldTransaction
       tblTransactions.Indexes.Append idxTransactions
   
       dbKoloBank.TableDefs.Append tblTransactions
   
       Set relTransactions = dbKoloBank.CreateRelation("TransactionsProcessors", "Employees", "Transactions", _
                                                       dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relTransactions.Fields.Append relTransactions.CreateField("EmployeeNumber")
       relTransactions.Fields!EmployeeNumber.ForeignName = "EmployeeNumber"
       dbKoloBank.Relations.Append relTransactions
   
       Set relTransactions = dbKoloBank.CreateRelation("TransactionsLocations", "Locations", "Transactions", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relTransactions.Fields.Append relTransactions.CreateField("LocationCode")
       relTransactions.Fields!LocationCode.ForeignName = "LocationCode"
       dbKoloBank.Relations.Append relTransactions
   
       Set relTransactions = dbKoloBank.CreateRelation("TransactionsAccounts", "Customers", "Transactions", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relTransactions.Fields.Append relTransactions.CreateField("AccountNumber")
       relTransactions.Fields!AccountNumber.ForeignName = "AccountNumber"
       dbKoloBank.Relations.Append relTransactions
   
       Set relTransactions = dbKoloBank.CreateRelation("TypesOfTransactions", "TransactionsTypes", "Transactions", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relTransactions.Fields.Append relTransactions.CreateField("TransactionType")
       relTransactions.Fields!TransactionType.ForeignName = "TransactionType"
       dbKoloBank.Relations.Append relTransactions
   
       Set relTransactions = dbKoloBank.CreateRelation("TransactionsCurrencies", "CurrenciesTypes", "Transactions", _
                                                       dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relTransactions.Fields.Append relTransactions.CreateField("CurrencyType")
       relTransactions.Fields!CurrencyType.ForeignName = "CurrencyType"
       dbKoloBank.Relations.Append relTransactions
   
       Set relTransactions = dbKoloBank.CreateRelation("TransactionsCharges", "ChargesReasons", "Transactions", _
                                                    dbRelationUpdateCascade Or dbRelationDeleteCascade Or dbRelationLeft)
       relTransactions.Fields.Append relTransactions.CreateField("ChargeReason")
       relTransactions.Fields!ChargeReason.ForeignName = "ChargeReason"
       dbKoloBank.Relations.Append relTransactions
       
       Application.RefreshDatabaseWindow
       dbKoloBank.Close
       cmdTransactionsTable.Enabled = False
   End Sub

7. In the Object combo box, select cmdClose
8. Implement the event as follows:

   Private Sub cmdClose_Click()
       DoCmd.Close
   End Sub

9. Close Microsoft Visual Basic and return to Microsoft Access
10. Switch the form to Form View

    

11. Click the Accounts Types, the Transactions Types, the Accounts Status, the Charges Reasons, the Currencies Types, the Bank Locations, the Employees, the Customers, the Accounts Histories, the Transactions, and the Close buttons
12. When asked whether you want to save the form, click Yes
13. On the Ribbon, click Database Tools and click Relationships
14. Position the items as you see fit. Here is an example:

    

15. To close the Relationships window, click the Close button on the Ribbon
16. When asked whether you want to save, click Yes

Data Entry on Numeric Values

To specify the value of a number-based field, simply assign the desired value to its Value property. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 92073
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees("IsFullTime").Value = True
    rsEmployees.Update

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

If the value is stored in, or coming from, a Windows control such as a text box, you can first convert it before assigning it to the field.

Data Entry on Boolean Fields

To specify the value of a Boolean field, assign True or False to its Value property. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees("IsFullTime").Value = True
    rsEmployees.Update
    
    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Data Entry on Date/Time Fields

To perform data entry of date or time fields in DAO, create the date and/or time value between # and #, then assign it to the Value property of the DAO.Field object. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 92073
    rsEmployees("DateHired").Value = #4/12/2010#
    rsEmployees("EmployeeName").Value = "John Schwartz"
    rsEmployees("IsFullTime").Value = True
    rsEmployees.Update

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

You can also provide the value in double-quotes. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As DAO.Database
    Dim rsEmployees As DAO.Recordset
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 94055
    rsEmployees("DateHired").Value = "10/05/2008"
    rsEmployees("EmployeeName").Value = "Alain Binam"
    rsEmployees("IsFullTime").Value = False
    rsEmployees.Update

    dbExercise.Close
    Set dbExercise = Nothing
End Sub

Of course, if the value is gotten trough a Windows control and especially if it is not obviously such as a date or time value from a text box, you should first convert it.

Learning: Performing Data Entry on Number-Based Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click Sweet Star Clothers from Lesson 29
3. In the Navigation Pane, right-click Payroll Preparation and click Design View
4. On the form, right-click the Submit Payroll button and click Build Event...
5. In the Choose Builder dialog box, click Code Builder and click OK
6. Implement the event as follows:

   Private Sub cmdSubmitPayroll_Click()
       Dim dbSweetStar As Database
       Dim rsPayrolls As Recordset
       
       If IsNull(txtPayDate) Or Not IsDate(txtPayDate) Then
           MsgBox "You must provide a valid pay date.", _
                  vbOKOnly Or vbInformation, "Sweet Star Clothers"
           Exit Sub
       End If
       
       If IsNull(txtEmployeeNumber) Then
           MsgBox "You must provide a employee number.", _
                  vbOKOnly Or vbInformation, "Sweet Star Clothers"
           Exit Sub
       End If
       
       Set dbSweetStar = CurrentDb
       Set rsPayrolls = dbSweetStar.OpenRecordset("Payrolls", _
                                                   RecordsetTypeEnum.dbOpenTable, _
                                                   RecordsetOptionEnum.dbAppendOnly, _
                                                   LockTypeEnum.dbPessimistic)
       
       rsPayrolls.AddNew
       rsPayrolls!PayDate = CDate(txtPayDate)
       rsPayrolls!EmployeeNumber = txtEmployeeNumber
       rsPayrolls!WorkUnits = txtWorkUnits
       rsPayrolls!NetPay = CDbl(Nz(txtNetPay))
       rsPayrolls.Update
       
       Set rsPayrolls = Nothing
       Set dbSweetStar = Nothing
           
       MsgBox "The payroll has been processed and approved.", _
              vbOKOnly Or vbInformation, "Sweet Star Clothers"
       
       DoCmd.Close
   End Sub

7. Close Microsoft Visual Basic and return to Microsoft Access
8. Save the form
9. Close Microsoft Access