Home

Characteristics of a Record Set in the MAOL and DAO

Assistance With Data Entry

Setting a Default Value for a Field

You can specify a default value for a field so the user can skip that field during data entry. To support this, the DAO.Field class is equipped with a property named DefaultValue. To specify a default value for a new field you are creating, assign that value to it. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeName As DAO.Field
    Dim fldEmployeeNumber As DAO.Field
    Dim fldEmploymentStatus As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT, 10)
    fldEmployeeNumber.Required = True
    tblEmployees.Fields.Append fldEmployeeNumber

    Set fldEmployeeName = tblEmployees.CreateField("EmployeeName", DB_TEXT, 100)
    tblEmployees.Fields.Append fldEmployeeName

    Set fldEmploymentStatus = _
        tblEmployees.CreateField("EmploymentStatus", DB_TEXT, 20)
    fldEmploymentStatus.DefaultValue = "Full Time"
    tblEmployees.Fields.Append fldEmploymentStatus

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close
   Set dbExercise = Nothing

   Application.RefreshDatabaseWindow
End Sub

Once a field has a default value, it can be skipped during data entry, in which case the default value would be used for its value.

Learning: Introducing Characteristics of a Record Set in DAO

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Watts A Loan from Lesson 29

Field Nullity

In the DAO library, the Field class provides the Required property. You can use this property to allow the user to skip a field during data entry. You can also use this property to make sure a value is entered for the field before the record is considered complete. The default value of this property is False. If you set it to True, a value must always be entered for the field. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim dbExercise As DAO.Database
    Dim tblEmployees As DAO.TableDef
    Dim fldEmployeeName As DAO.Field
    Dim fldEmailAddress As DAO.Field
    Dim fldEmployeeNumber As DAO.Field
    
    ' Specify the database to use
    Set dbExercise = CurrentDb

    ' Create a new TableDef object.
    Set tblEmployees = dbExercise.CreateTableDef("Employees")

    Set fldEmployeeNumber = tblEmployees.CreateField("EmployeeNumber", DB_TEXT)
    fldEmployeeNumber.Required = True
    tblEmployees.Fields.Append fldEmployeeNumber

    Set fldEmployeeName = tblEmployees.CreateField("EmployeeName", DB_TEXT)
    tblEmployees.Fields.Append fldEmployeeName

    Set fldEmailAddress = tblEmployees.CreateField("EmailAddress", DB_TEXT)
    tblEmployees.Fields.Append fldEmailAddress

   ' Add the new table to the database.
   dbExercise.TableDefs.Append tblEmployees
   dbExercise.Close

   Application.RefreshDatabaseWindow
End Sub

If you try or the user tries skipping a field whose Required property is set to True, the database would produce an error.

The Characteristics of a Record Set

Introduction

We saw that, to create a record set, you could call the OpenRecordset() method of a DAO.Database variable. Its syntax is:

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

A record set follows some rules in the way it is created or the way it is used. The rules concern the name or source of data and the type of record set, etc. So far, we provided the source of data as a name of a table.

The Type of a Record Set

A record set must specify the way its records would be accessed and used. This information is provided by the second argument of the OpenRecordset() method. The value of this argument is (must represent) a member of the RecordsetTypeEnum enumeration. If the first argument is the name of a table, the second argument nust be set as 1 or dbOpenTable.

The Options of a Record Set

Further options are used to control the actions that can be performed on the Recordset object. These options are specified through the third argument of the OpenRecordset() method. The options are members of the RecordsetOptionEnum enumeration and they can be provided as an OR combination. The available values are:

RecordsetOptionEnum Member Constant Value Description
dbDenyWrite 1 The user cannot change a record in the record set
dbDenyRead 2 While a person is using the record set, other people cannot access the records in that set
dbReadOnly 4 The record set is open to only see the values in the records
dbAppendOnly 8 The record set must be opened as a dynaset. Then, a user can add a new record to the record set but cannot see the existing records
dbInconsistent 16 The record set must be opened as a dynaset or a snapshot. The record(s) can be updated and an update in one record can affect other records of the same dynaset
dbConsistent 32 The record set must be opened as a dynaset or a snapshot. The record(s) can be updated but only if an update will not change other records of the same dynaset. Although you can combine some values of the the RecordsetOptionEnum enumeration, you can use either dbInconsistent or dbConsistent but not both
dbSQLPassThrough 64 The record set must be opened as a snapshot using a SQL statement
dbFailOnError 128 If an error occurs, the record set is dismissed
dbForwardOnly 256 The record set must be created as a forward-only snapshot
dbSeeChanges 512 The record set must be created as a dynaset. If a problem occurs, the database engine generates a run-time error
dbRunAsync 1024 The record set is created as an asynchronous SQL statement
dbExecDirect 2048 The record set executes withoug first calling the SQLPrepare() function

Remember that the value you specify for this argument depends on the second and may depend on the fourth arguments.

Locking a Record Set

The last argument controls how to deal with simultaneous connections to the same database. The value of this argument is a member of the LockTypeEnum enumeration. The available values are:

LockTypeEnum Member Constant Value
dbOptimisticValue 1
dbOptimistic 3
dbOptimisticBatch 5

