Microsoft® Access 2000 Step by Step

Author Catapult, Inc. Pages 320 Disk 1 Companion CD(s) Level Beg/Int Published 05/07/1999 ISBN 9781572319769 Price $29.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 5: Keeping Database Information Reliable

• Validating Data in a Form
  • Add a data validation check
  • Test the new data validation check
  • Add a combo box control to a form
  • Set a default value in a form
  • Change the tab order on a form
• Validating Records Across Related Tables
  • Set a field property to establish referential integrity
  • Set a field property to keep primary keys consistent
• Resolving Many-to-Many Relationships
  • View junction table relationships
  • Using Subdatasheets
  • Open a subform and subdatasheet
  • Insert a subdatasheet
• Finish the lesson

• Add data validation checks and messages.
• Use form controls to increase data entry accuracy.
• Set field properties to strengthen data validation.
• Ensure related tables always contain correct data.
• View and edit related tables or queries in a subdatasheet.
• Recognize many-to-many relationships.

"Garbage in, garbage out" is the motto of the data processing professional. The information that you get out of a database is only as good as the data you put into it. Bad data is often worse than no data at all. In Microsoft Access, database reliability is realized by conscientiously applying data validation and referential integrity.

Data validation is the system of rules that Access 2000 uses to reduce or even eliminate the possibility of error as data is being entered into a database. Data validation can be applied by using the appropriate controls on a form or by setting the appropriate properties on a field. For example, using a list box control on a form restricts the data that can be entered to one of the items on the list, greatly reducing the opportunity for error. Setting an AutoNumber data type property on a number field in a table automatically fills that field with the next sequential number, so there's no possibility of error.

Referential integrity is the system of rules that Access 2000 uses to be sure that relationships between records in related tables are valid, and that changes made in one table are properly applied to a related table. For example, when you delete the record on a customer from the Customers table, all of the order records in the related Orders table should also be deleted, unless a related order is still pending.

To assist Sweet Lil's adoption of Microsoft Access 2000, Impact Public Relations' data manager Becky Sawyer conducted a complete audit of Sweet Lil's data processing operations. The database portion of Becky's research revealed that Sweet Lil's could improve a number of areas. Becky suggests applying data validation checks, or rules, to certain forms or fields, changing the way data is entered into certain forms or fields, enforcing referential integrity between related tables, and coordinating changes in related tables. Becky has asked you to help her implement these improvements.

Start Microsoft Access 2000 and reopen the database

• If Access 2000 isn't started yet, start it. Open the Sweet Lil's database. If the Microsoft Access window doesn't fill your screen, maximize the window.
  If you need help opening the database, see Lesson 1, "Using Forms."

Validating Data in a Form

Access 2000 displays data within graphical objects: forms, queries, reports, data access pages, and tables. These objects are made up of smaller objects-controls-that accept, display, or locate the data. Everything that you see on a form is a control. All data is entered into a form through a form control. By changing the properties of a form control or replacing one control with another, you can change the way data is entered into the form. For example, you can restrict the information that can be entered into a field by changing the field from a text box to a list box, in which only predefined items can be selected. By limiting what can be entered, you reduce the opportunity for error.

Impact Public Relations' Becky Sawyer recommended a number of improvements to the Orders form that will both increase data validation and streamline data entry. In these exercises, you add a data validation check, replace a text box control with a list box control, set a default value, and change the tab order on the Orders form.

Add a data validation check

Becky's research showed that credit transactions could be placed using expired credit cards. She recommended adding a validation rule to check credit card expiration dates as they're being entered into the Orders form. At Sweet Lil's you make the change, adding a data validation rule to the Expiration Date text box control on the Orders form.

1. In the Database window, click Forms on the Objects bar.

2. In the forms list, select Orders, and then click the Design button on the Database window toolbar.

   The Orders form opens in Design view. The Form Design toolbar replaces the Database toolbar and the Formatting toolbar appears just below it.

   
   

3. In the Orders form detail section, click the ExpirationDate text box.

   Small blocks, called sizing handles, appear surrounding the text box and its label to select them.

4. On the Form Design toolbar, click the Properties button.

   The Text Box property sheet appears.

5. In the property sheet, click the Data tab.

   
   

