6 Forms

OBJECTIVES

Types of forms: main form, sub form and linked form.
Creating forms: generated and manual.
Working with controls.
Calculations in a form.
Forms with a chart.

Forms are for showing organized and formatted views of fields in tables and queries. They are very important for data entry.

6.1 About creating forms

Data can be entered directly in the tables. In practice, this happens only in very simple tables. Usually, forms are used for data entry. Simple forms are often based on data from one table or query. You also have advanced forms such as main-subforms or linking forms, whose data come from related tables or queries.

Except that you can give your forms an attractive appearance, it is more important that you have the opportunity to apply all kinds of checks (validations ) on data entry. And then you can place controls on forms. Examples of controls are: list boxes, checkboxes, buttons, …

You can build a form from scratch, but you can also let Access generate a form or use the Form Wizard and then adjusting the resulting form manually.

6.1.1 Main and subforms

A subform is a form that is included in another form, called the main form. Subforms are often used when you need to display data from tables and queries with a one-to-many relationship. The data from the one-side are in the main form and the data from the many-side in the subform.

Example 6.1 Orders customer

Main form: Contains data from a customer, taken from table Customer.

Sub form: Contains the customer’s orders, taken from table Orders.

The main form and sub form are linked so that the sub form only displays records that are related to the record in the main form.

Figure 6.1: Main form Customers with subform Orders.

6.1.2 Linked forms

Linked forms are separate forms that are related to each other. One of the forms, also called the main form, has a command button that opens another form when clicking on it. The records between the two forms are synchronized. Behind the button is program code (VBA) which causes that the subform is opened. This VBA code is automatically created by the Wizard. You do not need to be able to program to work with this.

Whether you with linked forms or with subforms depends usually on the method of work of the user. If you only need the primary data of a customer, you don’t have to see simultaneously all order information from the customer. It is often convenient to have a button on the form with which you can pick out the corresponding order information.

Figure 6.2: Two linked forms with synchronized data.

6.2 Controls and Layouts

6.2.0.1 Controls

Controls are parts of a form or a report that you use to enter, edit, or display data. So is the Text Box is the most used control for displaying data. Other commonly used controls are command buttons, checkboxes, and combo boxes.

Controls can be bound, unbound, or calculated:

Bound control: A control whose source of data is a field in a table or query. You use bound controls to display values that com from fields.
Unbound control: A control without a data source. You use unbound controls to display information, pictures, lines, …
Calculated control: A control whose data source is an expression, rather than a field.

6.2.0.2 Layouts

Layouts are guides that align your controls horizontally and vertically. Layouts are optional and are mostly used to give your form a uniform appearance. Layouts come in two varieties:

Tabular layout: In tabular layouts, controls are arranged in rows and columns like a spreadsheet, with labels across the top.

Stacked layout: In stacked layouts, controls are arranged vertically, with a label to the left of each control.

6.2.0.3 Edit Controls

Because you often need to reorganize controls on forms, it is important to know how you can change the location and layout of these controls. Many operations you can perform on multiple controls. To edit controls, you must always select them first. Also, make sure that the Property Sheet is visible.

selecting: Click on the control.
multiple selections: Click with pressed Shift key on the controls you want to select. Another possibility is to drag a rectangular frame with the mouse around the controls.
deselecting: Click on an empty spot in the form.
moving: Drag the control with the top left square. An exact location can be set in the Property Sheet.
dimensions: Drag one of the handles (in the middle or the corner points of the selection rectangle). Exact dimensions can be set in the Property Sheet.
aligning: With the options at tab Arrange > Align (group Sizing & Ordering).
deleting: With the Delete key.

6.3 Generated form

When new records must be added to a table, it is usually intended that all of the fields get a value. Such a form must contain all the fields from the table. The quickest way to create such a form is by first selecting the table and then through the command Form automatically generate a form containing all fields. After that, you can manually adjust the form.

In this task, you create a form for a warehouse employee to modify the warehouse stock of a box and eventual to indicate if a box is out of production. It shouldn’t be possible for the employee to change the other box data with this form. The form should look something like this:

Task 6.1 File: candy365.accdb

Open the database.
Select table Boxes. It’s not necessary to open the table.
Click tab Create > Form (group Forms). The form is created and displayed in Layout View.

