Home

Relationships and Data Integrity
 

Relational Databases
 

Introduction

When performing data entry, a user's job should be as easy and smooth as possible. One of the aspects that take care of this is the flow of information from one list to another. In other words, data stored in one list can be made available to another list so the user not only doesn't have to enter the same piece of information in different lists but also the should be able to access, from one list or table, data stored in another table.

To allow the information to flow from one list to another, there should (must) exist a relationship between both tables. A relationship is made possible through a type of link from one table to another. This is the essence of relational databases.
 

Practical Learning Practical Learning: Introducing Relational Databases
  • Start Microsoft Access and open the Yugo National Bank database you started in the previous lesson

The Key to Hold a Relationship

Once again, when performing data entry, the records that the user creates must be easily distinguishable. This means that each record must have some uniqueness with regards to the other records. To make this possible, you can create a field or column that sets apart each record. To do this, you can isolate a column that you know will hold unique values from one record to another. For example, if you were creating a database for the office that releases identity cards or driver's licenses, you certainly would like to make sure that two people don't have the same number.

The Primary Key of a Table
 

Introduction

The purpose of the primary key is to keep records distinct from one another. When performing data entry, you can let the user manage this and you can use some techniques to exercise a certain level of control. The data type of a primary key can be almost type, certainly a string or a number. For example, you can use an employee number or else. The most important rule is that each record must have a unique value to distinguish it from another record. In most cases, if you let the user specify the value(s) of the primary key, this can be overwhelming. Instead of going through this configuration process, you can rely on Microsoft Access to automatically create a unique value for each record of a particular column.

Creating a Primary Key in the Table Design View

A column that holds unique values that can differentiate one record from another is called a primary key. In most cases, you can create one column that would serve as the primary key of the table. To create a primary key, if working from the Design View of the table, you can right-click the column that will play this role and click Primary Key:

You can also click anywhere in the field and click the Primary Key button on the Table Design toolbar. After doing this, a picture with a key would appear on the left box of the column name. In some cases, you can use more than one column, that is, a combination of columns, to serve as the primary key. To do this, click the gray box on the left of one of the desired columns, press and hold Ctrl (or Shift). Click the gray box of the other column(s) to select. To make this combination the primary key, right-click the selection and click Primary key. Each of the selected columns would now display a pictured key on its gray box:

Based on a habit that follows a certain logic, the name of a column that holds the primary key usually ends with ID.

Because a primary key is very important and common in a relational database, in Microsoft Access, if you don't specify a primary key and start saving the table, a warning would inform you that your table doesn't have a primary key.

Practical Learning Practical Learning: Creating a Primary Key
  1. In the Tables section of the Database window, Click the New button
  2. In the New Table dialog box, double-click Design View
  3. Specify the first Field Name as WithdrawalTypeID
  4. Set its Data Type to AutoNumber
  5. To make it the Primary Key, while the field is still selected, on the Table Design toolbar, click the Primary Key button Primary Key and press F6
  6. Click Caption and type Withdrawal Type ID
  7. Under WithdrawalTypeID, type WithdrawalType
  8. In the lower section, click Caption and type Withdrawal Type
  9. Press the down arrow key and type Description
  10. Set the Description's Data Type to Memo
  11. Save the table as WithdrawalTypes and switch it to Datasheet View
  12. Close the table and, in the Tables section of the Database window, click WithdrawalTypes to make sure it is selected
  13. On the Database toolbar, click the arrow of the New Object button and click AutoForm
  14. Save the form with the default name
  15. Right-click the form and click Form Header/Footer
  16. Use the Command Button Wizard and add a Command Button to the right side of the Form Footer section
  17. Set the button's caption to Close and its name to cmdClose
  18. Adjust the design of the form as you see fit. Here is an example: 
     
  19. Enter a few records as follows:
     
    Withdrawal Type ID Withdrawal Type Description
    1 Cashier Money cashed at a bank
    2 ATM Automated Teller Machine
    3 Check Check issued by the customer or transaction authorized by the customer to another institution
    4 Drive-In Money cashed by the customer through the drive-in window
    5 Transfer Money transfer initiated by the customer
  20. Save and close the new form
  21. To create a new report, on the main menu, click Insert -> Report
  22. In the New Report dialog box, click AutoReport: Columnar
  23. In the combo box, select WithdrawalTypes and click OK
  24. To save the report, on the main menu, click File -> Save
  25. Accept the suggested name of the report and click OK
  26. Close the Report
  27. When asked whether you want to save it, click Yes
  28. To create a new report, on the main menu, click Insert -> Report
  29. In the New Report dialog box, click Report Wizard

