Creating a Form with a One-Level Subform

Investigating the Relationships window shown in Figure 3-2, you will note that you can use the FOOD_DES and WEIGHT tables because they have a direct one-to-many relationship (each food item can have many different types of presentations according to the WEIGHT table records).

Follow these steps to create a form/subform using these two tables:

1. 1.

   Select the table that represent the “one” side of the relationship in the Database window (FOOD_DES) and click the Form Wizard command on the Create tab.

    
2. 2.

   On the Form Wizard’s first page, double-click the fields NDB_No, Long_Desc, and Refuse to add them to the Selected Fields list.

    
3. 3.

   Still on the first page, click the Table/Queries box to expand it, and select the WEIGHT table in the list.

    
4. 4.

   Double-click the fields Amount, Msre_Desc, and Gm_Wgt to also add them to the Selected Fields list.

    
5. 5.

   Click Next in the wizard to detect the direct relationship between these two tables and define how the form must group its records, using the “one” side (FOOD_DES) or the “many” side (WEIGHT).

    
6. 6.

   Keeps FOOD_DES selected (“one” side) and note that the wizard allows us to show the data in two different ways: using “Form with subform(s)” (one single form) or “Linked Forms” (two independent windows forms that synchronize its records).

   Caution The Linked form proposed by the Form Wizard to show two different windows is not working well and will produce a “buggy” form. Later in this book, you will learn how to synchronize two forms using a simple macro.

    
7. 7.

   Keep the “Form with subform(s)” option selected and click Next. The wizard will show to present the data: using a Tabular or Datasheet view. Select Datasheet view to get a better subform view and click Next.

    
8. 8.

   The wizard will show the name of the two forms it will create: one for the “one” side (FOOD_DES) and another for the “many” side of the relationship (the WEIGHT subform, as shown in Figure 4-6).

   Note Following the rules proposed in Chapter 1, prefix both form names with the “frm” tag.

    

The Form Wizard will create both forms (frmFOOD_DES and the frmWEIGHT subform). Open frmFOOD_DES and show the first FOOD_DES record (Butter Salted) and all four of its WEIGHT measures for portions. Each form has its own navigation buttons, which you can use to select another food item or food measure. Use the main form (frmFOOD_DES) navigation buttons to change the food item and note how it synchronizes the WEIGHT records of its subform (Figure 4-7).

Creating a Form with a Two-Level Subform

Microsoft Access allows you to create up to two levels of subforms, which means that any subform can have its own subform to synchronize up to three tables at once using a single interface.

Supposing that you want to create a form to show food categories (FR_GROUP table), food items of each category (FD_DES table), and the most common portions weight (WEIGHT table), you can use the Form Wizard to create such a form with two subforms. Follow these steps:

1. 1.

   Open the Form Wizard, and on its first page select the FD_GROUP table in the Table/Queries box. Select its Fd_GrpDesc field and click > to add it to the Selected Fields list.

    
2. 2.

   Still on the first wizard page, select the FOOD_DES table in the Table/Queries list, select the desired fields (NDB_No, Shrt_Desc and Refuse), and click > to add it to the Selected Fields list.

    
3. 3.

   Once more on the first wizard page, select the WEIGHT table in the Table/Queries box, select the desired fields (Amount, Msre_Desc, and Gm_Wgt), and click > to add it to the Selected Fields list (see Figure 4-8).

    
4. 4.

   Click Next. The wizard detects the direct relationship between these three tables and defines how the form must group its records (keep selected FD_GROUP and the “Form with subform(s)” option).

    
5. 5.

   Click Next and note that the wizard now offer two options to show the two subforms (one form for FD_DES and another for WEIGHT tables). Select Datasheet view on both options.

    
6. 6.

   Click Next, and the wizard will show the name of the three main forms it will create: one for FD_GROUP, one for FOOD_DES), and another for WEIGHT.

    
7. 7.

   Rename the forms by prefixing them with the “frm” tag (and eventually add a suffix to the ones the wizard warns it will overwrite, like the frmWEIGHT subform, created in the previous section; see Figure 4-6).

    
8. 8.

   Click Finish to make the Form Wizard create the form with its two subforms, each one having its own navigation buttons. Use the main form navigation buttons to show different food categories, and note how the food items of these categories are automatically synchronized and how the common measures of the selected food item are automatically shown in the second-level subform (Figure 4-9).

    

Selecting the Form and Its Sections

You can set properties for both the form and each of its sections. Form properties relate to the way the entire form window behaves, while section properties relate just to that specific portion of the form.

Since the Form object and its sections have a lot of different properties, let’s explore some of them, comparing with the forms created by the Form Wizard or More Forms options, whenever possible.

Setting the Form’s Record Source

To define the records shown by a form and what a user can do with them, you need to select the Form object and use the Form Data tab’s Property Sheet.

Basically, you just need to set the form’s Record Source type to the name of a table, query, or SQL instruction to make it capable of showing its records. To define the form record source, you can do the following:

• Click the Record Source property arrow to expand its list and select the name of a table or query saved on the database (Figure 4-12).

• Click the ellipsis button located to the right of Record Source property to show the form’s Query Builder window, where you can create a query, save it, or just store its SQL instruction on the form’s Record Source property.

Adding Fields to the Form Detail Section

Once you set the form’s Record Source property, click the Add Existing Fields command in the Tools area of the Form Design tab to show the Field List window with all its fields returned by the table, query, or SQL instruction.

Now you need to add the desired fields to the form by selecting the desired fields in the Field List windows (Ctrl+click to select individual fields, Shift+click to select a field sequence) and dragging them from the Existing Fields section in the Form Details section. By default, all dragged fields will be associated to a Text Box control (Figure 4-13).

Figure 4-14 shows the form from Figure 4-13 using Form and Datasheet views.

Setting Form Data Properties

Besides setting the form’s Record Source property, you can customize how the records will be manipulated by the user by setting Form Data properties.

Figure 4-15 shows how Form1 from Figure 4-13 will appear in Layout view with Data Entry set to Yes (it shows a new record, while its navigation buttons indicate “Record 1 of 1”).

Setting Form Format Properties

All forms have an extensible list of Format properties that allow control over how the form window will behave. By correctly using the Format properties, you can improve the user experience with your database interface and create a different kind of form, which can be used both to show records and to produce the database interface.