If Access finds a table that has a one-to-many relationship with the table or query that you used to create the form, Access adds a sub-datasheet to the form that is based on the related table or query. If there is more than one table with a one-to-many relationship to the table that you used to create the form, Access does not add any datasheets to the form.

In this case, the form is based on the table Boxes, and there is a one-to-many relationship defined between the table Boxes and the table OrderDetails. The sub-datasheet displays all the records in the OrderDetails table that pertain to the current Boxes record.

If you decide that you do not want the sub-datasheet on the form, you can delete the subdatasheet in the Layout view by selecting the datasheet, and then pressing Delete.

Select the sub-datasheet and press Delete.
Switch to Design View.

In a form that has been created through this method, two controls are created for each field: a Text Box and a linked Label.

All controls are arranged vertically with a label to the left of the control. This is called a Stacked layout. That’s why you can’t move a control anywhere on the form. If this is the intention, then you must remove the layout for that control through tab Arrange > Remove Layout (group Table).

Select the text in frame Boxes in the Form Header and change it in Stock-in-trade. Change the properties Font Weight in Bold and Font Size in 20 pt.

The Property Sheet is on the right side of the screen and can be made visible and invisible by clicking on button Property Sheet or by using hotkey F4. And to faster find a specific property, you can alphabetically sort the list with the button in the top right corner of the sheet.

Select the text boxes of all fields and change the property Width in 3". After that, deselect by clicking elsewhere in the form.

The property Locked of a control can be used to protect data in a field by making it read-only.

Select all text boxes except Stock and OutOfProduction. Then change property Locked (tab Data) in Yes.

With the text boxes still selected, change the background color from white in light grey. You can do this in one of the following ways:
- Change property Back Color.
- Right-click on one of the selected fields and choose from the shortcut menu Fill/Back Color.
Switch to Form View to check the result.
Close the form, answer the question to save the changes with Yes.
Name the form Stock-in-trade and click OK.

6.4 Main and subform

The easiest way to make a main form with a subform is with the aid of the Form Wizard. This wizard makes the two forms and provides the linking.

The goal is to create a form that displays customer code, name, and address, and further shows the corresponding orders with order code, order date, and order subtotal.

The customer data is going to the main form and all necessary fields are available in table Customers. The subform will display the order data: OrderCode, OrderDate, and a calculated subtotal. The subtotal can be calculated from the fields Quantity and BoxPrice (table Boxes).

To display the subtotal on a form, you need a query that can offer all these data as a source to the subform. Such a query is already available and is named Orders with subtotals. This query contains also the field CustomerCode, so that a link between the main and subform can be established.

Task 6.2 File: candy365.accdb

Open the database.
Choose tab Create > Form Wizard (group Forms).
Select Tables/Queries Table: Customers.

Figure 6.10: Form Wizard: selecting fields.

Add successively the following fields: CustomerCode,FirstName, LastName, Address, ZipCode, City, Telephone.
Select Query: Orders with subtotals under Tables/Queries. The fields of the query are displayed in the box Available Fields.
Add successively the following fields: OrderCode, OrderDate, and Subtotal. Click Next.

Check the settings and then click Next. The wizard now asks for the layout of the subform: Tabular or Datasheet.
Select layout Datasheet and click Next. You can now enter titles for the form and subform. There are already default titles available, but you can change these titles.
Change the default titles as follows.
- Form title: Customers and Orders
- Subform title: Customers and Orders Subform
Click Finish.

If desired, both the design of the main form and the subform can be modified.

6.5 Data entry form Customers

In this task, you create a form for entering data of new customers and editing data of existing customers. And you will practice some skills in moving and aligning controls to get the following desired format.

Task 6.3 File: candy365.accdb

Open the database.
Select table Customers. It’s not necessary to open the table.
Click tab Create > Form (group Forms). The form is created and displayed in Layout View.

Because the form is based on table Customers, and this table has a one-to-many relationship with table Orders, a sub-datasheet based on Orders is added automatically to the form. The sub-datasheet displays all the records in the Orders table that pertain to the current Customers record.