The Data Type of a Primary Key

If you are creating a table in the Design View, to get the primary key column to automatically generate a unique incremental number, you can set its data type as AutoNumber.

If you are programmatically creating the table using SQL, in Lesson 14, we saw that you could apply the COUNTER or the AUTOINCREMENT data type to a column if you want it to generate an auto-incrementing numeric value.

 

Creating a Primary Key From the Table Wizard

The table Design View is the most common and probably the best place to create a primary key for a table. Still, you can use the table wizard to create a primary key. You make have noticed that the first column in the Sample Fields list of each Sample Table has a name that ends with ID:

When creating a table, if you select such a column and start creating the table, in the second page, the wizard would suggest that you let it create a primary key:

If you agree to let the wizard create the primary key, it would use the first ID column that was added to the table.

Practical Learning Practical Learning: Creating a Table Using the Table Wizard
  1. In the Tables section of the Database window, double-click Create a Table By Using Wizard
  2. In the Sample Tables list, click Employees
  3. In the Sample Fields, double-click the following fields: EmployeeID, EmployeeNumber, FirstName, LastName, Title, and Salary
  4. Click Next
  5. Accept the suggested name of the table as Employees and notice that a primary key will be created. Click Finish
  6. Right-click the title bar of the table and click Design View
  7. Click the empty field under Salary and type CanCreateNewAccount
  8. Set its Data Type to Yes/No and its Caption to Can Create New Account?
  9. In the upper section of the window, right-click Title and click Insert Rows
  10. Click the new empty field, type FullName and press F6
  11. Set the Field Size to 80 and its Caption to Full Name
  12. Save and close the table
  13. In the Tables section of the Database window, make sure the Employees table is selected
    On the Database toolbar, click the New Object: AutoForm button
  14. On the Form View toolbar, click the Save button
  15. Accept the suggested name of the form as Employees and click OK
  16. Right-click the title bar of the form and click Form  Design
  17. Double-click the FirstName text box to display its Properties window
  18. In the All tab of the Properties window, double-click On Lost Focus
  19. Click its Build button and implement the event as follows:
      
    Private Sub FirstName_LostFocus()
    On Error GoTo FirstName_Error
    
    Dim strFirstName As String
    Dim strLastName As String
    Dim strFullName As String
    
    strFirstName = [FirstName]
    strLastName = [LastName]
    
    If IsNull(strFirstName) Then
        strFullName = strLastName
    Else
        strFullName = strLastName & ", " & strFirstName
    End If
    
    [FullName] = strFullName
    Exit Sub
    
FirstName_Error:
    If Err.Number = 94 Then
        MsgBox "Make sure you provide a name for the employee"
    End If
End Sub
  20. Return to the form and click the LastName text box
  21. In the All tab of the Properties window, double-click On Lost Focus and click its Build button
  22. Implement the event as follows:
      
    Private Sub LastName_LostFocus()
    On Error GoTo FirstName_Error
    
    Dim strFirstName As String
    Dim strLastName As String
    Dim strFullName As String
    
    strFirstName = [FirstName]
    strLastName = [LastName]
    
    If IsNull(strFirstName) Then
        strFullName = strLastName
    Else
        strFullName = strLastName & ", " & strFirstName
    End If
    
    [FullName] = strFullName
    Exit Sub
    
FirstName_Error:
    If Err.Number = 94 Then
        MsgBox "Make sure you provide a name for the employee"
    End If