Practical LearningPractical Learning: Adding Records

  1. In the Navigation Pane, right-click the Payroll form and click Design View
  2. On the form, right-click the Approve Submit Payroll button and click Build Event...
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows: 
    Private Sub cmdApproveSubmitPayroll_Click()
    Dim rsPayrolls As Recordset
    Dim dbWattsALoan As Database
    
    If IsNull(txtStartDate) Then
        MsgBox "You must enter a valid time sheet start date.", _
               vbOKOnly Or vbInformation, "Watts A Loan"
        Exit Sub
    End If
    
    If IsNull(txtEmployeeNumber) Then
        MsgBox "Please enter a valid employee number to identity " & _
               "the employee whose payroll is being prepared.", _
               vbOKOnly Or vbInformation, "Watts A Loan"
        Exit Sub
    End If
    
    Set dbWattsALoan = CurrentDb
    Set rsPayrolls = dbWattsALoan.OpenRecordset("Payrolls", _
                                                RecordsetTypeEnum.dbOpenTable, _
                                                RecordsetOptionEnum.dbAppendOnly, _
                                                LockTypeEnum.dbPessimistic)
    
    rsPayrolls.AddNew
    rsPayrolls("StartDate").Value = CDate(txtStartDate)
    rsPayrolls("PayDate").Value = txtPayDate
    rsPayrolls("EmployeeNumber").Value = txtEmployeeNumber
    rsPayrolls("EmployeeName").Value = txtEmployeeName
    rsPayrolls("HourlySalary").Value = CDbl(Nz(txtHourlySalary))
    rsPayrolls("RegularTime").Value = CDbl(Nz(txtRegularTime))
    rsPayrolls("RegularPay").Value = CDbl(Nz(txtRegularPay))
    rsPayrolls("Overtime").Value = CDbl(Nz(txtOvertime))
    rsPayrolls("OvertimePay").Value = CDbl(Nz(txtOvertimePay))
    rsPayrolls("GrossPay").Value = CDbl(Nz(txtOvertimePay))
    rsPayrolls("FederalTax").Value = CDbl(Nz(txtFederalWithholdingTax))
    rsPayrolls("SocialSecurityTax").Value = CDbl(Nz(txtSocialSecurityTax))
    rsPayrolls("MedicareTax").Value = CDbl(Nz(txtMedicareTax))
    rsPayrolls("StateTax").Value = CDbl(Nz(txtStateTax))
    rsPayrolls.Update
    
    ' Let the customer know that the deposit was made.
    MsgBox "The payroll has been prepared and approved.", _
           vbOKOnly Or vbInformation, "Watts A Loan"
    
    DoCmd.Close