Figure 4-16 shows Form1_FoodItems, created previously in this chapter, with some Format properties set so you can best understand their usage and meaning (it has Caption set to Food Item, Navigation Buttons set to Yes, Dividing Lines set to Yes, and Scroll Bars set to Both).

Looking at the forms created by the Form Wizard and More Form tools for the FOOTNOTE table, you will notice that frmFOOTNOTE_Tabular (Figure 4-4) and frmFOOTNOTE_MultipleItems (Figure 4-5) have the property Default View set to Continuous. Their Detail section repeats continuously, showing multiple records in a single form window, while frmFOOTNOTE_SplitForm shows the Detail section above using a single record view, with the form’s Datasheet view at the bottom, because it has the property Default View set to Split Form and Split Form Orientation set to Datasheet on Bottom (Figure 4-17).

Form frmModalDialog (Figure 4-5) is the one that differs in behavior, because it has no Record Source property, it has disabled access to Microsoft Access, and it does not allow the Layout View mode (property Modal set to Yes). Also, it has a thin border that cannot be resized (property Border Style set to Dialog) and can float outside the Microsoft Access interface over other opened Windows applications (property PopUp set to Yes, as shown in Figure 4-18).

The other properties shown in Table 4-2 will be commented on in this book whenever a form needs to use them. For now, it is enough that you understand that they exist and what each one does.

Setting Form Section Properties

Besides setting properties associated to the data or format of the form window, you can also set properties for each form section.

As with the Format properties, we will discuss Section properties later in the book. For now, just pay attention that the forms created by the Form Wizard, More Forms, and Form Design tools (Figures 4-5, 4-6, 4-7, and 4-12) show the Form Header section with a light blue background color because their property Back Color is set to Text2, Lighter 80%, while frmFOOTNOTE_Tabular and frmFOOTNOTE_Datasheet, as shown in Figure 4-4, and all but frmModeDialog in Figure 4-5 alternates the Detail section background color between white and light gray because they have the properties Background set to Background 1 and Alternate Back Color set to Background 1, Darker 5% (Figure 4-19).

In the next sections, you will learn how to use form controls to give your database applications a more attractive layout.

Break and Rebuild a Label-Control Association

Since there are a lot of control types not counting the default label-control association, it is a good programming practice that you also use a naming convention rule to better identify the controls inserted on a form.

Enabled and Locked Properties

All controls have the properties enabled (with the default value set to Yes) and locked (with the default value set to No), already cited in Table 4-7. By using both properties, you can create different types of formatting regarding the control change and its selection.

Figure 4-24 shows the unbound frmTextBox_EnabledLocked (that you can extract from Chapter04.zip) both in Design and Form views. It has four unbound Text Box controls that use both properties so you can better appreciate the control interface behavior.

Note that all Label controls were dragged by its handle to the top of the associated Text Box to make better use of the form’s Detail section space. Also, note that the first two Text Boxes (the ones with Enabled set to Yes) have the Control Tip property set (point the mouse to it and wait a second to see the floating tip).

The Text Box default behavior happens when Enabled is set to Yes and Locked is set to No, meaning that you can select and change it contents. When Enabled is set to No and Locked is set to No, the control becomes disabled and uses a light gray background color—you can’t select it or change its contents. When Enabled is set to Yes and Locked is set to Yes, the control can be selected, but its content can’t be changed. When Enabled is set to No and Locked is set to Yes, the control can’t be selected or changed, but it appears with the normal background color.

Since it has both Scroll Bars (vertical and horizontal), whenever you shrink the form size by dragging its border, they will automatically allow scrolling for the Detail section (and the gray border will disappear, as shown in Figure 4-25).

The control’s Tab orders are set by using the Tab Order command in the Tools area of the Form Design tab. It will show the Tab Order window, from where you can select each form section and drag its controls to the desired tab order—the form will change its Tab Index properties automatically. See Figure 4-26.

Now let’s explore other specific Text Box properties.

Specific Text Box Properties

Use the Property Sheet’s Format tab to set how the value typed on the Text Box must appear whenever you press Enter. To get better control over what can be typed, use the Input Mask property in the Property Sheet’s Data tab.

Show Date Picker and Input Mask Properties

The Text Box control can be optimized to receive valid dates using the Input Mask property or a Date Picker control to select it from a calendar—not both.

Whenever these rules are followed, Microsoft Access will show a small calendar located at the control’s right corner, where the user can select the desired date.

By setting the Format property, the user can type (or select) a date using a short format (like mm-dd-yy) and show it in another way; Format set to Long Date spells both the weekday and month names for the selected date.

Using the Validation Rule and Validation Text properties, the control can also check whether the date selected is valid for the application (like a date equal or greater than system date). But be advised that whenever the Text Box control uses the property Validation Rule (and Validation Text) along with the properties Input Mask or Show Date Picker, in the case the user selects a date, it will no longer be able to delete it from the control. The Text Box will stuck firing the validation rule requiring a date selection.

Figure 4-27 shows the unbound form frmTextBox_DatePicker, which has four Text Box controls that use the properties Input Mask, Default Date Picker, and eventually Validation Rule and Validation Text to allow you to check its behavior.

All four Text Box controls have the properties Format set to Long Date and Show Date Picker set to For Dates, but just the two Text Boxes at the bottom automatically show the Date Picker calendar when selected, because they have no input mask.

The Text Box controls at the left have no validation rule, allowing you to type and delete its content by pressing the Delete key.

But whenever you use the Validation Rule property (and Validation Text), the Text Box will no longer be able to allow you to delete its content, because by doing this it will break the validation rule.

Whenever you type a birthday date using year 1900 or earlier, the validation rule will be broken, and the validation text will fire. But if you type a valid date (year > 1900) and then try to delete it, the empty control will also break the validation rule, because it will have no date—and no year to test. The Text Box control will be stuck asking for a date with “Year must be > 1900” (Figure 4-28).

Pay attention to these properties of frmTextBox_DatePicker:

• It is not centered on the screen (Auto Center set to No); it stores the screen position (the form’s Top and Left properties) from where it was last saved in the form’s Design view.

• It automatically fits the Detail section’s height and width (Auto Resize set to Yes).

• It has no sizeable border or maximize or minimize buttons (Border Style set to Dialog).

• It has no dark gray bottom bar (Scroll Bars set to None).

• It has Min Max buttons set to None.

• It sets the tab order to first select the controls on the left and then on the right (from top bottom). See Figure 4-29.

Can Grow and Can Shrink Properties