6. In the Validation Rule property box, type the expression >=now and press Enter.

   The expression is converted to >=Now() to indicate that it's a function. The Now() function, which is built into Microsoft Access, retrieves the current date and time. In plain language, the validation rule is: "The expiration date must be either today or later."

7. In the Validation Text property box, type Invalid date! Please check the order and re-enter. and press Enter.

   Whatever you type into the Validation Text property box appears as a message whenever the validation rule is broken.

8. On the Form Design toolbar, click the Save button.

   Access 2000 can now check any date entered in the Expiration Date field and display the validation text for any date in the past.

9. Close the Text Box property sheet.

   The new validation rule and message are now in place.

Test the new data validation check

Testing is the only way to be sure that any change to a field property has the intended effect. In this exercise, you test the data validation check by entering invalid data.

1. On the Form Design toolbar, click the View button to switch to Form view.

   The Orders form reappears in Form view, and the Form View toolbar replaces the Form Design toolbar.

2. On the Form View toolbar, click the New Record button.

   A new blank record appears.

3. On the Orders form, type 9/1/98 in the Expiration Date field, and press Enter.

   The validation text appears as a message.

   
   

4. Click OK.

5. On the Orders form, type 12/1/04 in the Expiration Date field, and press Enter.

   This entry is accepted as a valid date (December 01, 2004).

   A message appears, telling you that Access 2000 can't find a record in the Customers table that matches one in the CustomerID primary key field. This happens because the test record is purposely incomplete and the key data is missing.

6. Click OK.

7. Close the Orders form. <>The message appears again, telling you that Access 2000 can't find a record in the Customers table that matches one in the CustomerID primary key field.

8. Click OK.

   A message appears, confirming that the new record won't be saved. This is a built-in Access 2000 safety feature that prevents you from adding records that are missing key information.

9. Click Yes.

   The new record is discarded, and the Orders form closes.

Add a combo box control to a form

Becky's research showed that Sweet Lil's could streamline much of the data entry work by creating predefined lists instead of having the data entry staff enter the data manually each time. This is especially true of the customer information on the Orders form. Currently, the sales clerks must look up the customer by name in the Customers form to find the customer ID and other data for the Orders form. You can automate this process by using a combo box control that lists the customers by name and then enters the corresponding customer ID, name, and address into the form. At Sweet Lil's you make the change, replacing the CustomerID text box on the Orders form with a combo box.

1. In the Database window forms list, select Orders, and then click the Design button on the Database window toolbar.

2. In the Orders form, click the CustomerID text box, and then press Delete.

   When you click the text box, sizing handles appear around the CustomerID text box and its label to select them. When you press Delete, both objects disappear.

3. If the field list is not visible, click the Field List button on the Form Design toolbar.

   The field list appears.

   
   

4. In the toolbox, be sure that the Control Wizards tool is selected, and then click the Combo Box tool.

5. In the field list, drag the CustomerID field to the center of the empty space on the Orders form where the CustomerID text box used to be.

   The mouse pointer becomes a field pointer. When you release the mouse button, a combo box and its label are added to the form and the first page of the Combo Box Wizard appears.

6. On the first Combo Box Wizard page, be sure the I Want The Combo Box To Look Up The Values In A Table Or Query option is selected, and then click Next.

   The second page of the Combo Box Wizard appears.

7. In the View area, select the Queries option.

   The queries list replaces the tables list.

8. In the queries list, select Customer List, and then click Next.

   The third page of the Combo Box Wizard appears.

   
   

9. In the Available Fields list, double-click CustomerID, LastName, and FirstName, in that order, and then click Next.

   As you double-click each field name, the fields appear in the Selected Fields list in the same order in which you double-clicked them. When you click Next, the fourth page of the Combo Box Wizard appears.

10. Double-click the right edge of each of the three column selectors, and then click Next.

    As you double-click each column selector, the width of each column adjusts to the best fit for the data it contains. When you click Next, the fifth page of the Combo Box Wizard appears.

11. Click CustomerID, the column that contains the data you want, and then click Next.

    CustomerID is now bound to the combo box. When you click Next, the sixth page of the Combo Box Wizard appears.

