Home

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

    Kolo Bank - Central Processubg

  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:

    Kolo Bank - Data Relationships

  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
 
 
   
 

Previous Copyright © 2005-2016, FunctionX, Inc. Next