End Sub
  5. Return to Microsoft Access and save the form
  6. On the Ribbon, click File and click Open
  7. In the list of files, click Kolo Bank from the previous lesson
  8. On the Ribbon, click Create and click Form Design
  9. In the Controls section of the Design tab of the Ribbon, click the Button and click the form.
    If a wizard starts, click Cancel
  10. On the form, right-click the new button and click Build Event...
  11. In the Choose Builder dialog box, click Code Builder and click OK
  12. Create two procedures and call them from the event of the button as follows: 
    Private Sub HireSomeEmployees()
    Dim dbKoloBank As Database
    Dim rsEmployees As Recordset
    
    Set dbKoloBank = CurrentDb
    Set rsEmployees = dbKoloBank.OpenRecordset("Employees", _
	                	               RecordsetTypeEnum.dbOpenTable, _
        	                	       RecordsetOptionEnum.dbDenyWrite, _
	                	               LockTypeEnum.dbOptimistic)
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-101"
    rsEmployees!FirstName = "Automatic"
    rsEmployees!MiddleName = "Teller"
    rsEmployees!LastName = "Machine"
    rsEmployees!LocationCode = "0ALXML"
    rsEmployees!Title = "ATM"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-102": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0CLGPK": rsEmployees!Title = "ATM"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-103": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0GRNML": rsEmployees!Title = "ATM"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-104": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0LNFPL": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-105": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0MNGML": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-106": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0SSCTPL": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-107": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0SSMTR": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-108": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0UNSDC": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-109": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0WPLZM": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-110": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0WTFML": rsEmployees!Title = "ATM"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "000-111": rsEmployees!FirstName = "Automatic": rsEmployees!MiddleName = "Teller": rsEmployees!LastName = "Machine": rsEmployees!LocationCode = "0SLS7LCK": rsEmployees!Title = "ATM": rsEmployees!Address = "3925 Euler Ave": rsEmployees!City = "Silver Spring": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20904"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "100-200": rsEmployees!FirstName = "Automatic": rsEmployees!LastName = "Transaction": rsEmployees!LocationCode = "1ONLIN": rsEmployees!Title = "Automatic Computer Transaction"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "111-111": rsEmployees!FirstName = "Online": rsEmployees!LastName = "Web": rsEmployees!LocationCode = "1ONLIN": rsEmployees!Title = "Online Transaction"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "209-400": rsEmployees!FirstName = "Krista": rsEmployees!MiddleName = "Daniela": rsEmployees!LastName = "Cole": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Branch Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "1026 Jacktown St": rsEmployees!City = "Baltimore": rsEmployees!State = "MD": rsEmployees!ZIPCode = "21205"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "248-552": rsEmployees!FirstName = "Michael": rsEmployees!MiddleName = "William": rsEmployees!LastName = "Olney": rsEmployees!LocationCode = "CPKUMD": rsEmployees!Title = "Cashier": rsEmployees!Address = "3832 Great River Rd": rsEmployees!City = "Silver Spring": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20906"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "279-377": rsEmployees!FirstName = "Michael": rsEmployees!LastName = "Dobmeyer": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Shift Programmer": rsEmployees!Address = "12 16th St. S. W.": rsEmployees!City = "Washington": rsEmployees!State = "DC": rsEmployees!ZIPCode = "20008"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "280-082": rsEmployees!FirstName = "Geoffrey": rsEmployees!MiddleName = "Robert": rsEmployees!LastName = "Coleman": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Head Cashier": rsEmployees!CanCreateNewAccount = 1: rsEmployees!Address = "828 John Booker St": rsEmployees!City = "Chevy Chase": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20851"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "284-005": rsEmployees!FirstName = "Anne": rsEmployees!MiddleName = "Laura": rsEmployees!LastName = "Wine": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Cashier": rsEmployees!Address = "4137 Chivas Crt": rsEmployees!City = "Silver Spring": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20910"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "284-725": rsEmployees!FirstName = "Wanda": rsEmployees!MiddleName = "Mary": rsEmployees!LastName = "Dundon": rsEmployees!LocationCode = "ALXJPZ": rsEmployees!Title = "Cashier": rsEmployees!Address = "614 Simpson Ave": rsEmployees!City = "Takoma Park": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20912"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "294-075": rsEmployees!FirstName = "Luis": rsEmployees!LastName = "Velker": rsEmployees!LocationCode = "ALXJPZ": rsEmployees!Title = "Branch Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "9502 Copher Ave": rsEmployees!City = "Alexandria": rsEmployees!State = "VA": rsEmployees!ZIPCode = "22312"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "461-842": rsEmployees!FirstName = "Dorrin": rsEmployees!MiddleName = "Annette": rsEmployees!LastName = "Vive": rsEmployees!LocationCode = "CPKUMD": rsEmployees!Title = "Branch Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "273 S. Independence Ave.": rsEmployees!City = "College Park": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20747"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "462-777": rsEmployees!FirstName = "Ada": rsEmployees!LastName = "Zeran": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Administrative Assistant": rsEmployees!Address = "992 White Horse Rd": rsEmployees!City = "Hyattsville": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20782"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "481-114": rsEmployees!FirstName = "Samuel": rsEmployees!MiddleName = "Arthur": rsEmployees!LastName = "Lansing": rsEmployees!LocationCode = "CPKUMD": rsEmployees!Title = "Cashier": rsEmployees!Address = "9337 Cachet St": rsEmployees!City = "Beltsville": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20705"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "482-799": rsEmployees!FirstName = "Annette": rsEmployees!MiddleName = "Joan": rsEmployees!LastName = "Roberts": rsEmployees!LocationCode = "GTWMST": rsEmployees!Title = "Cashier": rsEmployees!Address = "1277 Cecil Maurice Av. NW": rsEmployees!City = "Washington": rsEmployees!State = "DC": rsEmployees!ZIPCode = "20004"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "484-050": rsEmployees!FirstName = "Marianne": rsEmployees!MiddleName = "Becky": rsEmployees!LastName = "Oslin": rsEmployees!LocationCode = "WHTFLT": rsEmployees!Title = "Assistant Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "104 G St. S. E.": rsEmployees!City = "Washington": rsEmployees!State = "DC": rsEmployees!ZIPCode = "20012"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "492-081": rsEmployees!FirstName = "Jeffrey": rsEmployees!MiddleName = "Aaron": rsEmployees!LastName = "Kilborne": rsEmployees!LocationCode = "GTWMST": rsEmployees!Title = "Accounts Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "308 Capitol Blvd": rsEmployees!City = "McLean": rsEmployees!State = "VA": rsEmployees!ZIPCode = "22101"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "503-938": rsEmployees!FirstName = "Michelle": rsEmployees!MiddleName = "Deborah": rsEmployees!LastName = "Stephenson": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Head Teller": rsEmployees!Address = "2888 Gwett Richards Ave": rsEmployees!City = "Glen Burnie": rsEmployees!State = "MD": rsEmployees!ZIPCode = "21060"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "507-728": rsEmployees!FirstName = "Timothy": rsEmployees!LastName = "Wray": rsEmployees!LocationCode = "GTWMST": rsEmployees!Title = "Branch Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "720 Oak Tree Rd": rsEmployees!City = "Columbia": rsEmployees!State = "MD": rsEmployees!ZIPCode = "21044"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "533-825": rsEmployees!FirstName = "Aaron": rsEmployees!MiddleName = "Robert": rsEmployees!LastName = "Kast": rsEmployees!LocationCode = "WHTFLT": rsEmployees!Title = "Accounts Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "2991 Justine Ave": rsEmployees!City = "Rockville": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20854"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "552-884": rsEmployees!FirstName = "Joy": rsEmployees!MiddleName = "Maureen": rsEmployees!LastName = "Donovan": rsEmployees!LocationCode = "ALXJPZ": rsEmployees!Title = "Customer Accounts Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "845 Arcadia Ave. #1512": rsEmployees!City = "Alexandria": rsEmployees!State = "VA": rsEmployees!ZIPCode = "22314"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "624-825": rsEmployees!FirstName = "Luke": rsEmployees!MiddleName = "Andrew": rsEmployees!LastName = "Parkinson": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Intern": rsEmployees!Address = "4445 Blue Oak St. #6A": rsEmployees!City = "Silver Spring": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20906"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "660-026": rsEmployees!FirstName = "Lucas": rsEmployees!LastName = "Frieddle": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Cashier": rsEmployees!Address = "8252 Eleven Sons Rd": rsEmployees!City = "College Park": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20740"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "712-083":   rsEmployees!FirstName = "Wendy":   rsEmployees!MiddleName = "Noella":   rsEmployees!LastName = "Huntsmann":   rsEmployees!LocationCode = "WHTFLT":   rsEmployees!Title = "Cashier":   rsEmployees!Address = "13622 Washington Blvd":   rsEmployees!City = "Laurel":   rsEmployees!State = "MD":   rsEmployees!ZIPCode = "20707"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "722-286":   rsEmployees!FirstName = "Donald":   rsEmployees!MiddleName = "Anthony":   rsEmployees!LastName = "Fisher":   rsEmployees!LocationCode = "SLVSSL":   rsEmployees!Title = "Public Relations Manager":   rsEmployees!Address = "58 North Assault St.":   rsEmployees!City = "Takoma Park":   rsEmployees!State = "MD":   rsEmployees!ZIPCode = "20910"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "736-626": rsEmployees!FirstName = "Annabelle": rsEmployees!MiddleName = "Jeannette": rsEmployees!LastName = "Coen": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Cashier": rsEmployees!Address = "15328 Crystal St.": rsEmployees!City = "Frederick": rsEmployees!State = "MD": rsEmployees!ZIPCode = "21701"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "829-313": rsEmployees!FirstName = "Simon": rsEmployees!MiddleName = "Eddy": rsEmployees!LastName = "Michaels": rsEmployees!LocationCode = "CPKUMD": rsEmployees!Title = "Cashier": rsEmployees!Address = "1336 Philadelphia St.": rsEmployees!City = "Baltimore": rsEmployees!State = "MD": rsEmployees!ZIPCode = "21206"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "927-395": rsEmployees!FirstName = "Luisa": rsEmployees!MiddleName = "Judie": rsEmployees!LastName = "Vanecek": rsEmployees!LocationCode = "GTWMST": rsEmployees!Title = "Cashier": rsEmployees!Address = "8082 16th St N.W.": rsEmployees!City = "Washington": rsEmployees!State = "DC": rsEmployees!ZIPCode = "20002"
    rsEmployees.Update
    
    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "928-495": rsEmployees!FirstName = "Ryan": rsEmployees!LastName = "Duck": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "General Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "10244 Webster Rd": rsEmployees!City = "Rockville": rsEmployees!State = "MD": rsEmployees!ZIPCode = "20854"
    rsEmployees.Update

    rsEmployees.AddNew
    rsEmployees!EmployeeNumber = "952-846": rsEmployees!FirstName = "John": rsEmployees!LastName = "Possemato": rsEmployees!LocationCode = "SLVSSL": rsEmployees!Title = "Assistant Manager": rsEmployees!CanCreateNewAccount = True: rsEmployees!Address = "8254 12th St. N.E.": rsEmployees!City = "Washington": rsEmployees!State = "DC": rsEmployees!ZIPCode = "20004"
    rsEmployees.Update

    rsEmployees.Close
    dbKoloBank.Close