12. Be sure that the Store That Value In This Field option is selected and that CustomerID is selected in the corresponding box, and then click Next.

    Data from the CustomerID field is now listed in the combo box. When you click Next, the last page of the Combo Box Wizard appears.

13. Be sure that Customer ID is the label for your combo box (include a space before ID), and then click Finish.

    The Orders form reappears in Design view. It now has a combo box, bound to the CustomerID field, in place of the text box.

14. Close the field list.

15. Save your changes and click the View button on the Form Design toolbar to switch to Form view.

16. In the new Customer ID combo box, select the name Faye Palmer.

    The number 98 appears in the Customer ID combo box and the customer information for Faye Palmer appears in the Bill To area of the form.

You can also add bound controls to tables. For example, you can add a lookup column to a table that works just like the new CustomerID combo box on the Orders form. The lookup column is a combo box that replaces a text box in a table. It is bound to a field in another table or in a query and displays the data in that column as a list in the combo box. Any form based on the lookup column automatically incorporates the field as a combo box control.

Set a default value in a form

Becky's research showed that Sweet Lil's sales clerks often end up entering and selecting data unnecessarily, and that employees' data entry can be further streamlined by changing the default values of certain fields. The default value is a predefined item of data that's used if nothing else is entered or selected. Because the date that the sales clerks must enter into the Order Date combo box on the Orders form is always the date that the form is filled out, Becky has recommended changing the field so that it automatically fills in the current date. In this exercise, you make the change, setting the default value of the OrderDate field to the current date.

1. On the Form View toolbar, click the View button to switch to Form Design view.

2. In the Orders form detail section, click the OrderDate text box.

   The text box and its label are selected.

3. On the Form Design toolbar, click the Properties button.

   The property sheet for the text box appears.

4. In the Default Value property box, type the expression =Date()

   The Date() function, which is built into Access, returns the current date. Today's date is now set as the default value for the OrderDate text box.

   
   

5. Close the property sheet and save your changes.

   Access 2000 now puts today's date in the OrderDate field of each record as the record is added to the Orders form.

6. On the Form Design toolbar, click the View button to switch to Form view, and then on the Form View toolbar, click the New Record button.

   A new blank Orders form appears, with today's date in the OrderDate field.

7. Close the Orders form.

   A message appears, asking if you want to save your changes.

8. Click Yes.

   The new record is discarded, and the Orders form closes.

Change the tab order on a form

Becky's database research showed that the process for moving between fields on a form doesn't always follow the layout of the form. For example, on the Orders form, customer details are grouped in one area, shipping details in another, and credit information in a separate section, but the sales clerks must jump from one area to another as they fill out the form. The sequence in which the insertion point moves from field to field on a form is called the tab order. In this exercise, you help Liz implement Becky's form improvement recommendation, changing the tab order of the Orders form so that the data entry sequence flows smoothly from one area to another.

1. In the Database window forms list, select Orders, and then click the Design button on the Database window toolbar.

2. On the Orders form, click the CustomerID combo box.

   The CustomerID combo box and label are selected.

3. On the View menu, click Tab Order.

   The Tab Order dialog box appears. Because the CustomerID field was just changed, CustomerID is now last in the Custom Order list.

   
   

4. In the Tab Order dialog box, scroll to the bottom of the Custom Order list and click the field selector to the left of CustomerID.

5. Drag CustomerID to the top of the Custom Order list.

6. In the Custom Order list, drag Street up until it's just below LastName.

7. Drag ShipFirstName down until it's just below Country.

8. Continue rearranging the Custom Order list until the fields are in the following order.

   CustomerID
   OrderDate
   OrderID
   FirstName
   LastName
   Street
   City
   StateOrProvince
   PostalCode
   Country
   ShipFirstName
   ShipLastName
   ShipStreet
   ShipCity
   ShipStateOrProvince
   ShipPostalCode
   ShipCountry
   CreditCard
   AccountNumber
   ExpirationDate
   Gift
   Orders Subform
   Subtotal

9. In the Tab Order dialog box, click OK.
   The Tab Order dialog box closes. The new tab order is now saved.

10. On the Form Design toolbar, click the View button to switch to Form view.
    The CustomerID field is selected.

