06-forms.qmd

# Forms {#sec-forms}

::: callout-intro
-   Types of forms: main form, subform and linked form.
-   Creating forms: automatic and manual.
-   Working with controls.
-   Performing calculations in a form.
-   Forms with charts.
:::

Forms provide organized and formatted views of data from tables and queries. They are especially useful for data entry.

## Creating Forms {#sec-forms-about}

While it's possible to enter data directly into tables, this is generally only practical for very simple tables. Typically, forms are used for data entry. Basic forms often display data from a single table or query. More advanced forms, such as main-subform combinations or linked forms, draw data from related tables or queries..

Beyond simply giving your forms an attractive appearance, the key advantage is the ability to apply various validations during data entry. Forms also support a variety of controls such as list boxes, checkboxes, buttons, and more.

You can build a form from scratch, let Access generate one for you, or use the [Form Wizard]{.uicontrol} and then adjust the generated form manually.

### Main and Subforms {#sec-forms-explanation-mainsub}

A **subform** is a form embedded within another form, known as the **main form**. Subforms are typically used to display data from tables or queries with a one-to-many relationship. The "one" side appears in the main form, and the "many" side in the subform.

::: {#exm-forms-mainsubform}
**Orders by Customer**

-   **Main form:** Displays customer data from the [Customer]{.varname} table.
-   **Subform:** Displays the customer's orders from the [Orders]{.varname} table.

The main and subform are linked so that the subform only shows records related to the current main form record.

![ustomers main form with Orders subform.](images/forms/f-customers-orders.png){#fig-f-customers-orders-1}
:::

### Linked Forms {#sec-forms-explanation-linked}

Linked forms are separate forms that interact with each other. One form—usually the main form—contains a command button that opens another form when clicked. The records in both forms are synchronized. The button includes VBA code to open the second form, which is automatically generated by the wizard. No programming knowledge is required.

Whether you use linked forms or subforms depends on user preferences. If users only need basic customer data, there's no need to display order information simultaneously. A button that opens the order details form is often more convenient.

![Two linked forms with synchronized customer and order data.](images/forms/f-customerorders-linked.png){#fig-f-customerorders-linked-1}

## Controls and Layouts {#sec-forms-controls-layout}

#### Controls

Controls are elements in a form or report that allow you to enter, display, or manipulate data. The **Text Box** is the most commonly used control for displaying data. Other frequently used controls include command buttons, checkboxes, and combo boxes.

Controls can be:

-   **Bound:** Connected to a field in a table or query; used to display data from that source.
-   **Unbound:** Not connected to any data source; used for static text, images, lines, etc.
-   **Calculated:** Displays a value based on an expression rather than a data field.

#### Layouts

Layouts help align controls horizontally and vertically, giving your form a consistent look. Two layout types are commonly used:

-   **Tabular Layout:** Controls are arranged in rows and columns like a spreadsheet, with labels across the top.

    ![Example of a tabular layout.](images/forms/layout-controls-tabular.png){#fig-layout-controls-tabular}

-   **Stacked Layout:** Controls are arranged vertically with labels to the left.

    ![Example of a stacked layout.](images/forms/layout-controls-stacked.png){#fig-layout-controls-stacked}

#### Edit Controls

Since you often need to rearrange controls, it's important to understand how to move and modify them. Many operations can be performed on multiple controls at once. Make sure the [Property Sheet]{.uicontrol} is visible before editing.

-   **Select a control:** Click on it.
-   **Select multiple controls:** Hold down the Shift key and click each control, or drag a rectangle around them.
-   **Deselect:** Click an empty area of the form.
-   **Move:** Drag the control using the upper-left handle. You can also set an exact position in the [Property Sheet]{.uicontrol}.
-   **Resize:** Drag the middle or corner handles, or set dimensions via the [Property Sheet]{.uicontrol}.
-   **Align:** Use [tab Arrange \> Align (Sizing & Ordering group)]{.uicontrol}.
-   **Delete:** Press the [Delete]{.uicontrol} key.

## Automatically Generated Form {#sec-forms-autoform}

When new records are added to a table, typically all fields must be filled. A form containing all fields is the easiest way to do this. The fastest method is to select the table and use [Form]{.uicontrol} from the \[Create\] tab to auto-generate a form. You can then modify it as needed.

In this task, you'll create a form for a warehouse employee to update box stock and indicate if a box is out of production. Other box details should not be editable through this form. The desired result looks like this:

![Stock-in-trade form for warehouse data updates.](images/forms/f-stock-in-trade-result.png){#fig-f-stock-in-trade-result}

::::::: {#prp-forms-autoform}
[File]{.smallcaps}: `{{< var database >}}`

1.  Open the database.

2.  Select the [Boxes]{.uicontrol} table (no need to open it).

3.  Click [Create \> Form (Forms group)]{.uicontrol}. A form is created and displayed in [Layout View]{.uicontrol}.

::: {.callout-note title="Subdatasheet added automatically"}
If Access detects a one-to-many relationship between your selected table/query and another table, it adds a subdatasheet for that related table. If multiple such tables exist, no subdatasheet is added.

In this case, [Boxes]{.varname} has a one-to-many relationship with [OrderDetails]{.varname}, so a subdatasheet appears showing related [OrderDetails]{.varname} records.

If you don’t want this, you can delete the subdatasheet in [Layout View]{.uicontrol} by selecting it and pressing [Delete]{.uicontrol}.
:::

4.  Select the subdatasheet and press [Delete]{.uicontrol}.

5.  Switch to [Design View]{.uicontrol}.

![Stock-in-trade form in Design View.](images/forms/f-stock-in-trade-design.png){#fig-f-stock-in-trade-design}

::: {.callout-note title="Stacked layout"}
Automatically created forms include two controls per field: a [Text Box]{.uicontrol} and a linked [Label]{.uicontrol}.

![Text box control with a linked label.](images/forms/textbox-label.png){#fig-textbox-label}

By default, all controls are arranged in a stacked layout, meaning you can’t freely move them. To move a control freely, remove its layout using [Arrange \> Remove Layout (Table group)]{.uicontrol}.
:::

6.  In the [Form Header]{.uicontrol}, change the label from "Boxes" to "Stock-in-trade". Set [Font Weight]{.uicontrol} to `Bold` and [Font Size]{.uicontrol} to `20 pt`.

::: {.callout-tip title="Property Sheet"}
You can show or hide the [Property Sheet]{.uicontrol} using the \[Property Sheet\] button or the [F4]{.uicontrol} hotkey. To find properties more quickly, sort them alphabetically using the ![](images/common/button-sort-ascending.png) button in the top right.
:::

7.  Select all text boxes and set the [Width]{.uicontrol} property to `3"`. Click elsewhere to deselect.

![Setting the Width property for text boxes.](images/forms/property-textboxes-width.png){#fig-property-textboxes-width}

::: {.callout-note title="Locked property"}
The [Locked]{.uicontrol} property makes a control read-only.
:::

8.  Select all text boxes except [Stock]{.uicontrol} and [OutOfProduction]{.uicontrol}. Set their [Locked (tab Data)]{.uicontrol} property to `Yes`.

![Setting the Locked property for selected text boxes.](images/forms/property-textboxes-locked.png){#fig-property-textboxes-locked}

9.  While the fields are still selected, change their background color to light gray by either:

    -   Setting [Back Color]{.uicontrol} directly.
    -   Right-clicking and choosing [Fill/Back Color]{.uicontrol} from the shortcut menu.

10. Switch to [Form View]{.uicontrol} to check the result.

11. Close the form and save the changes.

12. Name the form [Stock-in-trade]{.varname} and click [OK]{.uicontrol}.
:::::::

## Main and Subform {#sec-forms-customers-orders}

The easiest way to create a main form with a subform is by using the [Form Wizard]{.uicontrol}. This wizard generates both forms and handles the linking automatically.

The goal is to create a form that displays customer information (code, name, address) and, additionally, shows their corresponding orders (order code, date, and subtotal).

Customer data will appear in the main form and is sourced from the [Customers]{.varname} table. The subform will show order data: [OrderCode]{.varname}, [OrderDate]{.varname}, and a calculated [Subtotal]{.varname}. The subtotal is derived from the fields [Quantity]{.varname} and [BoxPrice]{.varname} in the [Boxes]{.varname} table.

To calculate the subtotal, you'll use a query that contains all necessary fields and serves as the data source for the subform. A ready-made query named [Orders with subtotals]{.varname} is already available. It includes the [CustomerCode]{.varname} field to link the main and subform.

:::: {#prp-forms-mainsub}
[File]{.smallcaps}: `{{< var database >}}`

1.  Open the database.

2.  Go to [tab Create \> Form Wizard (Forms group)]{.uicontrol}.

3.  From [Table: Customers]{.uicontrol}, add the following fields: [CustomerCode]{.varname}, [FirstName]{.varname}, [LastName]{.varname}, [Address]{.varname}, [ZipCode]{.varname}, [City]{.varname}, [Telephone]{.varname}.

![Form Wizard: Selecting fields from the Customers table.](images/forms/f-wizard-t-customers.png){#fig-f-wizard-t-customers-1}

4.  Next, select [Query: Orders with subtotals]{.uicontrol} and add: [OrderCode]{.varname}, [OrderDate]{.varname}, [Subtotal]{.varname}. Click [Next]{.uicontrol}.

![Form Wizard: Choosing how to display data for the subform.](images/forms/f-wizard-customers-orders-dataview.png){#fig-f-wizard-customers-orders-dataview}

5.  Review the grouping structure and click [Next]{.uicontrol}. Select [Datasheet]{.varname} as the subform layout.

6.  Click [Next]{.uicontrol}. You’ll be prompted to enter titles for the forms.

7.  Set the form titles as follows:

    -   Form title: "Customers and Orders"
    -   Subform title: "Customers and Orders Subform"

8.  Click [Finish]{.uicontrol}.

![Customers and Orders form with datasheet subform.](images/forms/f-customers-orders.png){#fig-f-customers-orders-2}

::: {.callout-note title="Modify form layout"}
You can modify the layout and design of both the main form and subform as needed.
:::
::::

## Customer Data Entry Form {#sec-forms-customers-data-entry}

In this task, you'll create a form for adding new customers and editing existing ones. You'll also practice moving and aligning controls to match a specific layout:

![Customer data entry form.](images/forms/f-customer-entry.png){#fig-f-customer-entry}

::::: {#prp-forms-customers}
[File]{.smallcaps}: `{{< var database >}}`

1.  Open the database.

2.  Select the [Customers]{.uicontrol} table (no need to open it).

3.  Click [Create \> Form (Forms group)]{.uicontrol}. The form opens in [Layout View]{.uicontrol}.

::: {.callout-note title="Subdatasheet added automatically"}
Since [Customers]{.varname} has a one-to-many relationship with [Orders]{.varname}, a subdatasheet based on [Orders]{.varname} is added automatically.
:::

4.  Delete the subdatasheet by selecting it and pressing [Delete]{.uicontrol}.

5.  Switch to [Design View]{.uicontrol}.

6.  Remove the form logo in the header.

7.  Save the form as [Customer entry and modification]{.varname}.

8.  Increase the height of the [Form Header]{.uicontrol} by dragging the top of the [Detail]{.uicontrol} section downward.

9.  Change the title label from "Customers" to "Chocolate Connoisseur". Set the font to `MS Sans Serif`, `14 pt`, `italic`, and `bold`.

10. Insert a new label via [Design \> Label (group Controls)]{.uicontrol} and place it below the title. Type "Customer entry and modification" and format it with `MS Sans Serif`, `14 pt`, `bold`, `black`.

11. Select all controls in the [Detail]{.uicontrol} section by dragging a rectangle around them.

12. Click [Arrange \> Remove Layout (Table group)]{.uicontrol} to unstack the layout.

13. Deselect the controls.

14. Arrange the controls to match the desired layout.

::: callout-tip
-   Align controls using [Arrange \> Align (group Sizing & Ordering)]{.uicontrol}.
-   Add a new [Label]{.uicontrol} called `Name:` before the first and last name fields.
-   Adjust size using [Width]{.uicontrol} and [Height]{.uicontrol}.
-   Position controls using [Top]{.uicontrol} and [Left]{.uicontrol}.
-   Set background color using [Back Color]{.uicontrol}.
:::

15. Switch to [Form View]{.uicontrol} to test the layout

16. ave and close the form.
:::::

## Praline Data Entry Form {#sec-forms-pralines-data-entry}

This task involves creating a form to add or edit pralines. You'll also replace three text boxes with combo boxes for better usability. The final layout should look like @fig-f-praline-entry.

![Praline data entry form with combo boxes.](images/forms/f-praline-entry.png){#fig-f-praline-entry}

::: {#prp-forms-pralines}
[File]{.smallcaps}: `{{< var database >}}`

1.  Open the database.

2.  Select the [Pralines]{.uicontrol} table (no need to open it).

3.  Click [tab Create \> Form (Forms group)]{.uicontrol}.

4.  Switch to [Design View]{.uicontrol}.

5.  Delete the [BoxDetails]{.uicontrol} subdatasheet.

6.  Remove the form logo in the header.

7.  Save the form with as [Praline information]{.varname}.

8.  Change the header text format to: `Calibri`, `22 pt`, `bold`, `white color` on a `green background`.

9.  Remove the stacked layout by selecting all controls in the [Detail]{.uicontrol} section and choosing [Arrange \> Remove Layout (Table group)]{.uicontrol}.

10. Deselect all controls.

11. Delete the fields [ChocolateType]{.varname}, [FillingType]{.varname}, and [NutType]{.varname}. Reorganize the remaining fields to match the layout.

12. Use [Design \> Line (group Controls)]{.uicontrol} to draw a horizontal line under [PralineCode]{.varname} and [PralineName]{.varname}. Set line thickness to `2 pt` and match the header background color.

13. Add a combo box for [ChocolateType]{.varname} using the [List Box Wizard]{.uicontrol} with these settings:

    -   Enter values manually.
    -   One column with values: `Butterscotch`, `Milk`, `Dark` and `Toffee`.
    -   Store value in [ChocolateType]{.varname}.
    -   Label: "Chocolate type:"

14. Create a [Combo Box]{.uicontrol} for [FillingType]{.varname} with values: `Amaretto`, `Blueberry`, `Cherry cream`, `Coconut`, `Marzipan`, `Mocha cream`, `None`.

15. Create a [Combo Box]{.uicontrol} for [NutType]{.varname} with values: `Almond`, `Cashew`, `Hazelnut`, `Macadamia`, `Paranut`, `Pecan`, `Pistachio`, `Walnut`, `None`.

16. Switch to [Form View]{.uicontrol} and test the form.

17. Close the form and save the changes.
:::

## Boxes Data Entry Form {#sec-forms-boxes-data-entry}

In this task, you’ll replace a [Text Box]{.uicontrol} on an existing subform with a [Combo Box]{.uicontrol} that uses a query as its data source.

The existing [Boxes]{.varname} form allows you to select pralines only by their [PralineCode]{.varname}. The goal is to enable selection by praline name as well.

The [Boxes]{.varname} form consists of a main form ([Boxes]{.varname}) and a subform ([Boxes subform]{.varname}). Praline selection happens in the subform. Therefore, only the subform needs to be modified—specifically, replacing the [PralineName]{.uicontrol} text box with a [Combo Box]{.uicontrol}.

However, the [Boxes subform]{.varname} is based on the [BoxDetails]{.varname} table, which does not contain the [PralineName]{.varname} field. To support name selection, you first need to create a query that includes both [PralineName]{.varname} and [PralineCode]{.varname}, and sorts the results alphabetically by name. This new query will be the data source for the combo box.

:::: {#prp-forms-boxes}
[File]{.smallcaps}: `{{< var database >}}`

**Query: [Choose praline]{.varname}**

1.  Open the database.

2.  Go to [Create \> Query Design (Queries group)]{.uicontrol}. Add the [Pralines]{.varname} table. Add the fields [PralineName]{.varname} and [PralineCode]{.varname}. Sort by [PralineName]{.varname} in ascending order.

![Query design for selecting pralines by name.](images/forms/q-choose-praline-design.png){#fig-q-choose-praline-design}

3.  Save the query as [Choose praline]{.varname} and close it.

**Modify the Subform**

4.  Open the [Boxes subform]{.varname} in [Design View]{.uicontrol}.

5.  Delete the [PralineName]{.uicontrol} field from the [Detail]{.uicontrol} section.

6.  Go to [Design \> Combo Box (Controls group)]{.uicontrol} and draw a rectangle where the [PralineName]{.varname} field was. The [Combo Box Wizard]{.uicontrol} starts.

7.  Answer the wizard prompts as follows:

    -   Choose: [I want the values to come from another table or query]{.uicontrol}.
    -   Select the [Choose praline]{.uicontrol} query.
    -   Add the fields [PralineName]{.varname} and [PralineCode]{.varname}.
    -   Sort by [PralineName]{.varname} (ascending).
    -   Adjust column widths if needed.
    -   Store the value from [PralineCode]{.varname} into the [Code]{.varname} field.
    -   Accept the default label (this will be removed later).

8.  Delete the auto-generated label, as a label already exists in the form header.

![Subform with Combo Box for praline selection.](images/forms/f-boxes-subform.png){#fig-f-boxes-subform}

9.  Select the [Combo Box]{.uicontrol} and check the [Limit To List]{.uicontrol} property in the [Property Sheet]{.uicontrol} (tab Data). If it’s not set to `Yes`, change it.

::: {.callout-note title="Limit To List property"}
Setting “Limit To List” to `Yes` ensures that only predefined praline names can be selected, new names cannot be entered manually.
:::

10. Save and close the [Boxes subform]{.varname}.

11. Open the main [Boxes]{.varname} form and test the combo box functionality.
::::

## Orders Data Entry Form {#sec-forms-orders-data-entry}

To enter new orders and view existing ones, you'll create an order form that includes both main form data and order details in a subform.

![Order data entry form with subform for order details.](images/forms/f-orderdata-entry.png){#fig-f-orderdata-entry}

-   The **main form** will display data from the [Customers]{.varname} and [Orders]{.varname} tables.
-   The **subform** will show data from [OrderDetails]{.varname} and [Boxes]{.varname}, including a calculated [Total]{.varname} field.

To supply data to these forms:

-   Create a query [Orders-Customers]{.varname} that combines the necessary fields from [Orders]{.varname} and [Customers]{.varname}.
-   Create a query [OrderDetails-Boxes]{.varname} combining data from [OrderDetails]{.varname} and [Boxes]{.varname}, with the [Total]{.varname} calculated as `Quantity * BoxPrice`.

Then use the [Form Wizard]{.uicontrol} to create the form and subform based on these queries.

:::: {#prp-forms-orders}
[File]{.smallcaps}: `{{< var database >}}`

**Create Query: [Orders-Customers]{.varname}**

1.  Open the database.

2.  Go to [Create \> Query Design (Queries group)]{.uicontrol}. Add \[Orders\] and \[Customers\].

3.  Add all fields from tbothtables.

::: {.callout-tip title="Adding all fields"}
You can use `*` to add all fields from a table.
:::

![Query design combining Orders and Customers.](images/forms/q-orders-customers-design.png){#fig-q-orders-customers-design}

4.  Save the query as [Orders-Customers]{.varname} and close it.

**Create Query [OrderDetails-Boxes]{.varname}**

5.  Go to [tab Create \> Query Design (group Queries)]{.uicontrol}. Add tables [OrderDetails]{.uicontrol} and [Boxes]{.uicontrol}.

6.  Add all fields from both tables.

7.  Add a calculated field: `Total: [Quantity]*[BoxPrice]`.

![Query design combining OrderDetails and Boxes, including a calculated Total field.](images/forms/q-orderdetails-boxes-design.png){#fig-q-orderdetails-boxes-design}

8.  Save the query as [OrderDetails-Boxes]{.varname} and close it.

**Create the Form and Subform**

9.  Go to [tab Create \> Form Wizard]{.uicontrol}.

10. First, select [Orders-Customers]{.uicontrol} and add all fields **except** [Orders.CustomerCode]{.varname}.

11. Then select [OrderDetails-Boxes]{.uicontrol} and add: \[Boxes.BoxCode\], \[BoxName\], \[Quantity\], \[BoxPrice\], \[Total\]

12. Click [Next]{.uicontrol}, and choose to view the data by [Orders-Customers]{.varname}. This makes it the main form, with [OrderDetails-Boxes]{.varname} as the subform.

13. Click [Next]{.uicontrol} again and select the [Tabular]{.uicontrol} layout for the subform.

14. Choose a style and click [Next]{.uicontrol}.

15. Enter the form names:

    -   Main form: [Orderdata main\`]{.varname}
    -   Subform: [Orderdata sub\`]{.varname}

16. Click [Finish]{.uicontrol}. The form opens automatically.

17. Adjust the layout of both forms to achieve the desired final design.
::::

## Total Order Amount {#sec-forms-orders-total}

In this section, you’ll enhance the form created in @sec-forms-orders-data-entry by calculating and displaying the total amount of an order in the subform footer.

![Orders with total amount.](images/forms/f-orderdata-entry-totals.png){#fig-f-orderdata-entry-totals}

![Order form with calculated total amount.](images/forms/f-orderdata-entry-totals.png)

::: {#prp-forms-orderamount}
[File]{.smallcaps}: `{{< var database >}}`

1.  Open the database.

2.  Open the [Orderdata sub]{.uicontrol} form in [Design View]{.uicontrol}.

3.  In the [Property Sheet]{.uicontrol}, set the selection type to [Form]{.uicontrol}. Change the [Default View]{.uicontrol} to [Continuous Forms]{.uicontrol}.\
    This setting is necessary because otherwise the total cannot be calculated.

![Setting Default View to Continuous Forms for total calculation.](images/forms/f-orderdatasub-properties.png){#fig-f-orderdatasub-properties}

4.  Increase the height of the Form Footer section by dragging its bottom edge downward.

5.  Go to [Design \> Text Box (Controls group)]{.uicontrol} and draw a text box in the footer where the total should appear.

6.  Set the [Control Source]{.uicontrol} of the text box to `=Sum([Total])` and its [Format]{.uicontrol} to `Currency`.

7.  Save and close the subform.

8.  Open the main form [Orderdata main]{.uicontrol} and verify the total appears and updates correctly.
:::

## Form with Chart {#sec-forms-sales-boxes}

GOAL

Create a form with a column chart showing sales per box, per month, for the year 2010.

![Form displaying a column chart of sales per box per month (2010).](images/forms/f-sales-boxes-chart.png){#fig-f-sales-boxes-chart}

ANALYSIS

-   **BoxName**: from the [Boxes]{.varname} table.

-   **Sales**: calculated as `[Quantity]*[BoxPrice]`.

-   **BoxPrice**: from [Boxes]{.varname} table.

-   **Quantity**: from [OrderDetails]{.varname} table.

-   **OrderDate**: from [Orders]{.varname} table; [Month]{.varname} and [Year]{.varname} can be calculated with the functions `Month([OrderDate])` and `Year([OrderDate])`.

You will:

-   Create a query that outputs [BoxName]{.varname}, [Sales]{.varname}, [Month]{.varname}, and [Year]{.varname}.
-   Group by \[BoxName\] and then by \[Month\].
-   Filter on \[Year\] = 2010.

::::: {#prp-forms-sales-boxes}
[File]{.smallcaps}: `{{< var database >}}`

**Create Query**

1.  Open the database.

2.  Go to [tab Create \> Query Design (Queries group)]{.uicontrol} and add the tables [Boxes]{.varname}, [OrderDetails]{.varname}, and [Orders]{.varname}.

3.  Add field [BoxName]{.varname} from table [Boxes]{.varname}, and create the calculated fields: `Month: Month([OrderDate])`, `Sales: [Quantity]*[BoxPrice]` en `Year: Year([OrderDate])`.

4.  Click [Design \> Totals (Show/Hide group)]{.uicontrol}.

5.  Change in the column [Sales]{.varname} the [Group By]{.uicontrol} operator in `Sum` and in the column [Year]{.varname} in `Where`. Add `2010` as criteria for [Year]{.varname}.

![Query design: sales per box per month in 2010.](images/forms/q-sales-box-month-design.png){#fig-q-sales-box-month-design}

6.  Save the query as [Sales per box per month in 2010]{.varname} and close it.

::: {.callout-note title="Reformatting calculated fields"}
After reopening the query in [Design View]{.uicontrol}, Access may reformat the \[Sales\] column to:

-   Field: `Sales: Sum([Quantity]*[BoxPrice])`.
-   Total:`Expression`.
:::

**Create Form**

7.  Click [tab Create \> Form Wizard]{.uicontrol}. Use table \[Boxes\] and select [BoxName]{.uicontrol}. Click [Next]{.uicontrol}.

8.  In the wizard:

    -   Choose Datasheet layout.
    -   Title: "Sales per box per month in 2010".
    -   Save the form with the same name as the title.

9.  Open the form in [Design View]{.uicontrol} and expand the [Detail]{.uicontrol} section.

10. Click [tab Design \> Chart (Controls group)]{.uicontrol}, ![](images/common/control-chart.png), and draw a rectangle. The [Chart Wizard]{.uicontrol} starts.

11. In the wizard:

    -   Use query [Sales per box per month in 2010]{.varname}
    -   Fields: [Month]{.varname}, [Sales]{.varname}.
    -   Chart type: Column Chart.

![Initial chart layout generated by the Chart Wizard.](images/forms/chartwizard-layout-default.png){#fig-chartwizard-layout-default}

12. Drag [SumOfMonth]{.uicontrol} to the horizontal axis. It changes to `Month`.

13. Drag [Sales]{.uicontrol} to the vertical axis. It updates to `SumOfSales`.

![Final chart layout with proper data axes.](images/forms/chartwizard-layout-data.png){#fig-chartwizard-layout-data}

14. Click [Next]{.uicontrol}. Now you should select the field in the form that should be linked to the field in the chart. In both cases it is [BoxName]{.varname}.

![Linking form data to chart fields.](images/forms/chartwizard-linking-fields.png){#fig-chartwizard-linking-fields}

15. Click [Next]{.uicontrol}. Set the chart title: "Sales per box per month in 2010". Disable the legend and click [Finish]{.uicontrol}.

::: {.callout-warning title="No real data in the chart"}
In Design View, a placeholder chart appears, not live data.
:::

16. Save and close the form.

17. Open the form in [Form View]{.uicontrol}, browse some records, then close the form.
:::::

## Linking forms {#sec-forms-customer-orders}

The easiest way to create linked forms is by using the [Form Wizard]{.uicontrol}. This wizard generates all necessary forms, creates the required links, and adds a command button on the main form to open the related form.

The goal is to create a form that displays customer information, with a command button to open a separate form that shows the customer’s order details. See @fig-f-customerorders-linked-2.

![Two linked forms with synchronized data.](images/forms/f-customerorders-linked.png){#fig-f-customerorders-linked-2}

The main form displays customer data, which comes from the [Customers]{.varname} table. The linked form shows the customer’s orders, with order code and date, from the [Orders]{.varname} table. It also includes a subform that shows order details, sourced from the [OrderDetails]{.varname} table.

::: {#prp-forms-linking}
[File]{.smallcaps}: `{{< var database >}}`

1.  Open the database.

2.  Go to [tab Create \> Form Wizard (group Forms)]{.uicontrol}.

3.  Select [Table: Customers]{.uicontrol}.

![Form Wizard: Selecting customer-related fields.](images/forms/f-wizard-t-customers.png){#fig-f-wizard-t-customers-2}

4.  Add the following fields:

    -   From [Customers]{.varname}: [CustomerCode]{.varname}, [LastName]{.varname}, [FirstName]{.varname}, [Address]{.varname}, [ZipCode]{.varname}, [City]{.varname}.

    -   From [Orders]{.varname}: [OrderCode]{.varname}, [OrderDate]{.varname}.

    -   From [OrderDetails]{.varname}: [BoxCode]{.varname}, [Quantity]{.varname}.

5.  Click [Next]{.uicontrol}. Choose to view data by [Customers]{.varname} and select [Linked Forms]{.uicontrol} as the layout.

![Form Wizard: Selecting linked forms display option.](images/forms/f-wizard-customerorders-dataview.png){#fig-f-wizard-customerorders-dataview}

6.  Click [Next]{.uicontrol}. You’ll be prompted to name the forms.

7.  Use the following names:

    -   Main form: [CustomerOrders]{.varname}
    -   First linked form: [CustomerOrdersSub1]{.varname}
    -   Subform: [CustomerOrdersSub2]{.varname}

8.  Click [Finish]{.uicontrol}.

The [CustomerOrders]{.uicontrol} main form appears. However, the command button to open the linked form is hidden beneath the form title and cannot be clicked.

![Command button hidden beneath form title—requires layout adjustment.](images/forms/f-customerorders-titles.png){#fig-f-customerorders-titles}

9.  Open the [CustomerOrders]{.uicontrol} form in [Design View]{.uicontrol}.

    -   Delete the [CustomerOrders]{.uicontrol} label.
    -   Select the command button and, in the [Property Sheet]{.uicontrol}, change the caption to "Order data".

10. Optionally, adjust the layout of all three forms.

11. Switch to [Form View]{.uicontrol} and test the command button.

12. Save and close the form.
:::

::: {.callout-tip title="Overlapping windows vs. tabs"}
By default, Access opens each object (tables, queries, forms, reports) in a separate tab. However, with linked forms, this isn't ideal, you won’t see both forms at once.

To show forms in overlapping windows instead of tabs:

1.  Go to [File \> Options \> Current Database]{.uicontrol}.
2.  Under [Document Window Options]{.uicontrol}, select [Overlapping Windows]{.uicontrol}.
:::

## Exercises {#sec-forms-exercises}

::: {#exr-form001}
**Customers (form001)**

Create a form named [form001]{.varname} that looks like the following example.

![Target layout for form001 – Customers form.](images/forms/form001.png)
:::

::: {#exr-form002}
**Pralines in Boxes (form002)**

Create a main form with a subform that shows which boxes contain a specific bonbon and in what quantity. Name the forms:

-   Main form: [form002 main]{.varname}
-   Subform: [form002 sub]{.varname}

![Target layout for form002 – Pralines in Boxes.](images/forms/form002.png)
:::

::: {#exr-form003}
**Boxes per Customer (form003)**

Create a main form with a subform. The main form displays box data; the subform shows total sales per customer for the selected box.

First, create a query that calculates total box sales per customer. Name everything as follows:

-   Query: [Boxes per customer]{.varname}
-   Main form: [form003 main]{.varname}
-   Subform: [form003 sub]{.varname}

![Target layout for form003 – Boxes per Customer.](images/forms/form003.png)
:::