End Sub

Private Sub DefineSomePrimaryAccountHolders()
    Dim dbKoloBank As Database
    Dim rsCustomers As Recordset
    
    Set dbKoloBank = CurrentDb
    Set rsCustomers = dbKoloBank.OpenRecordset("Customers", _
	                	               RecordsetTypeEnum.dbOpenTable, _
        	                	       RecordsetOptionEnum.dbDenyRead, _
	                	               LockTypeEnum.dbOptimistic)
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "94-477085-03"
    rsCustomers!EmployeeNumber = "507-728"
    rsCustomers!DateCreated = #1/6/2017#
    rsCustomers!AccountType = "Saving"
    rsCustomers!FirstName = "Ismail"
    rsCustomers!LastName = "Zorbah"
    rsCustomers!Address = "8252 Eleven Sons Rd"
    rsCustomers!City = "Arlington"
    rsCustomers!State = "VA"
    rsCustomers!ZIPCode = "20150"
    rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "20-304042-49": rsCustomers!EmployeeNumber = "952-846": rsCustomers!DateCreated = #1/6/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "Ophellie":         rsCustomers!LastName = "Wyman": rsCustomers!Address = "104 G St. S. E.": rsCustomers!City = "Washington": rsCustomers!State = "DC": rsCustomers!ZIPCode = "20005": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "68-304605-84": rsCustomers!EmployeeNumber = "294-075": rsCustomers!DateCreated = #1/8/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "Howie": rsCustomers!MiddleName = "Horace": rsCustomers!LastName = "Fallace": rsCustomers!Address = "9337 Cachet St": rsCustomers!City = "Arlington": rsCustomers!State = "VA": rsCustomers!ZIPCode = "20170": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "27-314257-84": rsCustomers!EmployeeNumber = "484-050": rsCustomers!DateCreated = #1/12/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "Robert": rsCustomers!MiddleName = "Daniel": rsCustomers!LastName = "Luner": rsCustomers!Address = "802 Lilas Ave": rsCustomers!City = "Baltimore": rsCustomers!State = "MD": rsCustomers!ZIPCode = "21208": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "28-370082-80": rsCustomers!EmployeeNumber = "461-842": rsCustomers!DateCreated = #1/12/2017#: rsCustomers!AccountType = "Saving": rsCustomers!FirstName = "James": rsCustomers!MiddleName = "Carlton": rsCustomers!LastName = "Brokeridge": rsCustomers!Address = "1022 Arlington Rd": rsCustomers!City = "Arlington": rsCustomers!State = "VA": rsCustomers!ZIPCode = "20164": rsCustomers!AccountStatus = "Suspended"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "38-402217-59": rsCustomers!EmployeeNumber = "507-728": rsCustomers!DateCreated = #1/12/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "James":        rsCustomers!LastName = "Norris": rsCustomers!Address = "1277 Cecil Maurice Ave": rsCustomers!City = "Chevy Chase": rsCustomers!State = "MD": rsCustomers!ZIPCode = "20870": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "68-640304-15": rsCustomers!EmployeeNumber = "492-081": rsCustomers!DateCreated = #1/16/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "Eldridge":        rsCustomers!LastName = "Powers": rsCustomers!Address = "273 S. Independence Ave": rsCustomers!City = "Alexandria": rsCustomers!State = "VA": rsCustomers!ZIPCode = "20185": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "30-514090-26": rsCustomers!EmployeeNumber = "461-842": rsCustomers!DateCreated = #1/18/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "Cherrine": rsCustomers!MiddleName = "Leonie": rsCustomers!LastName = "Horvath": rsCustomers!Address = "720 Oak Tree Rd": rsCustomers!City = "Laurel": rsCustomers!State = "MD": rsCustomers!ZIPCode = "20707": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "40-460582-63": rsCustomers!EmployeeNumber = "484-050": rsCustomers!DateCreated = #1/25/2017#: rsCustomers!AccountType = "CD ": rsCustomers!FirstName = "Mellinda":       rsCustomers!LastName = "Bridges": rsCustomers!Address = "1336 Philadelphia St": rsCustomers!City = "Takoma Park ": rsCustomers!State = "MD": rsCustomers!ZIPCode = "20908": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.AddNew
    rsCustomers!AccountNumber = "29-425806-46": rsCustomers!EmployeeNumber = "461-842": rsCustomers!DateCreated = #1/25/2017#: rsCustomers!AccountType = "Checking": rsCustomers!FirstName = "Albert": rsCustomers!MiddleName = "Sonny": rsCustomers!LastName = "Odonnell": rsCustomers!Address = "12 16th St. S. W.": rsCustomers!City = "Washington": rsCustomers!ZIPCode = "20008": rsCustomers!AccountStatus = "Active"
    rsCustomers.Update
    
    rsCustomers.Close
    dbKoloBank.Close
End Sub