Select the sub-datasheet and press Delete.
Switch to Design View.
Remove the form logo in the form header.
Save the form with the name Customer entry and modification.
Make the vertical dimension (the height) of the Form Header somewhat larger by dragging the top of the Detail section downwards.
Select the text Customers in the form header label and change it in Chocolate Connoisseur. Change the layout of the header in MS Sans Serif, 14 pt, italic and bold.
Select the control Label on the ribbon through tab Design > Label (group Controls) and draw in the header under the first label a frame for the second label. Type the text Customer entry and modification and format it as MS Sans Serif, 14 pt, bold, color black.
Select all controls in the section Detail by drawing a rectangle from the top left corner while holding down the left mouse button to the lower right corner. Then release the mouse button.
Choose tab Arrange > Remove Layout (group Table). The selected controls have now been removed from the stacked layout and can therefore be moved freely on the form.
Deselect the controls.
Drag the controls on the form to create a design that looks as good as possible in the desired format. Use the tips listed below.

You can align selected controls with tab Arrange > Align (group Sizing & Ordering.
You need a new Label with the text Name: in front of the text boxes first and last name.
You can set the dimensions of controls with the properties Width and Height.
You can set the upper left location of a control with the properties Top and Left.
You can set the background color with the property Back Color.

Switch to Form View and test the form.
Close the form and save the changes.

6.6 Data entry form Pralines

In this task, you are going to create a form for entering data of new pralines and editing data of existing pralines. Furthermore, three text boxes should be replaced by a combo box control. The desired layout for the form is displayed in Figure 6.14.

Task 6.4 File: candy365.accdb

Open the database.
Select table Pralines. It’s not necessary to open the table.
Click tab Create > Form (group Forms). The form is created and displayed in Layout View.
Switch to Design View.
Delete table BoxDetails in the lower part of the form.
Remove the form logo in the form header.
Save the form with the name Praline information.
Change the format of the header text in Calibri, 22 pt, bold, white color on a green background.
Select all controls in the section Detail and remove the (stacked) layout through tab Arrange > Remove Layout (group Table). You can now work with each control separately.
Deselect the controls.
Remove the fields ChocolateType, FillingType and NutType. Move the remaining fields on the form to create a design that looks as good as possible as the desired format
Select tab Design > Line (group Controls) and draw a horizontal line beneath the fields PralineCode and PralineName. Specify the line thickness of 2 pt and the same color as the background of the header.
Select tab Design > Combo Box (group Controls) and draw a rectangle for field ChocolateType. After drawing the rectangle, the Combo Box Wizard is started.
Answer the Wizard questions as follows:

The values will be typed.
Number of columns 1. The values are: Butterscotch, Milk, Dark and Toffee.
The value should be stored in the field ChocolateType.
Label text: Chocolate type:

After finishing the Wizard you are returning to the Design View.

Create analogously a Combo Box for FillingType with the values: Amaretto, Blueberry, Cherry cream, Coconut, Marzipan, Mocha cream, None.
Create analogously a Combo Box for NutType with the values: Almond, Cashew, Hazelnut, Macadamia, Paranut, Pecan, Pistachio, Walnut, None.
Switch to Form View and test the form.
Close the form and save the changes.

6.7 Data entry form Boxes

In this task, a text box on an existing subform will be replaced by a Combo Box with a query as data source.

With the existing form Boxes, you can only select pralines through their PralineCode. The goal is to make it possible that you can also choose by the name of the praline.

The form Boxes consist of the main form Boxes and the subform Boxes subform. The praline choice occurs in the subform. So only the subform should be changed, the text box PralineName must be replaced by a Combo Box.

But Boxes subform is based on table BoxDetails and that table doesn’t have a field PralineName. Therefore a query should be created, based on table Pralines, producing the columns PralineName and PralineCode, and sorted on PralineName. This new query is the data source for the combo box.

Task 6.5 File: candy365.accdb

Query Choose praline

Open the database.
Choose tab Create > Query Design (group Queries). Add table Pralines and from this table the fields PralineName and PralineCode. Sort ascending on PralineName.

Figure 6.15: Design query for praline choice.

Close the query and save it with the name Choose praline.

Adjustments subform

Open form Boxes subform in Design View.
Remove field PralineName in section Detail.
Select tab Design > Combo Box (group Controls) and draw a rectangle on the same location as the removed field PralineName. After drawing the rectangle, the Combo Box Wizard is started.
Answer the Wizard questions as follows:
- The values for the combo box comes from a table or query.
- The values are provided by query Choose praline.
- The selected fields are PralineName and PralineCode.
- Sort order is Ascending on PralineName.
- Adjust column widths by a double-click on the right edge of the column header.
- The value in column PralineCode should be stored in the field Code.
- Accept the default text for the label. This label will be removed in the next step because the label text already exists in the form header.
Remove the combo box label created in the previous step.

Figure 6.16: Combo Box on subform Boxes.

Select the Combo Box and check in Property Sheet (tab Data) if property Limit To List has the value Yes. If not, change it.

With this setting, you can only choose praline names from the list and you can’t add new bonbon names.

Close form Boxes subform and save the changes.
Test the Combo Box with form Boxes.

6.8 Data entry Orders

To view an existing order and enter a new order, an order form must be created, see Figure 6.17.

The data on the main form are from the tables Customers and Orders. So you need to create a query (Orders-Customers) based on these two tables, which provide the required data.

The data on the subform are from the tables OrderDetails and Boxes. So you need to create a query (OrderDetails-Boxes) based on these two tables, which provide the required data. The column Total is a calculated field.

Then, create a main form with subform based on successively the queries Orders-Customers and OrderDetails-Boxes. Add the desired fields for both the main form and the subform.

Task 6.6 File: candy365.accdb

Query Orders-Customers

Open the database.
Choose tab Create > Query Design (group Queries). Add tables Orders en Customers
Add all fields from these two tables.

With the choice * you can add all fields of a table.

Figure 6.18: Design query orders customers.

Close the query and save it with the name Orders-Customers.

Query OrderDetails-Boxes

Choose tab Create > Query Design (group Queries). Add tables OrderDetails and Boxes.
Add all fields from these two tables.
Make a column with the name Total which performs the calculation Quantity*BoxPrice.

Close the query and save it with the name OrderDetails-Boxes.

Form creation

Choose tab Create > Form Wizard.
Select query Orders-Customers and add all fields except Orders.CustomerCode.
Select query OrderDetails-Boxes and add the following fields: Boxes.BoxCode, BoxName, Quantity, BoxPrice, and Total.
Click Next and let the data view by Orders-Customers. With this choice, the main form will be based on query Orders-Customers and the subform on query OrderDetails-Boxes.
Click Next and choose Tabular for the subform.
Coose a style and click Next.
Provide the titles Orderdata main and Orderdata sub and click Finish. The form is opened.
Modify the layout of the two forms to make it look to the desired form for order data.

6.9 Total order amount

The form that is created in Section 6.8 will be modified so that the total order amount is calculated and displayed.

Task 6.7 File: candy365.accdb

Open the database.
Open form Orderdata sub in Design View.
Select in the Property Sheet the type Form and then set the Default View on Continuous Forms.
This setting is necessary because otherwise the total cannot be calculated.

Make the vertical space for the form footer larger by dragging the bottom down.
Select tab Design > Text Box (group Controls) and draw a rectangle in the form footer on the place where the total amount should come.
Modify property Control Source of the text box in =Sum([Total]) and the value of property Format on Currency.
Close the subform and save the changes.
Open form Orderdata main and check if everything is ok.

6.10 Form with Chart

INFORMATION NEEDS

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

ANALYSIS

The various components in the graph can be extracted or calculated from certain tables.

The BoxName is in table Boxes.
The Sales must be calculated from the expression [Quantity]*[BoxPrice].
The field BoxPrice is also in table Boxes.
The field Quantity is in table OrderDetails.
Month and Year can be calculated with the functions Month([OrderDate]) and Year([OrderDate]).
The field OrderDate is in table Orders.
Grouping on BoxName first and then on Month
Filtering on the year 2010.

You need to create a query which provides the necessary data (BoxName, Sales, Month, and Year). Grouping and filtering via the Total row in the design grid. For grouping, you choose the option Group By and for filtering the option Where, with 2010 as value for Criteria.

The work consists of two parts. First creating the query and then the form with the chart.

Task 6.8 File: candy365.accdb

Query creation

Open the database.
Choose tab Create > Query Design (group Queries). Add the tables Boxes, OrderDetails, and Orders.
Add field BoxName (table Boxes) and create the following calculated fields Month:Month([OrderDate]), Sales:[Quantity]*[BoxPrice] en Year:Year([OrderDate]).
Choose tab Design > Total (group Show/Hide).
Change in the column Sales the Group By operator in Sum and in the column Year in Where. Add 2010 as criteria for Year.

Figure 6.23: Design query sales per box per month in 2010.

Close the query and save it with the name Sales per box per month in 2010.

When you open the query again in Design View after closing it, Access has made some changes for the column Sales:

Value in row Field is now Sales: Sum([Quantity]*[BoxPrice]).
Value in row Total is now Expression.

Create Form

Choose tab Create > Form Wizard. Add table Boxes and from this table field BoxName. Click Next.
Answer the Wizard questions as follows:
- Form layout Datasheet.
- Title Sales per box per month in 2010.
- Save the form with the same name as the title.
Open the form in Design View. Create some space for the chart in section Detail by dragging down the underside.
Choose tab Design > Chart (group Controls) ] and draw on the form a rectangular frame for the chart. The Chart Wizard is started.
Answer the first three Wizard questions as follows:
- Use query Sales per box per month in 2010.
- Fields for the chart: Month and Sales.
- Choose Column Chart as type.