Use the properties Can Grow and Can Shrink to allow a Text Box control to change its size to adjust its content whenever it is printed. They do not work on Form view or Layout view—just in Print Preview.

The property Can Grow makes the Text Box grow vertically to show more text lines, while the property Can Shrink makes it shrink up to the height defined on the form’s Design view.

Open frmTextBox_CanGrow in design mode to verify how the property works. It is linked to the query qryFoodItemsAZ, which shows food item names stored on table FOOD_DES ordered by the Food Name field (alias for the FOOD_DES Shrt_Desc field).

The Food Name text box (automatically named by dragging the controls from the Field List window) has its Can Grow property set to Yes, which will make it grow to show more lines whenever you print preview the form, while Can Shrink set to Yes could make Food Name Text Box disappear for any item without a food name inserted (Figure 4-30).

Change it to Form view mode. It shows two records at a time due to it being a continuous form, alternating its Detail section background color among records. Change its records and note that the Food Name Text Box does not grow in Form view mode. Switch to Print Preview (File ➤ Print ➤ Print Preview), go to page 3, and note that the Food Name Text Box grew for some records to show the entire contents (Figure 4-31).

Text Format and Enter Key Behavior Properties

Two Text Box properties allow you to change a Text Box to a small text processor: Text Format (Data tab) and Enter Key Behavior (Other tab).

When Text Format is set to Rich Text, the Text Box allows text formatting: you can set bold, underline, text color, paragraph formatting, etc. By setting Enter Key Behavior to New Line in Field, when pressing Enter on a Text Box, you do not move to the next control in the form’s Tab order. Instead, it inserts an Enter character (ASCII code 13) on the text, breaking it to a new paragraph.

Open the unbound frmTextBox_TextFormat in Design mode, click its txtRitchText Text Box, and note that it has the properties Scroll Bars set to Vertical, Can Grow set to Yes, and Can Shrink set to Yes on the Format tab; Text Format set to Rich Text on the Data tab; Enter Key Behavior set to New Line in Field on the Other tab (Figure 4-32).

Change it to Form view, insert some text, and use the Home tab’s Text Formatting area tools to format it the way you want. Then show the form in Print Preview and note how the Can Grow and Can Shrink properties act on the Text Box (Figure 4-33).

Input Mask Property for Password

Using the Input Mask property, you can turn the Text Box into a password collector, where everything that is type appears with an asterisk.

Such an approach is commonly used on a form employed to capture the username and password on most database applications. (I personally dislike applications that use this approach, unless it deals with very sensitive data—or the client who bought it asks for it.)

Open the unbound form frmTextBox_Password in Design view, and you will note that it has only two text boxes: one for the username and other for the password (Figure 4-34).

The LEN() Function

The LEN() function allows you to check the number of characters that was typed on any string value, like a control value or property.

The LEN() function has this syntax:

LEN(String)

where:

• String is a string expression between quotes or an expression that returns a string (like a variable name or property value). If the String argument contains Null, then Null will be returned.

Show frmTextBox_Password in Form view and type a username. If it is greater than 25 characters, the validation rule (Len([txtUserName]) < 26) will be broken, and the validation text will be shown in a dialog box. The same is true for txtPassword, which allows just 14 characters with its validation rule (Len([txtPassword]) < 14, as shown in Figure 4-35.

Attention

Since both Text Boxes use the LEN() function (with the Control Name property between squared brackets as an argument) to limit the text amount, whenever you type and clear the text, when you try to leave the empty control, the validation rule will be broke, because the LEN() function can’t operate over a null Text Box.

Note the following characteristics of frmTextBox_txtPassword:

• It is horizontally centered in the Microsoft Access window, although the property Auto Center is set to Yes, because its Property PopUp is set to Yes.

• No sizeable border, although its Border Style is set to Sizeable because its property Modal is set to Yes.

• No minimize or maximize buttons (Min Max Buttons set to None)

• Since it has no Record Source property, it also has no navigation buttons or record selector because its navigation buttons and record selector were set to No).

• It automatically resize to the Detail section’s height and width (Auto Resize set to Yes).

• It is modal to the Microsoft Access interface—you can’t use Access until it is closed (Modal set to Yes).

• It can float outside the borders of the Microsoft Access interface (Pop Up set to Yes).

Control Source for Expressions and Aggregate Functions

The property Row Source of the Text Box control can also be used to calculate expressions that use a single field record value and to calculate the entire recordset used by the form, which is especially useful when Default View is set to Continuous Form.

If the expression is used in the Detail section, it will refer to the current record; but by putting it on the Form Footer section and using any of the aggregate functions (other than Group By, mentioned in Table 3-7 of Chapter 3), you can create a subtotal expression or statistic for all form records.

To show how it works, this section uses the WEIGHT table records to calculate the individual weight of each element of a food item’s common measure, by dividing Gm_Wgt by Amount and using the expressions inserted into the Text Box controls of the Form Detail and Footer sections.

Open frmWEIGHT_SubTotalsExample in Design view, and note that it has some Text Box controls whose Control Source properties are expressions, so like an Excel formula, they begin with a = sign and relate to other form controls with or without an aggregate function (Figure 4-36).

As Figure 4-36 shows, whenever the Control Source property is selected and you press Shift+F2, Access shows the Zoom window that will help you to build the desired expression.

All Text Box controls inserted in the Form Footer section have an aggregate function to calculate values that refer to all form records.

Since frmWEIGHT_SubTotalsExample’s Record Source property has a SQL instruction that uses the dynamic criteria =Like [NDB_No] & “*”, whenever the form is opened (or requeried after opened by pressing Shift+F9), it will ask for NDB_No or any numeric expression that begins a series of NDB_No records (press Enter without typing a value to return all WEIGHT table records). Figure 4-37 shows the result of typing 10 to show all 610 WEIGHT table records whose NBD_No fields begin with 10, calculating the desired subtotal records and aggregate functions almost instantly.

Using the Combo Box Wizard with a List of Values

Consider, for instance, that you need to offer a form where the user must define a person’s civil status, which is supposed to be described with the words Single, Married, Divorced, and Widowed.

You will be surprised by how these words can vary on a database record whenever you offer a Text Box control to the user for this task: it is surely better to use a Combo Box or List Box from where the desired civil status can be selected.

Also, instead of creating a Civil Status field using a Short Text data type with eight characters (the length of the longest status, Divorced), you decide instead to represent the civil status in your database as a Number, using a Byte field (the smaller data type that uses just 8 bits), where 1 is set to Single, 2 is set to Married, 3 is set to Divorced, and 4 is set to Widowed.