Private Sub Command0_Click()
    HireSomeEmployees
    MsgBox "Many employees have been hired and sent to their right locations.", _
           vbOKOnly Or vbInformation, "Kolo Bank"
           
    DefineSomePrimaryAccountHolders
    MsgBox "A few records for new account holders have been created.", _
           vbOKOnly Or vbInformation, "Kolo Bank"
End Sub
  13. Return to Microsoft Access and switch the form to Form View
  14. Click the button on the form
  15. Close the form
  16. When asked whether you want to save, click No
  17. On the Ribbon, click Create and click Form Design
  18. In the Property Sheet, change the following characteristics:
    Caption: Kolo Bank - Charge Against Account
    Auto Center: Yes
    Record Selectors: No
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
  19. Right-click the form and click Form Header/Footer
  20. Save the form as Charge Against Account
  21. Complete the design of the form as follows:
    In the below list of controls, when the Text Box is indicated, the Caption is for the accompanying label and the Name is for the text box.
    All text boxes have the Border Color as Black (or Text Black)
    We applied the Century Schoolbook font with size 11 to all labels, text boxes, and the combo box
    The top yellow title uses the Elephant font with size 24

    Kolo Bank - Charge Against Account Deposit

    Control Caption Name Other Properties
    Label Label Kolo Bank   Font Color: Yellow
    Line Line     Border Color: #FFC20E
    Label Label Charge Against Account   Font Color: White
    Label Label Charge Performed By   Back Color: #727272
    Text Box Text Box Employee #: txtEmployeeNumber  
    Line Line     Border Width: 2 pt
    Text Box Text Box Charge Date: txtChargeDate Format: Short Date
    Text Box Text Box Charge Time: txtChargeTime Format: Long Time
    Text Box Text Box Location Code: txtLocationCode  
    Label Label   Performed For Back Color: #727272
    Text Box Text Box Account #: txtAccountNumber  
    Text Box Text Box Charge Reason: cbxChargesReasons  
    Text Box Text Box Currency Type: cbxCurrenciesTypes  
    Text Box Text Box Previous Balance: txtPreviousBalance  
    Text Box Text Box Amount Charged: txtAmountCharged  
    Text Box Text Box New Balance: txtNewBalance  
    Text Box Text Box Notes: txtNotes Special Effect: Shadowed
    Scroll Bars: Vertical
    Button Button Submit Deposit btnSubmitDeposit  
    Button Button Close cmdClose  
  22. Close the form
  23. When asked whether you want to save, click Yes
  24. In the Navigation Pane, right-click Charge Against Account and click Copy
  25. Right-click an unoccupied area of the Navigation Pane and click Paste
  26. Type Money Withdrawal as the name of the new form and click OK
  27. In the Navigation Pane, right-click Charge Against Account and click Copy
  28. Right-click an unoccupied area of the Navigation Pane and click Paste
  29. Type Account Deposit as the name of the new form and click OK
  30. In the Navigation Pane, right-click Money Withdrawal and click Design View
  31. Click the cbxChargeReasons text box and press Delete
  32. Change (only) the following characteristics:

    Kolo Bank - New Withdrawal

    Control Caption Name
    Label Label Money Withdrawal  
    Label Label Withdrawal Performed By  
    Text Box Text Box Withdrawal Date: txtWithdrawalDate
    Text Box Text Box Withdrawal Time: txtWithdrawalTime
    Text Box Text Box Amount Withdrawn: txtAmountWithdrawn
    Button Button Submit Withdrawal btnSubmitWithdrawal
  33. Close the form
  34. When asked whether you want to save, click Yes
  35. In the Navigation Pane, right-click Account Deposit and click Design View
  36. Click the cbxChargeReasons text box and press Delete
  37. Change (only) the following characteristics:

    Kolo Bank - New Charge

    Control Caption Name
    Label Label Account Deposit  
    Label Label Deposit Performed By  
    Text Box Text Box Deposit Date: txDepositDate
    Text Box Text Box Deposit Time: txtDepositTime
    Text Box Text Box  Charge Reason  cbxChargesReasons
    Text Box Text Box Amount Deposited: txtAmountDeposit
    Button Button Submit Deposit cmdSubmitDeposit
  38. Save the form
  39. On the form, click the txtAmountDeposited text box
  40. In the Property Sheet, click the Event tab and double-click On Lost Focus
  41. Click its ellispsis button and implement the event as follows: 
    Private Sub txtAmountDeposited_LostFocus()
    Dim amountDeposited As Double
    Dim previousBalance As Double, currentBalance As Double

    amountDeposited = CDbl(Nz(txtAmountDeposited))
    previousBalance = CDbl(Nz(txtPreviousBalance))
    
    currentBalance = amountDeposited + previousBalance
    
    txtCurrentBalance = FormatNumber(currentBalance)
End Sub
  42. Close Microsoft Visual Basic and return to Microsoft Access
  43. In the Navigation Pane, right-click the Money Withdrawal form and click Design View
  44. On the form, click the txtAmountWithdrawn text box
  45. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
  46. Implement the event as follows: 
    Private Sub txtAmountWithdrawn_LostFocus()
    Dim amountWithdrawn As Double
    Dim previousBalance As Double, currentBalance As Double

    amountWithdrawn = CDbl(Nz(txtAmountWithdrawn))
    previousBalance = CDbl(Nz(txtPreviousBalance))
    
    currentBalance = previousBalance - amountWithdrawn
    
    txtNewBalance = FormatNumber(currentBalance)
End Sub
  47. Close Microsoft Visual Basic and return to Microsoft Access
  48. In the Navigation Pane, right-click the Charge Against Account form and click Design View
  49. On the form, click the txtAmountCharged text box
  50. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
  51. Implement the event as follows: 
    Private Sub txtAmountCharged_LostFocus()
    Dim amountCharged As Double
    Dim previousBalance As Double, currentBalance As Double

    amountCharged = CDbl(Nz(txtAmountCharged))
    previousBalance = CDbl(Nz(txtPreviousBalance))
    
    currentBalance = previousBalance - amountCharged
    
    txtNewBalance = FormatNumber(currentBalance)