The Wizard displays a layout of the chart. You need to modify it.

Drag the field SumOfMonth out of the box to the vertical axis to the box at the horizontal axis. It is now displayed at the horizontal axis as Month.
Drag the field Sales to the vertical axis box. The display along the horizontal axis then changes in SumOfSales.

Click Next. Here you select the field in the form that should be linked to the field in the chart. In both cases it is BoxName.

Figure 6.26: Linking fields form and chart.

Click Next. Enter for the title of the chart Sales per box per month in 2010 and choose not to display a legend. Then click Finish.

The Design View shows an example graph and not the chart of real data.

Close the form and save the changes
Open the form and switch to Form View and browse through some records and after that close the form.

6.11 Linking forms

The easiest way to create linked forms is with the [Form Wizard]{.uicontrol. This wizard creates the necessary forms, ensures the linking, and provides in a command button on the main form to open the linked form.

The goal is to create a form in which the data of a customer are displayed and with a command button to display the order information from the customer in a new form. See Figure 6.27.

Figure 6.27: Two linked forms with synchronized data.

On the main form is the customer data, all necessary fields are available in table Customers. The linked form contains the order code and date and these fields are in table Orders. This form also has a subform containing the details of the order and this data comes from table OrderDetails.

Task 6.9 File: candy365.accdb

Open the database.
Choose tab Create > Form Wizard (group Forms).
Select Table: Customers.

Figure 6.28: Form Wizard: selecting fields.

Add the following fields:
- From table Customers: CustomerCode, LastName, FirstName, Address, ZipCode, City.
- From table Orders: OrderCode, OrderDate.
- From table OrderDetails: BoxCode, Quantity.
Click op Next. Choose to view the data by Customers and choose Linked forms.

Click Next. Now you can name the forms.
Name the forms as follows:
- First form: CustomerOrders
- Second form: CustomerOrdersSub1
- Subform: CustomerOrdersSub2
Click Finish.

The main form CustomerOrders is displayed. The design still needs to be tweaked because the command button CustomerOrdersSub1 is located under the label CustomerOrders and therefore can’t be used.

Figure 6.30: Main form title with the unreachable command button below to open the sub form.

Open form CustomerOrders in Design View.
- Delete label CustomerOrders.
- Select the command button and use the Property Sheet to change the caption in Order data.
If desired, adjust the layout of the three forms.
Switch to Form View and test the operation of the command button.
Close the form and save changes.

In a default installation of Access each document (table, query, form, report ) opens in a separate tab. With linked forms, this is not always convenient. When you click the command button, the linked subform then opens in a new tab and you don’t see the information in both forms together.

If you want to do so you have to change the database settings that documents appear in overlapping windows instead of tabs. Method:

Choose File > Options > Current Database.
Select Overlapping Windows at Document Window Options.

6.12 Exercises

Exercise 6.1 Customers (form001)

Create a form Customers that looks like the following figure. Name the form form001.

Exercise 6.2 Pralines in boxes (form002)

Create a main form with subform that shows you in which boxes a particular bonbon is present and in what quantity. Name the forms form002 main and form002 sub.

Exercise 6.3 Boxes per customer (form003)

Create a main form with subform in which the main form displays the box data and the subform displays the total sales per customer for this box. For this, create first a query that calculates the total number of ordered boxes per customer. Name the query Boxes per customer and the forms form003 main and form003 sub.