End Sub
  23. Return to the form and click FullName
  24. Set its Tab Stop to No
  25. Change the following properties for the EmployeeID text box:
    Enabled: No
    Locked: Yes
    Special Effect: Chiseled
  26. Complete the design the form as follows:
     
  27. Create a few records as follows:
     
    Employee ID Employee Number First Name Last Name Title Salary Can Create New Account?
    1 BM-0082-H2 Matt Yuen Head Cashier $22.82 Checked
    2 DX-6288-K4 Catherine Marconi Customer Account Manager $22.55 Checked
    3 FF-2799-G2 Leonie Ankoma Cashier $14.88  
    4 FH-1984-K2 Sylvie Young Regional Manager $16.22 Checked
    5 FO-2784-G0 Andy Holland Assistant Manager $24.12 Checked
    6 GG-6626-D3 Lienev Zbrnitz Cashier $15.75  
    7 GT-4825-L2 Paulin Santiago Intern $16.35  
    8 HD-3938-F4 Plant Waste Head Teller $16.75  
    9 KD-8230-H1 Steven Chang Accountant $16.15  
    10 KD-9377-H6 Abedi Kombo Shift Programmer $10.56  
    11 KS-1114-Y2 Samuel McCain Cashier $15.25  
    12 LS-9293-L3 Kirsten Roberts Cashier $18.05  
    13 MD-2286-F2 William Fake-Eye Public Relations $16.32  
    14 OR-0026-Z6 Roger Lamy Cashier $10.24  
    15 PC-2777-F8 Ada Zeran Administrative Assistant $15.48  
    16 PL-2783-G7 Milicien Drudge Cashier $18.34  
    17 TL-3825-G4 Aaron Kast Accounts Manager $12.34 Checked
    18 TR-7728-G5 Antoine Lourde Regional Assistant Manager $15.62 Checked
    19 UD-4050-X2 Lorraine Kirkland Assistant Manager $12.86 Checked
    20 WE-5552-F8 Jeffrey Salomons Cashier $24.52  
  28. Close the Employees form
  29. When asked whether you want to save it, click Yes
  30. In the Database window, click Tables and click Employees
  31. In the upper section of the window, click EmployeeNumber
  32. In the lower section of the window, click Caption and press Delete to delete the caption
  33. In the upper section of the window, click FullName
  34. In the lower section of the window, click Caption and press Delete to delete the caption
  35. Save and close the table
  36. To create a new report, on the main menu, click Insert -> Report
  37. In the New Report dialog box, click AutoReport: Columnar
  38. In the combo box, select Employees and click OK
  39. Switch the report to Design View and design it as you see fit
     
  40. Set the Force New Page property of the Detail section to After Section
  41. Preview the report
     
  42. Close the Report
  43. When asked whether you want to save it, click Yes
  44. Accept the default name and click OK
  45. On the main menu, click Insert -> Table
  46. In the New Table dialog box, double-click Table Wizard
  47. In the Sample Tables, click Customers
  48. In the Sample Fields double-click CustomerID
  49. In the Sample Tables, click Employees and, in the Sample Fields, double-click DateHired
  50. Click Rename Field, type DateCreated and press Enter
  51. Click the Personal radio button
  52. In the Sample Tables, click Accounts
  53. In the Sample Fields, double-click AccountNumber and AccountName
  54. Click Business radio button
  55. In the Sample Fields, double-click Address, City, State, PostalCode, Country, EmailAddress, and Notes
  56. Click Next and accept the suggested name of the table as Customers
  57. Click Finish
  58. Switch the table to Design View
  59. Click DateCreated in the top section.
    In the lower section, click Caption and type Date Created
  60. In the top section, edit Postal Code to display ZIPCode
  61. In the lower section, change its Caption to ZIP Code
  62. Right-click Notes and click Insert Rows
  63. In the new empty Field Name, type AccountStatus and set its Data Type to Lookup Wizard...
  64. In the first page of the wizard, click the second radio button and click Next
  65. Click under Col1 and type Active
  66. Press the down arrow key and type Suspended
  67. Press the down arrow key and type Closed
  68. Click Next and click Finish
  69. Set its Caption to Account Status
  70. Close the table and, when asked whether you want to save it, click Yes
  71. Create a few records as with sample customers records
  72. Close the Customers table
  73. To create a new report, on the main menu, click Insert -> Report
  74. In the New Report dialog box, click AutoReport: Columnar
  75. In the combo box, select Customers and click OK
  76. Save the report with the default name
  77. Switch the report to Design View and design it as you see fit. Here is an example:
     
  78. Set the Force New Page property of the Detail section to After Section
  79. Close the Report
  80. When asked whether you want to save it, click Yes
  81. Use the Table Wizard to create a new table based on the Accounts sample table of the Personal category and include only the AccountTypeID field. Rename AccountTypeID as TransactionTypeID
  82. Click Next
  83. Set the Name to TransactionTypes and click Finish
  84. Right-click the title bar of the table and click Table Design
  85. Under the existing field, add another field named TransactionType
  86. Under the existing field, add another field named Description and set its Data Type to Memo
  87. Change the Caption of the TransactionTypeID field to display Transaction Type ID
  88. Change the Caption of the TransactionType field to display Transaction Type
  89. Save the table and switch it to Datasheet View
  90. Perform data entry as follows:
     
    TransactionTypeID TransactionType Description
    1 Deposit Used if a customer is depositing money, regardless of the type of account
    2 Withdrawal Specifies that a customer is receiving money. This also applies when a check is cashed from the customer's account
    3 Fund Transfer This applies to an operation that consists of transferring money from one account to another
    4 Money Order This is selected if a person is purchasing a money order from this bank
    5 Service Charge There are various types of service charges. This category applies to all of them, regardless of the reason, as long as the Bank Management decides to withdraw money from the customer's account as a fee or a penalty
  91. Close the table
  92. In the Tables section of the Database window, make sure TrasactionTypes is selected and, on the Database toolbar, click the New Object: AutoForm button
  93. Close the form
  94. When asked whether you want to save it, click Yes and click OK
  95. To create a new report, on the main menu, click Insert -> Report
  96. In the New Report dialog box, click AutoReport: Columnar
  97. In the combo box, select TransactionTypes and click OK
  98. Close the Report
  99. When asked whether you want to save it, click Yes
  100. Accept the suggested name of the report and click OK
  101. Close the report