For both strategies, a Combo Box or List Box must be the control of choice to guarantee that the correct civil status be set by the user. The first strategy (text insertion) will need a single-column list, while the second (number insertion using text selection) needs a two-column layout.

Creating a One-Column List of Values Combo Box

Follow these steps to create a Combo Box with a single list of values using the Combo Box Wizard:

1. 1.

   Use the Form Design tool on the Create tab to create a new form in design mode.

    
2. 2.

   Select the Combo Box tool in the controls list of the Form Design tab and click the Form Detail section.

    
3. 3.

   Considering that the Use Control Wizards option is selected, the Combo Box Wizard will open, asking you to indicate from where the list options must come (Figure 4-38).

    

Tip

If you click Cancel to close the wizard, the control will be placed with default values in the form section. The only way to start the wizard again is by deleting the control and inserting another one.

1. 4.

   To create a Combo Box whose items come from a list of values, select the “I will type the values I want” option and click Next.

    
2. 5.

   The Combo Box Wizard will ask for the number of columns in the list and give you a table grid to type the list values. To create a single column list, keep “Number of columns” set to 1 and type in different rows the four possible options: Single, Married, Divorced, and Widowed (Figure 4-39).

    

1. 6.

   Click Next, and the Combo Box Wizard will ask for the text you want to use on the Label control associated to the Combo Box. For this exercise, type Civil Status (Figure 4-40).

    

1. 7.

   Click Finish to create the control in the form’s Detail section and use the Property Sheet window to explore the control form and data properties.

    

The Combo Box Wizard will create the Civil Status Combo Box in the Form section. Alternate to Form view, and click the Civil Status Combo Box arrow to expand its drop-down list and see its values. Select one of its items to define the control value; then press F4 to expand the list and note it is selected in the list. Show the form in Design View or Layout View and explore the Combo Box properties (Figure 4-41).

To set up this one-column list, use the Combo Box Wizard to set these properties:

• Row Source = “Single”; “Married”; “Divorced”; “Widowed”

• Row Source Type = Value List

• Bound Column = 1

• Column Count = 1

• Limit To List = No

• Allow Value List Edits = Yes

• Column Widths = 1”

• List Width = 1”

The property Limit to List being set to No indicates that the Combo Box allows the user to type a civil status not in the list—which may be nonsense. Setting the properties Column Widths, List Width, and Width to 1 sets the width of the first column, list width, and control width.

Since this is a one-column list with the property Allow Value List Edits set to Yes, you can right-click the Combo Box control and select Edit List Items on the context menu to show the Edit List Items dialog box, where you edit, add, or delete the items in the list (Figure 4-42).

Creating a Two-Column List of Values Combo Box

To continue this exercise, let’s consider the alternative where the civil status is stored on the database as Number, where 1 is set to Single, 2 is set to Married, 3 is set to Divorced, and 4 is set to Widowed.

Follow these steps to create a Civil Status Combo Box that uses two columns (civil status code and civil status name):

1. 1.

   Click the Combo Box tool to select it and click an empty space of the form’s Detail section.

    
2. 2.

   On the first Combo Box Wizard page, select “I will type the values I want” and click Next.

    
3. 3.

   On the second Combo Box Wizard page, set “Number of columns” to 2, and on the table grid type into the Col1 column the civil status codes (the values 1, 2, 3 and 4, one per row) and in Col2 the CIVIL STATUS text (the values Single, Married, Divorced, and Widowed, as shown in Figure 4-43).

    

1. 4.

   Click Next, and the Combo Box Wizard will ask you to select which list column will return the Combo Box value. Select Col1 (the Civil Status code, as shown in Figure 4-44).

    

1. 5.

   Click Next to show the last Combo Box Wizard page, type again Civil Status for the text of the Label associated to the Combo Box, and click Finish to create the control.

    

Once again, the Combo Box Wizard will create the Civil Status Combo Box in the Form section. Switch it to Form view, and press F4 or click the Civil Status Combo Box arrow to expand its drop-down list to see its two column values. Select any list item and note that the Combo Box value received the civil status code (Figure 4-45).

To set up this one column list, the Combo Box Wizard set these properties:

• Row Source = 1; “Single”; 2; “Married”; 3; “Divorced”; 4; “Widowed”

• Row Source Type = Value List

• Bound Column = 1

• Column Count = 2

• Limit To List = No

• Allow Value List Edits = Yes

• Column Widths = 1”; 1”

• List Width = 2”

Although Limit to List is set to No and Allow Value List Edits is set to Yes, the list can’t be edited because it has two columns.

Column Widths is set to 1”, 1”, which defines two 1-inch columns, while List Width set to 2” sets the drop-down list width as the sum of each column width, which makes it double the control width (Width set to 1”).

Note

The Combo Box Wizard that uses a two-column value list could do a better job. On the second page (where it asks for the column values), it could have a “Hide key column” option. On its third page (where it asks to select the column that returns the Combo Box value), it could have a “Hide key column” option, so the user can select a civil status expression while the control stores its code.

By repeating the previous two exercises using the List Box control, you will be able to compare how both controls work when using these two methods. Form frmComboBoxListBox_ValueList did it for you and also fixed the two-column Combo Box and List Box to allow both to select a civil status, while Hidden assigns a civil status code to the control value (Figure 4-46).

Combo Box or List Box with Hidden Bound Column

Change frmComboBoxListBox_ValueList to Design view, click cboCivilState3 (located at the right), and show its Property Sheet. Then inspect its Format and Data tabs (Figure 4-47).

Using the Combo Box Wizard with a List of Records

Let’s begin by creating a Combo Box that allows you to show records from a table or query on the Form Detail section.

Creating a Two-Column List of Records Combo Box

Use the Relationships window of the sr28_Forms_Overlapping database to verify that the table’s FD_GROUP (food category records) and FOOD_DES (food item records) share a one-to-many relationship among their FdGrp_CD fields—each FOOD_DES record stores the FdGrp_CD primary key to set the food category association.

Since each FOOD_DES record is associated with a single FD_GROUP record, the FOOD_DES FdGrp_Cd is a good candidate to use a Combo Box to better show its value (Figure 4-48).

This one-to-many relationship is the classic case where a form can be used to create an interface where each FOOD_DES record uses a two-column Combo Box to represent the FdGrp_CD field showing the food category name, instead of its code.