End Sub
  52. Close Microsoft Visual Basic and return to Microsoft Access
  53. Close the forms
  54. When asked whether you want to save, click Yes

The Number of Records of a Record Set

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

RecordsetClone.RecordCount

Record Navigation in a Record Set

Besides creating a record set, probably the most common operation you perform on a record set is to navigate among records. The DAO.Recordset class supports the operations throught various method:

When navigating among records, you must avoid moving below the first record or above the last record. To assist you with this, the DAO.Recordset class is equipped with the BOF() and the EOF() methods. The BOF() method is used to check whether navigation is trying to reach below the first record. The EOF() method is used to check whether navigation is getting higher than the last record. Both methods return a Boolean value.

Record Maintenance

Locating a Value

There are various ways you can find a record in a record set. You can use a loop. This can be done as follows:

Private Sub cmdUpdate_Click()
    Dim fldEmployee As DAO.Field
    Dim curDatabase As DAO.Database
    Dim rstEmployees As DAO.Recordset
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees", _
                                                 RecordsetTypeEnum.dbOpenTable, _
                                                 RecordsetOptionEnum.dbReadOnly, _
                                                 LockTypeEnum.dbPessimistic)
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be edited has been located
                        
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
End Sub

Practical Learning: Locating a Record

  1. On the Ribbon, click File and click Open
  2. In the list of files, click ESCAPE from Lesson 33
  3. In the Navigation Pane, right-click New Customer Bill and click Design View
  4. Add a button to the right side of the Account # text box as follows:

    ESCAPE - New Customer Bill

    Control Name Caption
    Button Button cmdFindCustomer Find Customer
  5. On the form, right-click the Find Customer button and click Build Event...
  6. In the Choose Builder dialog box, click Code Builder and click OK
  7. Implement the event as follows: 
    Private Sub cmdFindCustomer_Click()
    Dim fldCustomer As Field
    Dim foundCustomer As Boolean
    Dim rsCustomers As Recordset
    Dim dbCommunication As Database
    
    If IsNull(txtAccountNumber) Then
        MsgBox "You must provide an account number to locate a customer.", _
               vbOKCancel Or vbInformation, "Eastern Shore Communications Company"
        Exit Sub
    End If
    
    
    foundCustomer = False
    Set dbCommunication = Application.CurrentDb
    Set rsCustomers = dbCommunication.OpenRecordset("Customers")
    
    rsCustomers.MoveFirst
    
    Do
        For Each fldCustomer In rsCustomers.Fields
            If (fldCustomer.Name = "AccountNumber") And (fldCustomer.Value = txtAccountNumber) Then
                txtCustomerName = rsCustomers("FirstName").Value & " " & rsCustomers("LastName").Value
                txtAddress = rsCustomers("Address").Value
                txtCity = rsCustomers("City").Value
                txtCounty = rsCustomers("County").Value
                txtState = rsCustomers("State").Value
                txtZIPCode = rsCustomers("ZIPCode").Value
                txtCableTVBasicFee = rsCustomers("CableTVBasicFee").Value
                chkIncludesDVRService = rsCustomers("UsesDVRService").Value
                chkIncludesSportsPackage = rsCustomers("UsesSportsPackage").Value
                txtInternetBasicFee = rsCustomers("InternetBasicFee").Value
                chkIncludesModemLease = rsCustomers("ProvidesOwnModem").Value
                txtInternetSpeedApplied = IIf(CInt(rsCustomers("InternetSpeedApplied").Value) = 1, "Starter", IIf(CInt(rsCustomers("InternetSpeedApplied").Value) = 2, "Blast", "Extreme"))
                txtInternetSpeedFee = IIf(CInt(rsCustomers("InternetSpeedApplied").Value) = 1, "55.95", IIf(CInt(rsCustomers("InternetSpeedApplied").Value) = 2, "74.65", "115.25"))
                
                chkIncludesSportsPackage_Click
                chkIncludesDVRService_Click
                chkIncludesModemLease_Click
                
                foundCustomer = True
                Exit For
            End If
        Next
        
        rsCustomers.MoveNext
    Loop While Not rsCustomers.EOF
    
    If foundCustomer = True Then
        cmdEvaluateCustomerBill_Click
    Else
        MsgBox "There is no customer with the account number you provided.", _
               vbOKCancel Or vbInformation, "Eastern Shore Communications Company"
        ResetForm
    End If
End Sub
  8. In the Object combo box, select cmdClose
  9. Implement the event as follows: 
    Private Sub cmdClose_Click()
    DoCmd.Close
End Sub
  10. Close Microsoft Visual Basic and return to Microsoft Access
  11. Save and close the form
  12. On the Ribbon, click File and click Open
  13. In the list of files, click WattsALoan from the previous lesson

Editing a Value in a Recordset

To let you edit a record, the Recordset class includes a method named Edit. First call it, then access each desired field and assign a value to it. After doing this, call the Update() method of the record set. This can be done as follows:

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