Programmatically Creating a Primary Key

Each of the libraries we have reviewed in the previous lessons provides a technique of specifying a column as the primary key or its table. If you are using either the Microsoft Access Object Library or DAO, to specify that a column is used as the primary, when calling the CreateField() method of the table as we saw in Lesson 10, pass a third argument as adKeyPrimary. Here is an example: 

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim colStudentNumber As Object
    Dim colFullName As Object

    Set curDatabase = CurrentDb
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set colStudentNumber = tblStudents.CreateField("StudentNumber", DB_LONG, adKeyPrimary)
    tblStudents.Fields.Append colStudentNumber
    Set colFullName = tblStudents.CreateField("FullName", DB_TEXT)
    tblStudents.Fields.Append colFullName
    
    curDatabase.TableDefs.Append tblStudents
    
    DoCmd.SelectObject acTable, "Students", True
End Sub

To create a primary using SQL, add the PRIMARY KEY (case-insensitive) expression on the right side of the column definition. Here is an example:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE SeasonalEmployees(" & _
                 "ContractorNo LONG NOT NULL PRIMARY KEY, " & _
                 "AvailableOnWeekend LOGICAL NULL, " & _
                 "OwnsACar BIT, " & _
                 "CanShareOwnCar YESNO);"
End Sub

Remember that you can use the data type to influence how the numbers would be assigned.

Practical Learning Practical Learning: Programmatically Creating a Primary Key
  1. In the Database window, click Forms.
    Right-click AccountTypes and click Design View
  2. Double-click the button at the intersection of the rulers to open the Properties window and click Event
  3. Double-click On Load and click its ellipsis button
  4. To programmatically create a table using SQL, implement the event as follows:
      
    Private Sub Form_Load()
    DoCmd.RunSQL "CREATE TABLE Transactions(" & _
                 "TransactionID COUNTER(1001, 1) NOT NULL PRIMARY KEY, " & _
                 "TransactionDate DATE, " & _
                 "EmployeeID LONG, " & _
                 "CustomerID LONG, " & _
                 "TransactionTypeID LONG, " & _
                 "DepositAmount DOUBLE, " & _
                 "DepositTypeID LONG, " & _
                 "WithdrawalAmount DOUBLE, " & _
                 "WithdrawalTypeID LONG, " & _
                 "ServiceCharge DOUBLE, " & _
                 "ChargeReasonID LONG, " & _
                 "Notes MEMO);"
End Sub
  5. Return to Microsoft Access and switch the form to Form View to create the new table
  6. Close the form
  7. When asked whether you want to save the changes, click No
  8. In the Database window, click the Tables button and notice that a table named Transactions has been created

Foreign Keys
 

Introduction

We stated earlier that the essence of a relational database was to get the information to flow from one list to another. To make this possible, a relationship must be created between both tables. After creating a primary key on the first table, to prepare the other table for the relationship, it must have a column that would hold and manage this relationship. This column would be used to "represent" the records of the first table. For this reason, such a column is referred to as a foreign key.

To create a foreign key, on the table that would receive the data, add a column that holds the same data type as the column of the primary key of the first table. Also, the name of the foreign key is usually the same as the name of the column that holds the primary key in the other table.

Practical Learning Practical Learning: Creating Foreign Keys
  1. In the Tables section of the Database window, right-click Customers and click Design View
  2. Under the Field Name, right-click AccountName and click Insert Rows
  3. In the new empty field, type AccountTypeID and press Tab
  4. Set its Data Type to Number and press F6
  5. In the lower section of the window, click Caption and type Account Type
  6. Save and close the table
  7. In the Tables section of the Database window, make sure Customers is selected and, on the Database toolbar, click the New Object: AutoForm button
  8. Switch the form to Design View and position the controls as follows:
     
  9. Close the form
  10. When asked whether you want to save it, click Yes and click OK

A Foreign Key From the Lookup Wizard

Creating a column that has the same name and data type as its corresponding primary key is the traditional technique of adding a foreign key to a table. Microsoft Access provides another technique that assists you with configuring the relationship between the primary key from the original table and the foreign key from the new table. To use it, after creating a column, set its Data Type to Lookup Wizard. A wizard would start where you can select the table that holds the primary key and the column that would show the desired value of the first table.

Practical Learning Practical Learning: Using the Lookup Wizard

  1. In the Tables section of the Database window, right-click Transactions and click Design View
  2. In the top section of the window, click TransactionID
  3. In the lower section of the window, click Caption and type Transaction Number
  4. In the top section of the window, click TransactionDate
  5. In the lower section, change the following properties
    Format: dd-mmm-yyyy
    Input Mask: 00\->L<LL\-0000;0;_
    Default Value: =Date()
    Indexed: Yes (No Duplicates)
  6. In the top section of the window, click EmployeeID and set its Data Type to Lookup Wizard
  7. In the first page of the wizard, accept the first radio button and click Next
  8. In the second page of the wizard, click Employees and click Next
  9. In the Available Fields list, double-click FullName, Title, and CanCreateNewAccount
  10. Click Next three times and click Finish
  11. When asked to save the table, click Yes
  12. Set the Caption to Processed By
  13. In the top section of the window, click CustomerID and set its Data Type to Lookup Wizard
  14. In the first page of the wizard, accept the first radio button and click Next
  15. In the second page of the wizard, click Customers and click Next
  16. In the Available Fields list, double-click AccountNumber and AccountName
  17. Click Next three times and click Finish
  18. When asked to save the table, click Yes
  19. Set the Caption to Customer
  20. In the top section of the window, click TransactionTypeID and set its Data Type to Lookup Wizard
  21. In the first page of the wizard, accept the first radio button and click Next
  22. In the second page of the wizard, click TransactionTypes and click Next
  23. In the Available Fields list, double-click TransactionType
  24. Click Next three times and click Finish
  25. When asked to save the table, click Yes
  26. Set the Caption to Trans Type
  27. In the top section of the window, click DepositAmount
  28. In the lower section, change the following properties
    Format: Fixed
    Caption: Deposit
  29. In the top section of the window, click DepositTypeID and set its Data Type to Lookup Wizard...
  30. In the first page of the wizard, accept the first radio button and click Next
  31. In the top section of the window, click DepositTypes and click Next
  32. In the top section of the window, click DepositType, click Next three times and click Finish
  33. Set its Caption to Deposit Type
  34. In the top section of the window, click WithdrawalAmount
  35. In the lower section, change the following properties
    Format: Fixed
    Caption: Withdrawal
  36. In the top section of the window, click WithdrawalTypeID and set its Data Type to Lookup Wizard...
  37. In the first page of the wizard, accept the first radio button and click Next
  38. In the second page of the wizard, click WithdrawalTypes and click Next
  39. In the Available Fields list, double-click WithdrawalType, click Next three times and click Finish
  40. When asked to save the table, click Yes
  41. Set its Caption to Withdrawal Type
  42. In the top section of the window, click ServiceCharge
  43. In the lower section, change the following properties
    Format: Fixed
    Caption: Charge
  44. In the top section of the window, click ChargeReasonID and set its Data Type to Lookup Wizard
  45. In the first page of the wizard, accept the first radio button and click Next
  46. In the second page of the wizard, click ChargeReasons and click Next
  47. In the Available Fields list, double-click ChargeReason, click Next three times and click Finish
  48. When asked to save the table, click Yes
  49. Set its Caption to Charge Reason
     
  50. Save the table and close it

The Relationships Window

A valuable utility you can use to create and manage the relationships of your tables is the Relationships window. Before using it, you should have created the necessary tables of your database. You also should have created the primary and foreign keys of the tables whose relationships you want to configure.

To access the Relationships window, when displaying the Database window, on the Database toolbar, you can click the Relationships button. Depending on your database, a Show Table may appear with the list of tables of your database. The Show Table dialog box may not appear too. In this case, to display it, you can right-click the window and click Show Table... From the Show Table dialog box, to add a table to the window, click it and click Add. After adding the tables, click Close.

When adding the tables to the Relationships window, if a relationship had already been created between the tables, the Relationship window would recognize it and create a link between them:

If a necessary relationship doesn't show between two tables, to create one between a primary key of a table and its corresponding foreign key in the other table, drag the primary key column and drop it on the foreign key of the other table. The Edit Relationship dialog box would come up. You can examine it to make sure that the right columns have been selected:

If the columns are the valid ones, you can click OK. If they are not, you can click the arrow of each combo box in the lists and select the desired columns, then click OK. After creating a relationship, to examine, configure, or change it, you can double-click the line that represents a link between two tables, or you can right-click that line and click Edit Relationship.

Practical Learning Practical Learning: Using the Relationships Window
  1. On the Database toolbar, click the Relationships button
  2. Right-click the window and click Show Table...
  3. Observe the names of table. Any table that is not displaying, click it in the Show Table dialog box and click Add. After adding all the tables, click Close
  4. Click AccountTypeID from the AccountTypes list. Drag it and drop it on top of AccountTypeID of the Customers list
     
    Creating a Relationship
  5. In the Edit Relationship dialog box, click Create
  6. In the same way, create a relationship between the DepositTypeID field of the DepositTypes list and the DepositTypeID field of the Transactions list:
     
  7. Close the Relationships window
  8. If asked whether you want to save it, click Yes
  9. In the Database window, click Forms and double-click Customers
  10. Switch it to Design View
  11. On the form, click the AccountTypeID text box and press Delete
  12. In the Toolbox, make sure the Control Wizards button is down. Click Combo Box and click the area where the AccountTypeID text box was
  13. In the first page of the wizard, accept the first radio button and click Next
  14. In the second page of the wizard, click AccountTypes and click Next
  15. In the third page of the wizard, double-click AccountType and click Next three times
  16. In the fifth page, select AccountTypeID in the combo box and click Next
  17. Set the label to Account Type and click Finish
  18. Change the new combo box' Name to AccountTypeID
  19. Complete the design the form as follows:
     
  20. Save the form and switch it to Form View
  21. Se the account types as follows:
     
    Account Number Account Type
    28-3782-85 Checking
    92-3782-48 Checking
    38-4227-52 Checking
    68-6434-50 Checking
    83-4654-27 Saving
    47-4783-92 Checking
    82-3763-24 Checking
    72-3474-22 Checking
    34-5458-48 Checking
    29-4586-64 Saving
    68-3465-85 Checking
    40-4658-26 Checking
    56-8468-15 Checking
    94-7785-37 Checking
    37-5764-80 Checking
    34-9754-27 Certificate of Deposit
    72-9375-44 Checking
    37-5490-16 Checking
    20-3454-96 Saving
    76-5475-24 Checking
    27-3457-48 Checking
  22. Close the form

Data Integrity

As mentioned in previous sections, relationships allow information to flow from one list, the parent table, to another list, the child table. When maintaining records, sometimes a piece of information may become obsolete. An employee may decide to change or to delete data from the parent table. This would cause the record in the child table to become orphan. When this happens, you need to take appropriate actions. Referential integrity is the ability to take care of necessary details when data from a table gets changed or deleted.

When a piece of information is changed in a parent table, you need to make sure that the change is replicated to the related child table. If you are creating or troubleshooting a relationship in the Relationships window, after displaying the Edit Relationship dialog box for a particular relationship, you can click the Enforce Referential Integrity check box. This makes available two other check boxes: Cascade Update Related Fields and Cascade Delete Related Fields.

Practical Learning Practical Learning: Insuring Referential Integrity
  1. On the main menu, click Tools -> Relationships...
  2. In the Relationships window, double-click the line between AccountTypes and Customers
  3. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
  4. Click the Cascade Update Related Fields and the Cascade Delete Related check boxes
     
  5. Click OK
  6. Right-click the line between Customers and Transactions and click Edit Relationship...
  7. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
  8. Click the Cascade Update Related Fields and the Cascade Delete Related check boxes
  9. In the same way, enforce the referential integrity of the other relationships
     
  10. Close the Relationships window.
    If asked whether you want to save it, click Yes
  11. In the Database window, click Tables and click Transactions
  12. On the Database toolbar, click the New Object: AutoForm button
  13. Save the form with the default name
  14. Switch the form to Design View and change its design as follows:
     
  15. Save the form
  16. On the form, double-click the TransactionTypeID combo box
  17. In the All tab of the Properties window, double-click On Change and click its ellipsis button
  18. Implement the event as follows:
      
    Private Sub TransactionTypeID_Change()
    On Error GoTo TransactionTypeID_Error
    
    If [TransactionTypeID] = 1 Then ' Deposit
        [DepositAmount].Enabled = True
        [DepositTypeID].Enabled = True
        [WithdrawalAmount].Enabled = False
        [WithdrawalTypeID].Enabled = False
        [ServiceCharge].Enabled = False
        [ChargeReasonID].Enabled = False
    ElseIf [TransactionTypeID] = 2 Then ' Withdrawal
        [DepositAmount].Enabled = False
        [DepositTypeID].Enabled = False
        [WithdrawalAmount].Enabled = True
        [WithdrawalTypeID].Enabled = True
        [ServiceCharge].Enabled = False
        [ChargeReasonID].Enabled = False
    ElseIf [TransactionTypeID] = 5 Then ' Service Charge
        [DepositAmount].Enabled = False
        [DepositTypeID].Enabled = False
        [WithdrawalAmount].Enabled = False
        [WithdrawalTypeID].Enabled = False
        [ServiceCharge].Enabled = True
        [ChargeReasonID].Enabled = True
    Else
        [DepositAmount].Enabled = True
        [DepositTypeID].Enabled = True
        [WithdrawalAmount].Enabled = True
        [WithdrawalTypeID].Enabled = True
        [ServiceCharge].Enabled = True
        [ChargeReasonID].Enabled = True
    End If
    
    Exit Sub
TransactionTypeID_Error:
    MsgBox "There is a problem with processing." & vbCrLf & _
           "Please call Customer Service"
    Resume Next
End Sub
  19. Return to the form and, while the TransactionTypeID combo box is still selected, in the All tab of the Properties window, double click On Lost Focus
  20. Click its ellipsis button and implement the event as follows:
      
    Private Sub TransactionTypeID_LostFocus()
    TransactionTypeID_Change
End Sub
  21. Click the button at the intersection of the both rulers
  22. In the All tab of the Properties window, double-click On Current and click its ellipsis button
  23. Implement the event as follows:
      
    Private Sub Form_Current()
    TransactionTypeID_Change
End Sub
  24. Save the form and switch it to Form View
  25. Create a few transactions
  26. Click Close

 

 
Previous Copyright © Yevol, 2007 Next