Follow these steps to create a form associated to the FOOD_DES table and insert it in a Combo Box for the FdGrp_CD field to associate it with the FD_GROUP table:

1. 1.

   Use the Form Design tool on the Create tab to create a new form in design mode.

    
2. 2.

   Show the form’s Property Sheet, and on the Data tab, set Form Record Source to FOOD_DES.

    
3. 3.

   Click the Show Existing Fields tool of the Form Design tab to show the Field List window, press and hold the Ctrl key to click the NDB_No and Lng_Desc fields, and drag them to the Form Detail section.

    
4. 4.

   Select the Combo Box tool in the controls list of the Form Design tab, and click and drag the FdGrp_Cd field from the Field List window to the Form Detail section to fire the Combo Box Wizard (Figure 4-49).

    

1. 5.

   On the first Combo Box Wizard page, select the option “I want the Combo box to get the values from another table or query” and click Next.

    
2. 6.

   The wizard will ask you to select a table or query. Select the FD_GROUP table and click Next to show its fields (Figure 4-50).

    

1. 7.

   Click the >> button to select both FD_GROUP Table fields (FdGrp_CD and FdGrp_Desc) and click Next.

    
2. 8.

   The Combo Box Wizard will ask if you want to sort the list items by one of the selected fields. Select FdGrp_Desc for Ascending, and click Next (Figure 4-51).

    

1. 9.

   The Combo Box Wizard will detect that you selected the table primary key (FdGrp_CD) and will use it as the control value, automatically hiding it on the list (checking the “Hide key column (recommended)” option). Click Next (Figure 4-52).

    
2. 10.

   The Combo Box Wizard will ask if the Combo Box value must be associated to a field, already selecting the FdGrp_Cd field (because it was preselected when the control was dragged). Click Next.

    

1. 11.

   Use the last Combo Box Wizard page to associate the Combo Box Label to the words Food Category (the wizard will propose using the field name), and click Finish to create the control in the form’s Detail section (Figure 4-53).

    

Figure 4-54 shows frmComboBox_FOOD_DES that has the Food Category Combo Box created by the Combo Box Wizard and that its list of items is composed by the records from the FD_GROUP table, alphabetically sorted by its FdGrp_Desc field, with all its relevant properties from the Data and Format tabs.

To set up this two-column list associated to the FD_GROUP table, the Combo Box Wizard sets these properties on the control’s Data and Format tabs on its Property Sheet:

• Control Source = FdGrp_Cd

• Row Source = SELECT [FD_GROUP].[FdGrp_CD], [FD_GROUP].[FdGrp_Desc] FROM FD_GROUP ORDER BY [FdGrp_Desc]

• Row Source Type = Table/Query

• Bound Column = 1

• Column Count = 2

• List Rows = 16

• Limit To List = Yes

The Combo Box Wizard always creates a 1-inch control with a 1-inch list of items that allow you to change its value. To improve the control functionality on the form, the next properties were also changed:

• Column Widths = 0; 2.5”

• List Width = 2.5”

• Width = 2”

• Locked = Yes

The Combo Box’s second column width was changed to 2.5 inches, as was its List Width setting (to allow you to select an item without truncating its text), while the control’s Width was set to 2 inches—which is enough to show the food category name.

It also has Locked set to Yes to allow the user to expand the list but not change its value so as not to ruin the Food Item and Food Category association.

To inspect how the wizard brings records to the Combo Box list, click the ellipsis button located to the right of the Row Source property to show the Query Builder with the graphic representation of the SQL instruction used by the control (Figure 4-55).

Attention

The Query Builder is a modal window, meaning you can’t use the Access interface while it is opened.

Tip

Whenever Row Source Type is set to Table/Query, you can use the Row Source arrow to expand a list of database saved queries and select one to be used to fill the control list of items.

As a bonus, open frmComboBoxListBox_QueryRecords that has two columns of Combo Box and List Box controls produced by the wizard, all of them using the FD_GROUP table to fill its lists.

The Combo Box and List Box of the right column have Column Heads set to Yes to show the field names on its first row, and both have the first column unhidden using Property Control Width set to 0,5”, 1”.

The List Box works the same, but a multicolumn Combo Box with two or more visible columns shows a fundamental concept: the first visible column will always appear as the selected list item, while the true control value can be returned by any other list column as defined by its Bound Column property (by setting Bound Column to 2, the true right Combo Box control value shown in Figure 4-56 is the food category name on the second list column, not the food category code).

Manually Creating a Form/Subform Interface

Using the second approach, Microsoft Access will automatically insert a SubForm/SubReport control and set its Source Object property to the name of the dragged form. Let’s look at an example by following the next steps:

1. 1.

   Open frmFOOD_DES_Continuous and note that it is a continuous form, showing many records at a time (Default View set to Continuous Form).

    
2. 2.
   The frmFOOD_DES_Continuous form was created by the More Forms and Multiple Items tools and needs some changes to improve its appearance.

   1. a.

      Set the Form Header BackGround Color to White.

       
   2. b.

      The Form Header Label controls and Form Detail associated Text Box controls were resized to use the desired horizontal space.

       
   3. c.

      Form Footer had its Height property changed to allow it to receive a SubForm/SubReport control.

       
    
3. 3.

   Select frmSubWeight in the Database window and drag it to the frmFOOD_DES_Continuous Form Footer section.

    
4. 4.

   Access will insert the SubForm/SubReport control and set its Source Object property to frmSubWeight, defining it as a subform, and will show a warning message telling that it will set the form’s Default View property to Single Form (Figure 4-65).

    

1. 5.

   Since the tables used for both forms are related by their NDB_No fields (check the Relationships window), Access will automatically set the SubForm control properties Link Master Fields and Link Child Fields to NDB_No field.

    
2. 6.

   Select the master form (click in the form’s top-left corner) and reset Default View to Continuous Form (Figure 4-66).

    

1. 7.

   Switch to Form view and note that frmFOOD_DES_Continuous is a continuous form with another continuous subform in its Form Footer section, automatically synchronizing its records (Figure 4-67).

    

By tweaking some SubForm control Format properties, you can insert another SubForm control on its Form Footer to also show LANG_DESC table records synchronized with the selected food item.