11. Press Tab to move through the Orders form.
    The new tab order moves in a logical progression across the top of the form, and then through the Bill To area, Ship To area, and Credit Card area before jumping to the subform.

12. Press Ctrl+Tab to move to the Total area in the main form.

13. Save your changes, and close the Orders form.

Validating Records Across Related Tables

To protect data in related tables from becoming disconnected, Access 2000 applies a system of rules called referential integrity. Referential integrity uses the relationships between tables to preserve data integrity and reliability. Changes made to one table are also made to the related tables, according to a set of rules that prohibit invalid relationships and prevent mismatches.

Referential integrity enforces the following basic rules:

• You can't enter a value in the foreign field of the related table unless that value already exists in the primary key field of the primary table. For example, you can't create a new order record in the Orders table unless you have a customer ID that matches a customer ID in the Customers table. (You can't place an order for a customer who doesn't exist.)

• You can't delete a record from the primary table if matching records exist in any related table. For example, you can't delete a customer record from the Customers table if there are matching orders for that customer in the Orders table. (You can't delete a customer while there are orders pending for that customer.)

• You can't change a primary key value in the primary table if there are any related records that refer to that value. For example, you can't change a customer ID in the Customers table if there are matching orders for that customer in the Orders table. (You can't change a customer's unique ID while there are orders pending for that customer.)

Referential integrity can't always be applied. You must ensure that your tables meet the following conditions before you can set or use referential integrity.

• The matching field from the primary table must be a primary key.

• Any value in the foreign field of the related table must already exist in the primary key field of the primary table.

• The related fields of both tables must have the same data type, which defines the kind of data (such as text, currency, or yes/no) that a field contains. The related fields must have the same field size.

• Both tables must belong to the same Access 2000 database. If the tables are in separate files that have been linked, the files must be in Microsoft Access database (.mdb) format, and the database in which the linked tables are stored must be open.

Cascading update applies any changes that you make in the primary table to the matching records in the related tables. For example, a change to the unique customer ID number in the Customers table "cascades" to the Orders table. Orders created using the old customer ID are automatically updated to the new customer ID, rather than being "orphaned" because they refer to a customer ID that no longer exists.

Cascading delete removes all matching records in the related tables when a record in the primary table is deleted. For example, the deletion of a customer record from the Customers table cascades to the matching order records in the related Orders table, which cascades in turn to the matching detail records in the Order Details table. All matching order records in the Orders table are automatically deleted. Then, for each deleted order record in the Orders table, all matching detail records in the Order Detail table are also deleted. This would remove pending as well as fulfilled orders, however, so cascading delete should be used with caution.

Database research conducted by Impact Public Relations' Becky Sawyer revealed that data in the Customers and Orders tables could be deleted or changed in ways that might compromise data integrity and reliability. In these exercises, you apply referential integrity to the Sweet Lil's database, with cascading update between these related tables.

Set a field property to establish referential integrity

Becky's database research showed that it's possible to delete customer records from the Customers table while there are still pending order records in the Orders table. At Sweet Lil's, you prevent such deletions by applying referential integrity between the two tables. By applying referential integrity, and not choosing cascade delete, you can add the safety feature of making it impossible to delete customers who have pending orders.

1. In the Database window, click Tables on the Objects bar.

2. On the Database toolbar, click the Relationships button.

   The Relationships window opens. The Relationship toolbar replaces the Database toolbar.

3. On the Relationship toolbar, click the Clear Layout button, clicking Yes to the message confirming that you want to clear the table layout.

   The table layout in the Relationships window is cleared.

4. On the Relationship toolbar, click the Show Table button.

   The Show Table dialog box appears.

   
   

5. In the Show Table dialog box, double-click Customers in the tables list, and then click Close.

   The Show Table dialog box closes, and the Customers table appears in the Relationships window.

6. On the Relationship toolbar, click the Show Direct Relationships button.

   The Orders table appears in the Relationships window, with a line indicating a one-to-many relationship between the Customers table and the Orders table.

   
   

7. In the Relationships window, double-click the thin section of the line connecting the Customers table to the Orders table.

   The Edit Relationships dialog box appears, with the CustomerID field selected for both the Customers (primary) table and the Orders (related) table.

   
   

8. In the Edit Relationships dialog box, verify that the Enforce Referential Integrity check box is selected.

9. In the Edit Relationships dialog box, click Join Type.

   The Join Properties dialog box appears.

10. In the Join Properties dialog box, be sure the Only Include Rows Where The Joined Fields From Both Tables Are Equal option is selected, and click OK.

    The Join Properties dialog box closes.

11. In the Edit Relationships dialog box, click OK.

    The Edit Relationships dialog box closes. Referential integrity is now in force between the Customers and Orders tables.

Set a field property to keep primary keys consistent

Becky's database research showed that changes to the contents of the BoxID field in the Boxes table could break the relationship between the Boxes, Box Details, and Order Details tables, resulting in incorrectly filled orders. At Sweet Lil's, you prevent such mismatches by applying cascading update to the Box ID field.

1. On the Relationship toolbar, click the Clear Layout button, clicking Yes to the message confirming that you want to clear the table layout.

   The table layout in the Relationships window is cleared.

2. On the Relationship toolbar, click the Show Table button.

   The Show Table dialog box appears.

3. In the Show Table dialog box, double-click Boxes in the tables list, and then click Close.

   The Show Table dialog box closes, and the Boxes table appears in the Relationships window.

4. On the Relationship toolbar, click the Show Direct Relationships button.

   The Box Details and Order Details tables appear in the Relationships window, with lines indicating a one-to-many relationship between the Boxes table and the other two tables.

5. In the Relationships window, drag the Order Details table down and left until it's directly below the Box Details table.

   The relationships are easier to see when the lines and tables don't overlap.

   
   

6. In the Relationships window, double-click the line connecting the Boxes table to the Box Details table.

   The Edit Relationships dialog box appears, with the BoxID field selected for both the Boxes (primary) table and the Box Details (related) table.

7. In the Edit Relationships dialog box, be sure that the Enforce Referential Integrity check box is selected.

   
   

8. Select the Cascade Update Related Fields check box, and click OK.

   The Edit Relationships dialog box closes. Cascading update is now applied between the Boxes table and the Box Details table.

9. In the Relationships window, double-click the line connecting the BoxID field in the Boxes table to the BoxID field in the Order Details table.

   The Edit Relationships dialog box appears, with the BoxID field selected for both the Boxes (primary) table and the Order Details (related) table.

10. In the Edit Relationships dialog box, be sure that the Enforce Referential Integrity check box is selected, select the Cascade Update Related Records check box, and click OK.

    The Edit Relationships dialog box closes. Cascading update is now applied between the Boxes table and the Order Details table.

11. Close the Relationships window, clicking Yes to confirm that you want to save your changes to the table layout.

Resolving Many-to-Many Relationships

A many-to-many relationship occurs when records in each related table can have more than one matching record in the other table. This can occur because, viewed from its own perspective, each side of the many-to-many relationship appears be a one-to-many relationship. For example, baskets and bonbons appear to have a one-to-many relationship because one basket can contain many different types of bonbons. The reverse, however, is also true: one type of bonbon can be contained in many different baskets.

The many-to-many relationship becomes evident only when you view such a relationship from both sides. It usually becomes apparent when you actually try to join such tables in a one-to-many relationship. Despite appearances, you can't make a one-to-many relationship between the two tables, no matter which table you set as the primary table. The only solution is to create a third table that links the other two: a junction table.

A junction table is an intermediate table that serves as a bridge between two tables with a many-to-many relationship. It uses the primary keys of the two other tables as its primary key (it is a multiple-field primary key); it is related to each table in a one-to-many relationship. It also contains at least one additional field that doesn't exist in either of the other two tables, but is relevant to both. The many-to-many relationship becomes a many-to-one and one-to-many relationship.

In the Sweet Lil's database, the Basket Details table is a junction table that resolves the many-to-many relationship between the BasketID and BonbonID fields of the Baskets and Bonbons tables. The primary key of the Basket Details table consists of the primary keys of two tables that the Basket Details table joins: the BasketID field of the Baskets table and the BonbonID field of the Bonbons table. The Quantity field of the Basket Details table resolves the many-to-many relationship by specifying the number of bonbons assigned to each basket.

View junction table relationships

As you make recommended changes to the database, you become more aware of the relationships between tables. You notice that the Basket Details table has only one unique field-Quantity. Why is a separate table necessary? In this exercise, you discover that the Basket Details table serves as a junction table between the Baskets and Bonbons tables to resolve a many-to-many relationship.

1. Verify that all tables and forms are closed.

2. On the Database toolbar, click the Relationships button.

   The Relationships window opens. The Relationship toolbar replaces the Database toolbar.

3. On the Relationship toolbar, click the Clear Layout button, clicking Yes to the message confirming that you want to clear the table layout.

   The table layout in the Relationships window is cleared.

4. On the Relationship toolbar, click the Show Table button.

   The Show Table dialog box appears.

5. In the tables list, double-click Basket Details, and then click Close.

   The Basket Details table appears in the Relationships window, and the Show Table dialog box closes.

6. On the Relationship toolbar, click the Show Direct Relationships button.

   The Baskets and Bonbons tables appear in the Relationships window, with lines indicating the one-to-many relationship between each of them and the Basket Details table.

7. Drag the Bonbons table down and left until it's directly below the Baskets table.

   The relationships are easier to see when the lines and tables don't overlap.

   
   

8. In the Relationships window, study the path from the Baskets table to the Basket Details table, and then from the Basket Details table to the Bonbons table.

   You can now see the relationships among the three tables. The Baskets table has a one-to-many relationship with the Basket Details table through the primary key (BasketID field) of the Baskets table. The Basket Details table has a many-to-one relationship with the Baskets table through the multiple-field primary key (BasketID and BonbonID fields) of the Basket Details table. The Bonbons table has a one-to-many relationship with the Basket Details table through the primary key (BonbonID field) of the Basket Details table.

9. In the Relationships window, be sure the Bonbons table is selected. Then, on the Relationship toolbar, click the Show Direct Relationships button twice.

   The Box Details and Boxes tables appear in the Relationships window, with lines indicating the one-to-many relationships between them and between the Box Details and Bonbons tables.

10. In the Relationships window, study the path from the Baskets table to the Basket Details table, and then from the Basket Details table to the Bonbons table.

    You can now see that the relationships among the Boxes, Box Details, and Bonbons tables are exactly the same as those of the Baskets, Basket Details, and Bonbons tables. Basket Details is a junction table between the Bonbons and Baskets tables, and Box Details is a junction table between the Bonbons and Boxes tables.

11. Close the Relationships window without saving the layout.

Using Subdatasheets

A table relationship can become visible in Datasheet view through a subdatasheet. A subdatasheet provides a hierarchical datasheet view, allowing you to browse and to edit related tables or queries from a single window. You view the primary data in the datasheet and related data in a subdatasheet, in much the same way that you view primary data in a form and related data in a subform.

For example, the relationship between the Baskets form and the Basket Details subform is now mirrored in the relationship between the Baskets table datasheet and the Baskets Subform Query subdatasheet. The parallel relationship between the Boxes form and Box Details subform is also mirrored in the relationship between the Boxes table datasheet and the Boxes Subform Query subdatasheet.

You can insert any related table or query as a subdatasheet to any datasheet. For example, the Bonbons datasheet has the Bonbons By Box query as a subdatasheet. This allows you to go down the list of bonbons in the Bonbon datasheet and look up the boxes in which any given bonbon is shipped. If you want to look up the baskets in which each bonbon is shipped, you can substitute the Bonbons By Basket query as the subdatasheet and even switch back and forth between the two subdatasheets-all from within Datasheet view.

Now that you know how important and far-reaching the relationships between tables are, you browse through and insert subdatasheets to see exactly how data is related.

Open a subform and subdatasheet

In this exercise, you look at the mirrored relationship between Datasheet view with a subdatasheet for the Baskets table, and Form view with a subform view for the Baskets form.

1. In the Database window, click Forms on the Objects bar to display the forms list.

2. Double-click the Baskets form, and view the information displayed in the first record.

   General information about the Summer Sampler basket appears in the main form. The subform displays details concerning the contents, four bonbon types, and their associated names: Bittersweet Blueberry, Bittersweet Strawberry, Bittersweet Raspberry, and Sweet Strawberry.

3. Close the Baskets form.

4. In the Database window, click Tables on the Objects bar to display the tables list.

5. Double-click the Baskets table.

   The Baskets table displays the same information as the main Baskets form. You can't see all of the information in the form using just this datasheet.

6. To the left of the first record, click the plus sign (+).

   The subdatasheet appears. It displays the same information as the Baskets subform. The datasheet plus the subdatasheet of the Baskets table shows you the same information as the main form plus the subform of the Baskets form. Moreover, the connection between the related tables is clearer with both forms showing in Datasheet view.

7. To the left of the first record, click the minus sign (-).

   The Baskets subdatasheet closes.

8. Close the Baskets table.

Insert a subdatasheet

In this exercise, you find that it's easy to insert a different subdatasheet to browse through a different set of related data, all from within Datasheet view. You substitute the Bonbons By Basket query as a subdatasheet for the Bonbons By Box query on the Bonbons datasheet.

1. In the Database window, double-click the Bonbons table.

   The Bonbons table contains information about each individual bonbon. The bonbon name in the first record, Candlelight Ecstasy, is selected.

2. In the first record, open the subdatasheet.

   The Bonbons subdatasheet opens with the box name of the first record, Northwind Collection, selected. The subdatasheet links the Bonbons table to the Bonbons By Box Query table, which relates individual bonbons to the various boxes. You can see that the Candlelight Ecstasy bonbon is included in the Northwind Collection and Sweet Creams boxes, where it accounts for $1.20 and $1.80, respectively, of the total cost of the boxes.

3. Close the subdatasheet.

4. On the Insert menu, click Subdatasheet.
   The Insert Subdatasheet dialog box appears.

   
   

5. In the Insert Subdatasheet dialog box, select Bonbons By Basket from the list on the Both tab, and click OK.

   The Master and Child boxes in the Insert Subdatasheet dialog box correspond to the related fields in the primary and related tables. You can see that the Bonbons and Bonbons By Basket Query tables are related through the Bonbon Name field.

6. In the third record, open the subdatasheet.

   The Bonbons By Basket Query table is now attached as a subdatasheet to the Bonbons table, so you can now see that the Marzipan Oakleaf bonbon is included in the Summer Sampler basket, where it accounts for 40 cents of the total cost.

7. Close the subdatasheet, and then close the Bonbons table.

   A message appears, confirming that you want to save the changes to the table layout.

8. Click Yes.

   The Bonbons By Basket Query table will appear in the subdatasheet the next time you open the Bonbons table.

Finish the lesson

1. To continue to the next lesson, on the File menu, click Close.

2. If you're finished using Access 2000 for now, on the File menu, click Exit.

Lesson 5 Quick Reference
To	Do this	Button
Add a validation rule to a control	In Design view, click the control. Click the Properties button on the Form Design toolbar. In the property sheet, type the rule in the Validation Rule property box.
Add a control to a form	In Design view, click the Field List button on the Form Design toolbar. Click the Control Wizards tool in the toolbox, and then click the tool for the control you want to add. Drag the field from the field list to the form.
Set a default value for a field	In Design view, click the control. Click the Properties button on the Form Design toolbar. In the property sheet, type the value in the Default Value property box.
Change the tab order on a form	In Design view, click a form control. Select Tab Order on the View menu. Rearrange the Custom Order list in the order you want.
Establish referential integrity between two related tables	In the Relationships window, double-click the line between the related tables. In the Edit Relationships dialog box, select Enforce Referential Integrity.	.
Apply cascading	In the Relationships window, with one of the update tables displayed, click the Show Direct Relationships button on the Relationship toolbar. Double-click the line between the related tables. In the Edit Relationships dialog box, select Enforce Referential Integrity and Cascade Update Related Fields.
Apply cascading delete	In the Relationships window, double-click the line between the related tables. In the Edit Relationships dialog box, select Enforce Referential Integrity and Cascade Delete Related Fields.
Use a junction table	Create a table containing the primary keys of both of the tables you want to join as a multiple-field primary key. Add a field that is relevant to both tables. Establish a one-to-many relationship between the junction table and the tables you want to join.