Practical Learning: Locating and Editing a Record

  1. In the Navigation Pane, right-click the TimeSheet form and click Design View
  2. On the form, right-click the Submit Time Sheet button and click Build Event...
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows: 
    Private Sub cmdSubmitTimeSheet_Click()
    Dim fldTimeSheet As Field
    Dim dbWattsALoan As Database
    Dim rsTimeSheets As Recordset
    Dim timeSheetFound As Boolean
    
    If IsNull(txtEmployeeNumber) Then
        MsgBox "Please enter the employee number.", _
               vbOKOnly, "Watts A Loan - New Time Sheet"
        Exit Sub
    End If
    
    If Not IsDate(txtStartDate) Then
        MsgBox "Please enter a valid start date for the current time sheet.", _
               vbOKOnly, "Watts A Loan - New Time Sheet"
        Exit Sub
    End If
    
    timeSheetFound = False
    Set dbWattsALoan = CurrentDb
    Set rsTimeSheets = dbWattsALoan.OpenRecordset("TimeSheets", RecordsetTypeEnum.dbOpenTable)

    If rsTimeSheets.RecordCount > 0 Then
        Do Until rsTimeSheets.EOF
            For Each fldTimeSheet In rsTimeSheets.Fields
                If fldTimeSheet.Name = "TimeSheetID" Then
                    If fldTimeSheet.Value = Me.txtTimeSheetNumber Then
                        rsTimeSheets.Edit
                        rsTimeSheets("Week1Monday").Value = CDbl(Nz(txtWeek1Monday))
                        rsTimeSheets("Week1Tuesday").Value = CDbl(Nz(txtWeek1Tuesday))
                        rsTimeSheets("Week1Wednesday").Value = CDbl(Nz(txtWeek1Wednesday))
                        rsTimeSheets("Week1Thursday").Value = CDbl(Nz(txtWeek1Thursday))
                        rsTimeSheets("Week1Friday").Value = CDbl(Nz(txtWeek1Friday))
                        rsTimeSheets("Week1Saturday").Value = CDbl(Nz(txtWeek1Saturday))
                        rsTimeSheets("Week1Sunday").Value = CDbl(Nz(txtWeek1Sunday))
                        
                        rsTimeSheets("Week2Monday").Value = CDbl(Nz(txtWeek2Monday))
                        rsTimeSheets("Week2Tuesday").Value = CDbl(Nz(txtWeek2Tuesday))
                        rsTimeSheets("Week2Wednesday").Value = CDbl(Nz(txtWeek2Wednesday))
                        rsTimeSheets("Week2Thursday").Value = CDbl(Nz(txtWeek2Thursday))
                        rsTimeSheets("Week2Friday").Value = CDbl(Nz(txtWeek2Friday))
                        rsTimeSheets("Week2Saturday").Value = CDbl(Nz(txtWeek2Saturday))
                        rsTimeSheets("Week2Sunday").Value = CDbl(Nz(txtWeek2Sunday))
                        rsTimeSheets.Update
                        
                        MsgBox "The time sheet has been updated.", _
                               vbOKOnly, "Watts A Loan - Time Sheet Edition"
                        
                        timeSheetFound = True
                    End If
                End If
            Next
            rsTimeSheets.MoveNext
        Loop
    End If
    
    If timeSheetFound = False Then
        rsTimeSheets.AddNew
        rsTimeSheets("EmployeeNumber").Value = txtEmployeeNumber
        rsTimeSheets("StartDate").Value = CDate(txtStartDate)
        
        rsTimeSheets("Week1Monday").Value = CDbl(Nz(txtWeek1Monday))
        rsTimeSheets("Week1Tuesday").Value = CDbl(Nz(txtWeek1Tuesday))
        rsTimeSheets("Week1Wednesday").Value = CDbl(Nz(txtWeek1Wednesday))
        rsTimeSheets("Week1Thursday").Value = CDbl(Nz(txtWeek1Thursday))
        rsTimeSheets("Week1Friday").Value = CDbl(Nz(txtWeek1Friday))
        rsTimeSheets("Week1Saturday").Value = CDbl(Nz(txtWeek1Saturday))
        rsTimeSheets("Week1Sunday").Value = CDbl(Nz(txtWeek1Sunday))
        
        rsTimeSheets("Week2Monday").Value = CDbl(Nz(txtWeek2Monday))
        rsTimeSheets("Week2Tuesday").Value = CDbl(Nz(txtWeek2Tuesday))
        rsTimeSheets("Week2Wednesday").Value = CDbl(Nz(txtWeek2Wednesday))
        rsTimeSheets("Week2Thursday").Value = CDbl(Nz(txtWeek2Thursday))
        rsTimeSheets("Week2Friday").Value = CDbl(Nz(txtWeek2Friday))
        rsTimeSheets("Week2Saturday").Value = CDbl(Nz(txtWeek2Saturday))
        rsTimeSheets("Week2Sunday").Value = CDbl(Nz(txtWeek2Sunday))
        rsTimeSheets.Update
        
        MsgBox "The new time sheet has been created.", _
               vbOKOnly, "Watts A Loan - New Time Sheet"
    End If

    Set rsTimeSheets = Nothing
    Set dbWattsALoan = Nothing
    
    DoCmd.Close
End Sub
  5. Return to Microsoft Access and close the form
  6. When asked whether you want to save, click Yes

Other Techniques of Accessing the Fields of a Record Set

So far, to access a field in a record set, we passed its indexed property as in rsTimeSheets("Week1Monday"). In both the MAOL and DAO, You can access a field by applying its name to the record set variable, both separated by the ! operator. Here is an example:

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("Something").Value = ...
    rstEmployees!FieldName =  ...
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

If the name is in more than one word, include it between [ and ]. Here is an example:

Private Sub cmdDataEntry_Click()
    Dim curDatabase As Object
    Dim rstEmployees As Object
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees")
    
    rstEmployees.AddNew
    rstEmployees("Something").Value = ...
    rstEmployees!FieldName =  ...
    rstEmployees![Area Code] =  ...
    
    Set rstEmployees = Nothing
    Set curDatabase = Nothing
End Sub