Figure 4-66 shows frmSubFOOD_DES in Design and Form views. It has two SubForm/SubReport controls on its Form Footer section: one for the frmSubWEIGHT and another to frmSubLANG_DESC, synchronizing two subforms as each food item is selected (one for the common measures and another for the Langual descriptors). Afterward, open frmSubFOOD_DES, press and hold the Page Down key to quickly select food items, and note how the two SubForm controls quickly synchronize its records (Figure 4-68)!

And this is not all: open frmFD_GROUP_TwoSubFormLevels, and note that using the same approach, frmSubFOOD_DES was inserted into the form footer, improving considerably the interface proposed by the Form Wizard to frmFD_GROUP shown in Figure 4-64. It now can show more information using almost the same screen size (Figure 4-69).

Using Aggregate Functions in Forms and Subforms

Looking at Figure 4-67, some may argue that by removing the navigation buttons from the forms used on the SubForm/SubReport controls, there is a loss of record information. How many food items are there in each food category? How many common measures and Langual descriptors?

These are reasonable questions that can be solved using the COUNT() aggregate function that can operate on all records shown by a form, which is especially useful when this form has Default View set to Continuous Form (see the section “Control Source for Expressions and Aggregate Functions” earlier in this chapter).

Use as an argument any field on the form’s Record Source property that is guaranteed to be fulfilled for every record: the primary key field.

Form frmSubFOOD_DES_Aggregate uses this trick on all its SubForm/SubReport controls to count the records and show the result in its Form Footer section, using an unbound Text Box with Control Source set to Count([NDB_No]), and a specific Format property that adds a suffix to clearly indicate what is counted (Figure 4-70).

You may note that frmSubFOOD_DES_Aggregate has the frmSub prefix, meaning that it will be used as a subform and that it also doesn’t have navigation buttons. This is because it was inserted in frmFD_GROUP_Aggregate’s Form Footer section as a subform, creating the desired effect of synchronizing records of four different tables and now showing how many records each subform has. Cool, isn’t it? (See Figure 4-71.)

Creating a Pie Chart with the Chart Control

Every time you select the Chart tool and click a form section in Design view, Microsoft Access opens the Chart Wizard, no matter the state of the Use Control Wizards option. In other words, you can’t get rid of it to create your first chart.

Since the Chart Wizard expects to receive data on a row-by-row basis, there is a need to create a query that returns the desired nutrients (protein, fat, and carbohydrate) and the calories each one gives, using different rows for each nutrient record.

We can build such a query by inspecting the Relationships window and noting that we need to use the FOOD_DES table for nutrient codes (NDB_No) and names (Long_Des) and its related NUT_DATA table for nutrient amounts, using a criteria to return just the desired nutrient amounts by its Ntr_No code (Ntr_No).

The IIF() Function

The IIF() function , also known as “immediate IF,” is used to provide a logical test that can return one of two different values and can be used anywhere on an expression.

The IIF() function has this syntax:

IIF(Expression, True_value, False_value)

where:

• Expression: Required; is an expression or value that must be evaluated (0 is considered as False; any other value is considered as True)

• True_value: Required; is a value or expression that is returned when Expression is evaluated to True

• False_value: Required; is a value or expression that is returned when Expression is evaluated to False

Since the IIF() function is used on an expression, it always evaluates both True values and False values, although it returns just one of them. This may lead to unexpected errors, because if one of these arguments generates an error (like a zero division), IIF() will propagate such error.

Of course, you can nest another IIF() function inside the True_value or False_Value, something that can become difficult to follow according to the value that must be returned.

Note

You’ll learn about the CHOOSE() function later in this book for a better alternative of nested IIF() functions.

Open the query qryFOOD_DES_ProtFatCHOCalories and verify how it was built to return the desired information.

• It relates FOOD_DES and NUTR_DATA by its NDB_No field.

• It uses In(“203”,”204”, “205”) on the Criteria option of the Ntr_No field from the NUTR_DATA table to select just the desired nutrients.

• It uses the IIF() function on the Nutrient column expression to return each nutrient name suffixed by the respective nutrient range (click the Nutrient column and press Shift+F2 to better see it in the Zoom window).

  Nutrient: IIf([Nutr_No]="203","Protein 10-35%", IIf([Nutr_No]="204","Fat 20-35%","Carbohydrate 45-65%"))

Tip

This expression uses two nested IIF() functions (also known as an immediate IF) to check the record Ntr_No value and return the desired nutrient name. The IIF() function needs three arguments, separated by commas, and has this syntax:

IIF(<Test>, <value for Test=True >, <value for Test=False>)

The first IIF(<Test>,…) verifies whether Ntr_No is set to 203, and if it’s True, it returns the argument when Test is set to True (“Protein 10-35%”), but if it’s False, it uses another (nested) IIF() function on the argument when Test is set to False to verify whether Ntr_No is set to 204. If it’s True, it returns Fat 20-35. If it’s False, it returns Carbohydrate 45-65%.

Attention

It is important to note that whenever you need to nest IIF() function to test n conditions, you will need n-1 nested IIF() functions, which can turn your expression into a mess. As an alternative, you can use the CHOOSE() function, which will be explained later in the chapter.

• It also uses the IIF() function to build this expression for its Calories column (to multiply the nutrient’s Ntr_Val field by the respective calories amount it has):

  Calories: [Nutr_Val]*IIf([Nutr_No] In ("203","204"),4,9)

Show qryFood_DES_ProtFatCHOCalories in Datasheet view, and note how each food item has three records, each one using the desired nutrient name and adequate range, so the pie chart can be easily built (Figure 4-72).

Note

There is no need to calculate the percentage of calories that came from each of these three nutrients (by dividing each nutrient value by the sum of their values), because the Microsoft graph pie chart can automatically give this information by using data series values with the Percent option set.

Follow these steps to create a pie chart with the Chart Wizard using the qryFood_DES_ProtFatCHOCalories query:

1. 1.

   Use the form’s Design tool to create a new form.

    
2. 2.

   Set the form’s Record Source property to the FOOD_DES table, show the Field List window, and drag the fields NDB_No and Long_Desc to the form’s Detail section.

    
3. 3.

   Select the Chart tool in the controls list of the form’s Design tab and click the Form Detail section to start the Chart Wizard.

    
4. 4.

   On the first page of the Chart Wizard tool, select Queries in the View area, choose the qryFood_DES_ProtFatCHOCalories query in the list, and click Next to continue (Figure 4-73).

    

1. 5.

   The second Chart Wizard page asks which fields you want to use on the chart. Double-click the Nutrient and Calories fields (or select them and use the > button) to add them to the Fields to Chart list and click Next.

    
2. 6.

   The third Chart Wizard page asks which type of chart you want to create. Click the pie chart and click Next.

    
3. 7.

   The fourth Chart Wizard page asks how you want to use the selected fields, adequately proposing Nutrient as the series name (in the upper right of the chart preview) and SumOfCalories as the series values. Accept the default values and click Next.

   Tip On this Chart Wizard page, you can drag the selected fields to the indicated positions of the chart preview. Double-click field SumOf Calories to change the aggregate function used to the Total field value (if any).

    
4. 8.

   The fifth Chart Wizard page will detect the fields used on the form’s Record Source property and in the query selected for the chart and, because they are related in the Relationships window, will propose them for the Chart control’s Link Master Fields and Link Child Field properties (it adequately proposes the NDB_No field for both properties). Click Next (Figure 4-74).

    

1. 9.

   The last Chart Wizard page asks for a title to be used on your chart and if a legend must be exhibited. Type Calories Breakdown as the chart title, keep “Yes, display a legend selected,” and click Finish to close the wizard and insert the chart in the Detail section.

    

After the chart is created, select Form view, use the navigation buttons to change the form records, and note how the chart updates as each record is shown (Figure 4-75).

Tip

For the Chart control’s Row Source property, the Chart Wizard sets by default a Total Query SQL instruction and may define the Link Master Field and Link Child Fields properties. To use all the query records on the chart, remove both control properties, click the ellipsis at the right of the Chart control’s Row Source property to open the Query Design window, and change the Total query to a Select query.

Note

This form is available as Form2 in the sr28_Forms_Overlapping.accdb file.

Creating a Column Chart with the Chart Control

According to what is currently proposed by the FDA for the daily values required of a nutrient, an adult needs to consume at least 50g of protein, 78g of total fat, and 275g of total carbohydrates.²

So, the %Daily Values chart requires a query similar to the one used for Calories Breakdown that calculates the %DV for any food item, dividing its Nutr_Val field from the NUTR_DATA table by the proposed FDA Daily Value for each nutrient. But in this case, we can use another powerful function to better select what value must be returned: the CHOOSE() function.

The CHOOSE() FUNCTION

The CHOOSE() function allows you to select a single value from a list of up to 100 different arguments, constituting a far better alternative than nesting IIF() functions.

The CHOOSE() function has this syntax:

Choose(Item_index , Item_1, Item_2 ... Item_n )

where:

• Item_index: Required; this is an integer or expression that returns an integer value between 1 and the number of available items. If Item_Index is not an integer value, it will be rounded to the nearest integer.

• Item_1, Item_2, Item_n: These are values or expressions that indicate all possible returned valued.

The CHOOSE() function checks the Item_Index value and returns the associated value on its list of choices. For example, if Item_index is set to 2, CHOOSE() will return Item_2 value. It will return NULL if Item_Index is less than 1 or greater than the number of choices listed.

Like IIF(), the CHOOSE() function evaluates every possible item before returning the desired value. If any of these items returns an error, this error will be propagated by the CHOOSE() function.

Open the query qryFOOD_DES_ProtFatCHO%DV and note that it uses the same query strategy, with the difference that it now uses the CHOOSE() function for the expressions needed to generate the Nutrient and %DV fields (click each field and press Shift+F2 to show the Zoom window, as shown in Figure 4-77).

The Nutrient field records are suffixed with the recommended FDA Daily Value using this expression:

Nutrient: Choose(Val([Nutr_No])-202,"Protein 50g","Fat 78g","Carbohydrate 275g")

while the %DV field for each nutrient uses this expression:

%DV: [Nutr_Val]/Choose(Val([Nutr_No])-202,50,78,275)

Note

The Query expressions use the VAL() function that converts a numeric string into a numeric value (the Nutr_No field has the Short Text data type). Since Nutr_No returns three consecutive values (203, 204, and 205), Val([Nutr_Val]) - 202 returns 1, 2, or 3, which allows you to compose the desired nutrient name or to divide Nutr_Val by the adequate daily value.

Follow these steps to create a column chart with the Chart Wizard using the qryFood_DES_ProtFatCHO%DV query:

1. 1.

   Use the Form Design tool to create a new form and set its Record Source property to the FOOD_DES table, adding the fields NDB_No and Lng_Des to the form’s Detail section.

    
2. 2.

   Select the Chart tool on the controls list of the Form Design tab and click the form’s Detail section to start the Chart Wizard.

    
3. 3.

   On the first page of the Chart Wizard tool, select Queries in the View area, choose the qryFood_DES_ProtFatCHO%DV query on the list, and click Next to continue.

    
4. 4.

   On the second Chart Wizard page, double-click the fields Nutrient and %DV fields (or use the > button) to add them to the Fields for Chart list, and click Next.

    
5. 5.

   On the Third Chart Wizard page, select the first column chart and click Next.

    
6. 6.

   On the fourth Chart Wizard page, drag the Nutrient field from the X-Axis to the Series option and click Next (Figure 4-78).

    

1. 7.

   On the fifth Chart Wizard page, accept NDB_No as the fields used on the Form Record Source property and click Next.

    
2. 8.

   On the last Chart Wizard page, type %Daily Values for the chart title, keep the Yes, display a legend option selected, and click Finish to insert the chart on the Form Detail section (Figure 4-79).

    

Note

This form is available as Form3 in the sr28_Forms_Overlapping.accdb file.

Customizing the Column Chart

The Column chart created by the Chart Wizard has a standard appearance that can be improved by customizing many of its elements, which is made by double-clicking the Chart control in Design view to fire the Microsoft Graph applet and put the chart in Edit mode, where you can select the desired chart element and double-click it to change it as you like (almost the same way as when using Excel).

Figure 4-80 shows the frmFOOD_DES_Column_Chart tool form, where this chart receives a new appearance by changing these chart settings:

• The legend was dragged to the bottom of the chart.

• The Y-Axis Format was changed to Percent, with 0 decimals.

• The Series Data labels Values was shown using Format Percent with two decimals (double-click each data series and use the Format Data Series dialog box).

• The Series color was changed by clicking two times on the bar to select it and then double-clicking to change its border and background colors (this can also be done by double-clicking the associated small square on the chart legend).

• The X-Axis title was removed (double-click the X-Axis and use the Font tab to apply a white font color—the same as chart background).