Practical Learning: Accessing the Fields of a Record Set

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Whirl Water Shine Lesson 29
  3. In the Navigation Pane, right-click Bill Preparation and click Design View
  4. On the Form, right-click the Submit button and click Build Event...
  5. In the Choose Builder dialog box, double-click Code Builder
  6. Implement the event as follows: 
    Private Sub cmdSubmit_Click()
    Dim rsWaterBills As Recordset
    Dim dbWaterCompany As Database
    
    Set dbWaterCompany = CurrentDb
    Set rsWaterBills = dbWaterCompany.OpenRecordset("WaterBills", _
                                                  RecordsetTypeEnum.dbOpenTable, _
                                                RecordsetOptionEnum.dbConsistent, _
                                                    LockTypeEnum.dbPessimistic)
    
    If IsNull(txtAccountNumber) Or _
       IsNull(txtServiceFromDate) Or _
       IsNull(txtServiceToDate) Or _
       IsNull(txtMeterReadingStart) Or _
       IsNull(txtMeterReadingEnd) Then
        Exit Sub
    End If
    
    rsWaterBills.AddNew
    rsWaterBills!AccountNumber = txtAccountNumber
    rsWaterBills!ServiceFromDate.Value = CDate(txtServiceFromDate)
    rsWaterBills!ServiceToDate.Value = CDate(txtServiceToDate)
    rsWaterBills![NumberOfDays] = CInt(txtNumberOfDays)
    rsWaterBills![MeterReadingStart] = CDbl(Nz(txtMeterReadingStart))
    rsWaterBills![MeterReadingEnd] = CDbl(Nz(txtMeterReadingEnd))
    rsWaterBills![TotalHCF] = CDbl(Nz(txtTotalHCF))
    rsWaterBills![TotalGallons] = CLng(Nz(txtTotalGallons))
    rsWaterBills![First15HCF] = CDbl(Nz(txtFirst15HCF))
    rsWaterBills![Next10HCF] = CDbl(Nz(txtNext10HCF))
    rsWaterBills!RemainingHCF = CDbl(Nz(txtRemainingHCF))
    rsWaterBills!WaterUsageCharge = CDbl(Nz(txtWaterUsageCharge))
    rsWaterBills!SewerCharge = CDbl(Nz(txtSewerCharge))
    rsWaterBills!StormCharge = CDbl(Nz(txtStormCharge))
    rsWaterBills![TotalCharges] = CDbl(Nz(txtTotalCharges))
    rsWaterBills![StateTaxes] = CDbl(Nz(txtStateTaxes))
    rsWaterBills![CountyTaxes] = CDbl(Nz(txtCountyTaxes))
    rsWaterBills![PaymentDueDate] = CDate(txtPaymentDueDate)
    rsWaterBills![AmountDue] = CDbl(Nz(txtAmountDue))
    rsWaterBills![LatePaymentDate] = CDate(txtLatePaymentDate)
    rsWaterBills![LatePaymentAmount] = CDbl(Nz(txtLatePaymentAmount))
    rsWaterBills.Update

    rsWaterBills.Close
    dbWaterCompany.Close
    
    MsgBox "The water has been prepared and approved.", _
           vbOKOnly Or vbInformation, _
           "Whirl Water Shine Corporation"
    
    DoCmd.Close
End Sub
  7. Close Microsoft Visual Basic and return to Microsoft Access
  8. Close the form
  9. When asked whether you want to save the form, click Yes

Deleting a Record in DAO

To support the ability to delete a record, the Recordset class is equipped with a method named Delete. Here is an example of calling:

Private Sub cmdDeleteRecord_Click()
    Dim fldEmployee As DAO.Field
    Dim curDatabase As DAO.Database
    Dim rstEmployees As DAO.Recordset
    
    Set curDatabase = CurrentDb
    Set rstEmployees = curDatabase.OpenRecordset("Employees", 
                                                 RecordsetTypeEnum.dbOpenTable, _
                                                 RecordsetOptionEnum.dbAppendOnly, _
                                                 LockTypeEnum.dbOptimistic)
    
    With rstEmployees
        Do Until .EOF
            For Each fldEmployee In .Fields
                If fldEmployee.Name = "EmployeeID" Then
                    If fldEmployee.Value = CInt(txtEmployeeID) Then
                        ' The record to be deleted has been found
                        .Delete
                        Exit For
                    End If
                End If
            Next
            .MoveNext
        Loop
    End With
End Sub

Data Analysis With a Record Set

Introduction

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

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

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

Controlling a Sub-Form Using a Record Set

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

Practical Learning: Testing a Database

  1. On the Ribbon, click File and click Open, in the list of files, click ESCAPE used earlier
  2. In the Navigation Pane, double-click New Customer Account
  3. Create the following records (enter the values and click the Create Customer Account button each time)

    Account #First NameLast NameAddressCity County State ZIP Code Cable TV Basic Fee Uses DVR Service Uses Sports Package Internet Basic Fee Provides Own Modem Internet Speed Applied
    5805864JosephMuir2795 Texas RdLaurel Prince George MD 20742 24.95 Checked Checked 64.50   1
    9475117ScottHaughley11720 Esplanade CrtWoolsey Fairfax VA 20169 26.85     64.50 Checked 1
    2858070KarenRessner1088 Barnley RdWashington   DC 20004 22.70     115.40   3
    5070686CatherineAlvarez11148 Kemp Know Mills RdReiterstown Baltimore MD 21136 24.95     82.20 Checked 2
    9686951AndrewTiggs884 Zephyr StWarsaw Prince William VA 22572 25.25 Checked Checked 64.50 Checked 1
    4028408JosephMarlens10328 Wellington AveSilver Spring Montgomery MD 20906 24.95      82.20   2
    7977829PhilippeNorte4148 Millfoy RdRock Mills Culpeper VA 22716 27.30 Checked   64.50 Checked 1
    2408504PascaleDonfack4003 Addison StrWashington  DC 20008 20.15     115.40   3
    2495708MelodieWright8266 Georgina RdAlexandria  VA 22314 25.75   Checked 82.20 Checked 2
    9370241FrederickBirds7002 Knowles RdLaurel Anne Arundel MD 20724 27.50     75.00   3
    7024059HarrySons8420 Givensons AveHyattsville Prince George MD 20782 24.95 Checked Checked 64.50   1
    8508620WilliamGoodson1077 Elm Rebuque AveSilver Spring Montgomery MD 20910 24.95 Checked Checked 64.50   1
  4. In the Navigation Pane, double-click New Customer Bill
  5. Create bills for the following account numbers (type the account number, press Tab, and click Submit New Bill every time): 5070686, 7977829, 2495708, 9370241, 8508620, 5805864, 4028408, 9475117,  2408504, 2858070, 9686951, and 7024059
  6. Close the form
  7. Close Microsoft Access
Previous Copyright © 2005-2022, FunctionX, Inc. Next