• The default horizontal grid lines were removed (click them in the chart area and uncheck the Value Axis Grid line tool on the Chart toolbar or use the Chart ➤ Chart Options command to show the Chart Options dialog box).

Tip

Whenever you use a Select query to create a column chart, which series values comes from different records grouped by a single record field (in this case the NDB_No field group different nutrients for the same food item), the Chart engine will not allow you to insert space between the bars chart because it considers them as a unit. To create a column chart that allows separate bars, use a Crosstab query (like qryFOOD_DES_ProtFatCHO%DV_Crosstab) where the field names define the chart series.

Creating a Modern Pie Chart with the Insert Chart Tool

The Insert Chart tool was designed by the Access Development team to somewhat approximate in the Microsoft Access interface what Excel has done since Microsoft Office 2007: a new interface to create and present charts.

“Modern charts” mainly use by default the Century Gothic font (instead of Arial used by the Chart control) and offer more color possibilities than the ones used by the Microsoft Graph applet. In other words, the Insert Chart tool creates a chart with a more modern appearance than the ones created by the Chart tool.

That said, the charts created with the Insert Chart tool are a totally different beast, with no Chart Wizard, using two different interfaces (the Chart Settings and Property Sheet windows). The Chart objects created are not intuitive (you can’t select chart elements on the control using the mouse or double-clicking) and lack lots of common personalization settings.

The pie charts created with the Insert Chart tool accept only queries whose series values are presented on a row-by-row basis, and there is no option to transform the data values to a percent of its total, like the Chart control does. You have to calculate the desired percent values using a query.

By relating both queries using the NDB_No field, qryFOOD_DESProtFatCHO%Calories calculates the Calories Breakdown values each nutrient has, allowing it to be used on a modern pie chart created with Insert Chart tool (Figure 4-81).

The Insert Chart option has no wizard to guide you, although the process is somewhat simplified after you are introduced to it.

Follow these steps:

1. 1.

   Use the Form Design tool to create a new form and set its Record Source property to the FOOD_DES table, adding fields NDB_No and Long_Des to the form’s Detail section.

    
2. 2.

   Click the Insert Chart tool on the form’s Design tab to expand it, select the pie chart, and click the form’s Detail section to insert a new modern Chart object and show the Chart Setting window with the data area selected (Figure 4-82).

    

1. 3.

   Select query qryFOOD_DESProtFatCHO%Calories in the Data Source box of the Chart Settings window to expand its Axis (Category), Legend (Series), and Value (Y axis) options showing all the selected query fields.

    
2. 4.
   Check these field names in the Chart Settings window to define the pie chart:

   1. a.

      Axis (Category): Nutrient

       
   2. b.

      Value (Y axis): Calories

      Note The Chart Setting Legend (Series) option may be used whenever you want that the series name to come from a field different from the one selected on the Axis (Category) option.

       
    
3. 5.

   Click the Format option at the top of the Chart Settings window and check the Display Data Labels option.

    
4. 6.

   Show the Property Sheet window, select the Data tab, and click the ellipsis button to the right of the Link Master Fields (or Link Child Fields) property to show the Subform Filter Linker window where you will select the NDB_No field to allow a form record-by-record synchronization (Figure 4-83).

    

After the Insert Chart tool is used to create a modern chart, show the form in Form view, use the navigation buttons to change the records, and note how they synchronize with the “modern” chart (you may need to change the Chart control’s dimensions in Design view, as shown in Figure 4-84).

Customizing the Modern Pie Chart

To customize the “modern” pie chart, you need to select the Chart control in Design view and use its Property Sheet to set some of its (many) properties to manually change the desired chart elements.

Figure 4-85 shows the chart reformatted after setting Properties Chart Title to Calories Breakdown and Primary Axis Format to Percent, which now exhibits the correct chart title and values using the adequate percent format (Figure 4-85).

Note

Since the pie chart has no axes, it seems inadequate that both interfaces (Chart Setting and Property Sheet) offer such settings for a pie chart—among many other properties that should be shown for this type of chart.

Creating a Column Clustered Chart with the Insert Chart Tool

Use the Clustered Column option of the Insert Chart ➤ Column tool to create the %Daily Values column chart.

Contrary to the limitations of the “modern” pie chart, the Clustered Column chart created with the Insert Chart tool accepts data on a row-by-row basis (a Select query where different field record values name the series) or on a column-by-column basis (a Crosstab query that uses just one record and uses its Field names to name the series).

Follow these steps to make to create the %Daily Values column chart using the Insert Chart tool:

1. 1.

   Create a new form, set Property Record Source to the FOOD_DES table, and add the fields NDB_No and Lng_Des to the form’s Detail section.

    
2. 2.

   Select Insert Chart ➤ Column ➤ Clustered Columns and click the Form Detail section to insert a new modern Chart object and show the Chart Settings window with the data area selected (Figure 4-86).

    

1. 3.
   Set these options in the Chart Settings window’s Data area:

   1. a.

      Set Data Source to qryFOOD_DES_ProtFatCHO%DV.

       
   2. b.

      Set Legend to Nutrient.

       
   3. c.

      Set Values (Y axis) to %DV (Sum).

       
    
2. 4.

   Select the Chart Settings window format, use the Data Series box to select each nutrient name (Carbohydrate 275g, Fat 78g, and Protein 50g), and check its Display Data Label option.

    
3. 5.

   Show the Property Sheet window, select the Data tab, and click the ellipsis button to the right of the Link Master Fields property to show the Subform Filter Linker window to automatically select the NDB_No field for record-by-record synchronization.

    
4. 6.
   Select the Property Sheet Format tab and set these properties:

   1. a.

      Set Chart Title to %Daily Values.

       
   2. b.

      Set Primary Values Axis Format to Percent (Figure 4-87).

       
    

After inserting the modern chart, select Form view and note how the modern column chart synchronizes the chart values as the records change (Figure 4-88).

Open frmFOOD_DES_Chart, and note that it has pie and column charts created with the Chart control and Insert Chart tool. The two left charts were created using the Chart Wizard and had many of its properties changed to make them more similar to the ones created with the Insert Chart tool (on the right). The most notable difference is the color palette that is limited on the Chart tool.

Also note that both column charts use a Crosstab query to separate its bars (both use qryFOOD_DES_ProtFatCHO%DV_Crosstab). Switch to the form’s Design view, click (or double-click) each chart, and study their properties to better understand how each one works. See Figure 4-89.