Microsoft Access 2010 - Lesson 37: Sectional and Tabbed Forms

	Sectional and Tabbed Forms

Subforms and Subreports

Introduction

A relational database allows you to separate data in objects so that these objects can hold different pieces of information and make data available to other objects that need it. Sooner or later, users constantly need information held by one form or report while they are working on another form or report. There are various ways you can solve such a problem. One solution is to "embed" one form or report into another form or report.

A subform (or a subreport) is a form (or a report) that you position inside of another form (or another report). In order to "include" one form (or report) into another form (or report), both objects must have a relationship. The form (or report) that is hosting the other form (or the other report) is the parent. The form (or report) that is added to the parent is called the child form (or child report). The parent object must have a primary key that "links" or relates it to a foreign key in the child object.

Microsoft Access Automatic Subforms

Microsoft Access provides various techniques you can use to create a subform. Probably the simplest technique consists of using a wizard. This allows you to specify the table or query that is holding the parent records, followed by the table or query that has the child records. Of course, both lists must be able to communicate through the primary key of the parent list and a foreign key in the child list.

To generate a form that contains a subform, start the Form Wizard. In the first page of the wizard, in the Tables/Queries combo box, select the parent object and, in the Available Fields list, select the fields you want to display on the form. Then, in the Tables/Queries combo box again, select the child list. In the Available Fields list, select the fields that the subform should display. Continue with the wizard. In the second page, you must be able to identify the relationship that will control the link between both list. In other words, you must be able to identify the primary key from the parent list and the foreign key from the child list. Once this is clear, you can continue. In the third page of the wizard, you will decide how you want the subform to display, as a tabular list or as a datasheet. After making this decision, you can continue. The fourth page of the wizard allows you to select a preset design you want to apply to the form (and the subform). There is no particular design you need to follow for a subform. After making the selection, you can click Next and Finish.

Practical Learning: Automatically Creating an Auto-Subform

Start Microsoft Access
Open the Video Collection3 database you started in in Lesson 35. If you did not create it, from the resources that accompany these lessons, open the Video Collection3a database
On the Ribbon, click Create
In the Forms section, click More Forms -> Form Wizard
In the Tables/Queries combo box, select Table: Directors
Click the Select All button
In the Tables/Queries combo box, select Table: Videos
Double-click the following fields to select them: Title, CopyrightYear, Length, and Rating
Click Next
In the second page, click by Directors (or make sure it is selected)

Click Next
In the 3rd page, click the Tabular radio button
Click Next
In the 4th page of the wizard, accept the name of (main) form as Directors.
Change the name of the subform to sbfVideos
Click Finish
After viewing the form, close it

Sub-Forms and Sub-Report Design

Introduction

The Form Wizard allows you to quickly create a sub-form. This would be fine if you just want to review records associates from one table to another. As effective as it can be, it can also provide some functionality you do not need and do not want. Besides that, the Form Wizard imposes some aspects you must simply accept. The alternative it to design your own subform, and you have various options:

You can create a form using the Form Wizard, then design a separate subform and add it to the form
You can create both the form and its subform using the Form Wizard, then modify the design
You can design both the form and the subform separately, then join them

All these techniques indicate that you should be able to configure the functionality of the subform as it relates to the form that will host it. When designing a subform, you must also specify its appearance. You have the options as datasheet, tabular, or just regular form.

The Datasheet Subform

As mentioned in Lesson 8, a form can appear as a datasheet like a table. This makes the form appear with columns and records whose intersections are cells. You can also create a subform that display as a datasheet.

To create a subform that would display as a datasheet, start the form in Design View. In the Property Sheet, set its Default View to Datasheet. When designing the form, you can position the controls anywhere because their positions would not be obvious on the form. Also, the sizes of the labels and fields would not show on the form. When designing the form, you work in Design View. The Datasheet View appears only if you change the view. Although you can use the different sections of a form, for a Datasheet form, the sections would appear only in the Design View, not in Datasheet View.

When adding controls or other objects to a subform (or a form) that would display in Datasheet View. Only the controls you display in the Detail section would appear when the subform comes up. The objects in the Queries sections would not appear. You can use this feature to your advantage. For example you can add unbound controls in a Form Footer section to hold some values or perform some calculations, then refer to those controls in the parent form.

Practical Learning: Using a Datasheet Subform

Open the Music Collection1 database
On the Ribbon, click Create
To create a new form, in the Forms section, click Form Design
Access the Property Sheet for the form and set its Record Source to AlbumTracks
Save the form as sbfTracks
Reduce the width of the form so its right border is at 2^3/8
If the Field List is not displaying, on the Ribbon, click Design and, in the Tools section, click Add Existing Fields .
In the Field List, click TrackNumber
Press and hold Shift
Then click TrackLength
Release Shift. This selects the TrackNumber, the TrackTitle, and the TrackLength fields
Click and drag the selected fields to the Detail section of the form. There is no need to change the positions or locations of the labels and text boxes
Click the TrackNumber text box
On the Ribbon, click Format and click the Right button
Double-click the button at the intersection of both rulers
In the Property Sheet, click Format and change the following properties:
DefaultView: Datasheet
Record Selectors: No
Navigation Buttons: No
To switch the form to Datasheet View, right-click its title bar and click Datasheet View
Right-click the # column header and click Field Width...
Click Best Fit
Click the Track Title column header
On the Ribbon, click Home
In the Records section, click More Field Width
Type 24.75
Click OK
On the table, right-click the Length column header and click Field Width
Type 7.85 and press Enter
Save and close the subform
In the Navigation Pane, under the Forms bar, double-click MusicAlbums to open it
After viewing it, switch it to Design View and click the Tracks tab on the tab control
In the Controls section of the Ribbon, click the More button and make sure the Use Control Wizards option is selected (or click it).
To add the new subform, in the Controls section of the Ribbon, click the Subform/Subreport button
Click the body of the Tracks tab on the form
In the first page of the Subform/Subreport Wizard, in the list of forms, click sbfTracks and click Next
In the second page of the wizard, make sure the Choose From A List radio button is selected. Also, in the Select One Of These Links list box, make sure the relationship is based on the AlbumID field.
Click Next
Accept the suggested name of the subform as sbfTracks and click Finish
Click the label that was added to the subform and press Delete (to remove the label). You may also need to move the subform and resize the tab sheet
Save and preview the form
Close the form

The Continuous Form

A variant of the datasheet is the tabular form. As you may know already, a tabular form displays its records in groups. Instead of showing one record at a time, the form can show as many records as its size allows:

Continuous Form

This type of form is referred to as continuous because the records are displayed continuously in the same view. If the number of records is not too high, all of them would display. If there are more records than the form's size can allow to view, some records would be hidden. To view them, you can use the scroll bars.

To create a continuous form, you can use the Form Wizard where, in the second page of the wizard, you would select the Tabular option. To create a continuous form in Design View, set its Default View to Continuous Forms:

Continuous Form

Unlike the datasheet form, a continuous form can be equipped with, and can display, other sections than the Detail section in Form View. This means that you can include additional fields in the Queries sections. Based on this, a typical design of a continuous form consists of creating some labels in the Form Header section and positioning their corresponding controls under them but in the Detail section. The fields can be horizontally aligned and adjacent each other. You should (strongly, if not must) avoid including Memo and OLE Objects (pictures, linked documents, etc) in a continuous form because such fields may take too much space. This would deceive the purpose of the continuous form.

Practical Learning: Designing a Subform or Subreport

Open the Ceil Inn1 database you started in Lesson 34 and continued in the previous lesson. If you did not create create it, from the resources that accompany our lessons, open the Ceil Inn4 database
On the Ribbon, click Create
To create a new query, in the Queries section, click Query Design
In the Tables tab of the Show Table dialog box, double-click Customers, Occupancies, and Rooms
On the Show Table dialog box, click Close
In the lists of fields, from the Occupancies list, double-click DateOccupied
In the Rooms, double-click RoomNumber
In the Occupancies list, double-click RateApplied, PhoneUse, and InternetFee
In the Customers list, double-click CustomerID
Save the query as CustomerRoomUse and close it
On the Ribbon, click Create
To create a new form, in the Forms section, click Form Design
Using the Property Sheet, set its Record Source to CustomerRoomUse
Save the form as sbfCustomerRoomUse
Change the following properties:
Default View: Continuous Forms
Record Selector: No
Navigation Buttons: No
Right-click the form and click Form Header/Footer
In the Controls section of the Ribbon, click the Label
Click just under the Form Header bar on the left side:
Type Date Occupied and press Enter
If the same way, add labels with the captions Room #, Rate Applied, Phone Use, and Internet Use
Design and apply a font available to you. Here is an example:
To display the Field List, on the Ribbon, click Add Existing Fields
From the Field List, drag DateOccupied and drop it somewhere in the Detail section. Click its label to select it and press Delete to remove it
In the same way, add the other fields
Align them next to each other under their corresponding labels. Design them as you see fit under the Part label but in the Detail section
In the Controls section of the Ribbon, click the Text Box and click under the Form Footer
Using the Property Sheet, change its characteristics as follows:
Name: txtTotalRateApplied
Control Source: =Sum(RateApplied)
Format: Fixed
Add another text box to the form footer section, change its characteristics as follows:
Name: txtTotalPhoneUse
Control Source: =Sum(PhoneUse)
Format: Fixed
Add another text box to the form footer section, change its characteristics as follows:
Name: txtTotalInternetFee
Control Source: =Sum(InternetFee)
Format: Fixed
Click the Form Footer bar and, in the All tab of the Property Sheet, set its Visible value to No
Save, preview and close the sub-form
In the Navigation Pane, under Forms, double-click Customers
After viewing the form, switch it to Design View
In the Navigation Pane, under Forms, drag sbfCustomerRoomUse and drop it on the form under the Notes memo

Add four text boxes under the subform and design them as follows:

Label's Caption	Text Box Name	Format	Control Source
Phone Use:	txtPhoneUse	Fixed	=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
Internet Use:	txtInternetUse	Fixed	=[sbfCustomerRoomUse].[Form]![txtTotalInternetFee]
Rate Total:	txtRateTotal	Fixed	=[sbfCustomerRoomUse].[Form]![txtTotalRateApplied]
Net Pay:	txtNetPay	Fixed	=Nz([txtPhoneUse])+NZ([txtInternetUse])+Nz([txtRateTotal])

Save and preview the form:
Close the form

The Single Form

A single form is the type of form that displays its data one record at a time. This concept, which was used on most forms we have created so far, can also be applied to a sub-form. The single layout can be valuable if you want to show one record from a related form as it is linked to a record on the current form. While a Datasheet form can clearly show that it is an embedded object, the fields of a single form can easily be mixed with those of the hosting form but it is usually determined to be apart.

A single subform is designed like a regular form where fields are created in the Detail section and other optional controls can be added to the other sections of the form. When selecting the fields that would be part of the subform, make sure you include only those that can be useful in the form that will host the subform.

Practical Learning: Designing a Single Subform

Open the Bethesda Car Rental2 database you started in Lesson 35 and continued in the previous lesson
On the Ribbon, click Create
To create a new form, in the Forms section, click Form Design
Save the form as sbfCustomers
Using the Property Sheet, set the following characteristics:
Record Source: Customers
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
On the Ribbon, click Add Existing Fields
Design the form as follows:
Save, preview, and close the subform
To create a new form, on the Ribbon, click Create and, in the Forms section, click Form Desig n
Save the form as sbfCars
In the Property Sheet, click Record Source and click its ellipsis button
In the Show Table dialog box, double-click Cars and Categories
Click Close
In the list of fields, double-click CarID, TagNumber, Make, Model, CarYear, and Category (from the Categories table)
Close the Query Builder
When asked whether you want to save, click Yes
Using the Property Sheet, set the following characteristics:
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
On the Ribbon, click Add Existing Fields
Design the form as follows:
Save, preview, and close the subform
On the Ribbon, click Create and, in the Forms section, click Form Design
Save the form as RentalOrders and change the following characteristics in the Property Sheet:
Record Source: RentalOrders
Caption: Bethesda Car Rental - Rental Orders
On the Ribbon, click Add Existing Fields
From the Fields List, drag RentalOrderID and drop it in the Detail section of the form
In the Controls section of the Ribbon, make sure the Use Control Wizard button is highlighted. Click Combo Box and click the Detail section of the form
In the first page of the wizard, make sure the first radio button is selected and click Next
In the second page of the wizard, click Table: Employees
Click Next
In the Available Fields list of the third page, double-click EmployeeNumber, LastName, and Title
Click Next
In the fourth page of the wizard, click the arrow of the Ascending combo box and select EmployeeNumber
Click Next
In the fifth page of the wizard, review the list and click Next
In the sixth page of the wizard, click the arrow of the combo box and select EmployeeID
Click Next
Accept the default label and click Finish
Using the Property Sheet, change the caption of the label to Processed By:
Click the combo box
In the Property Sheet, change its Name to cbxEmployeeID
Click the Data tab, click Row Source, and click its ellipsis button
Change the third column name to Employee Name: [LastName] & ", " & [FirstName]
Close the Query Builder
When asked whether you want to save, click Yes
While the combo box is still selected, in the Property Sheet, change the following characteristics:
Column Widths: 0";0.85";1.3";1.75"
List Width: 3.9"
In the Controls section of the Ribbon, click the More button and make sure the Use Control Wizards option is highlighted .
Click Combo Box and click the Detail section of the form
In the first page of the wizard, make sure the first radio button is selected and click Next
In the second page of the wizard, double-click Table: Customers and click Next
In the Available Fields list of the third page, double-click DrvLicNumber and FullName
Click Next
In the fourth page of the wizard, accept the default and click Next
In the fifth page of the wizard, review the list and click Next
In the sixth page of the wizard, click the arrow of the combo box and select CustomerID
Click Next
Accept the default label and click Finish
Using the Property Sheet, change the caption of the label to Processed For:
Click the combo box. In the Property Sheet, change the following characteristics:
Name: cbxCustomerID
Column Widths: 0";1.35";1.5"
List Width: 2.85
In the Controls section of the Ribbon, click Subform/Subreport and click the form under the previously added combo box.
If you receive a Microsoft Office Access Security Notice, read it and click Open
In the first page of the wizard, in the list, click sbfCustomers and click Next
In the second page of the wizard, make sure it indicated that the tables are connected through the CustomerID field and click Next
Accept the suggestions of the third page and click Finish
In the Controls section of the Ribbon, make sure the Use Control Wizard button is highlighted. Click Combo Box and click the Detail section of the form
In the first page of the wizard, make sure the first radio button is selected and click Next
In the second page of the wizard, double-click Table: Cars and click Next
In the Available Fields list of the third page, double-click TagNumber, Make, and Model
Click Next
In the fourth page of the wizard, accept the default and click Next
In the fifth page of the wizard, review the list and click Next
In the sixth page of the wizard, click the arrow of the combo box and select CarID
Click Next
Accept the default label and click Finish
Using the Property Sheet, change the caption of the label to Car Rented:
Click the combo box. In the Property Sheet, change the following characteristics:
Name: cbxCarID
Column Widths: 0";0.75";0.85";1.55"
List Width: 3.15"
In the Controls section of the Ribbon, click Subform/Subreport and click the form under the previously added combo box
In the first page of the wizard, in the list, click sbfCars and click Next
In the second page of the wizard, make sure it indicated that the tables are connected through the CarID field and click Next
Accept the suggestions of the third page and click Finish
Using the Fields List, add the other controls

Add additional text boxes and set their characteristics as follows:

Label's Caption	Text Box Name	Format	Control Source
Sub-Total:	txtSubTotal	Fixed	=Nz([RateApplied])*Nz([TotalDays])
Tax Amount:	txtTaxAmount	Fixed	=CLng(Nz([txtSubTotal])Nz([TaxRate])100)/100
Rent Total:	txtRentTotal	Fixed	=Nz([txtSubTotal])+Nz([txtTaxAmount])

Save the form and switch it to Form View
Close the form
Re-open the RentalOrders form in Form View (otherwise, open the Bethesda Car Rental5 database and open its RentalOrders form)

Enter the following information for the first record (ignore any field whose value is not given):

Record	1 of 1
Processed By:	28-485
Processed For:	402-22-9644
Car Rented:	CAM-422
Condition:	Excellent
Tank Level:	Half
Mileage Start:	6422
Start Date:	03/14/2011
Order Status:	Car On Road

Click the Next Record button

and complete the new record with the following data:

Record	2 of 2
Processed By:	80-485
Processed For:	A-378-478-439-384
Car Rented:	AFW-928
Condition:	Excellent
Tank Level:	1/4 Empty
Mileage Start:	2028
Start Date:	03/22/2011
Order Status:	Car On Road

Click the Next Record button

and complete the new record with the following data:

Record	3 of 3
Processed By:	35-079
Processed For:	M-028-662-206-814
Car Rented:	GMM-186
Condition:	Good
Tank Level:	Full
Mileage Start:	10407
Start Date:	03/25/2011
Order Status:	Car On Road

Close the Rental Orders form
Re-open the Rental Orders form

In the first record, add values to the following fields (ignore any field whose value is not given):

Record	1 of 3
Mileage End:	6514
Total Mileage:	92
End Date:	11/28/2009
Total Days:	5
Rate Applied:	24.95
Order Status:	Order Completed

Go to the second record and add values to the following fields:

Record	2 of 3
Mileage End:	2152
Total Mileage:	124
End Date:	03/22/2011
Total Days:	1
Rate Applied:	28.95
Order Status:	Order Completed

Click the New (Blank) Record button and complete the new record with the following data:

Record 4 of 4

Processed By: 80-485

Processed For: 402-22-9644

Start Date: 05/10/2011

Order Status: Order Reserved

Click the Next Record button

and complete the record with the following data:

Record	5 of 5
Processed By:	80-468
Processed For:	368-36-4838
Car Rented:	RBL-618
Condition:	Good
Tank Level:	Empty
Mileage Start:	8266
Start Date:	03/16/2011
Rate Applied:	35.95
Order Status:	Car On Road
Notes:	The customer has indicated that he will need the car for a month; that is, until mid-April 2011. The customer wanted to rent a Standard size car but there was none available. Therefore, although the customer is getting an SUV, we will apply the Monthly Standard rate.

Go to the third record and add values to the following fields:

Record	3 of 5
Mileage End:	10697
Total Mileage:	290
End Date:	03/28/2011
Total Days:	3
Rate Applied:	45.00
Order Status:	Order Completed

Click the New (Blank) Record button

and complete the new record with the following data:

Record	6 of 6
Processed By:	28-485
Processed For:	S-738-384-838-784
Car Rented:	DCC-713
Condition:	Excellent
Tank Level:	3/4 Full
Mileage Start:	5002
Start Date:	04/16/2011
Order Status:	Car On Road

Go to the fifth record and add values to the following fields:

Record	5 of 6
Mileage End:	10114
Total Mileage:	1848
End Date:	04/15/2011
Total Days:	30
Rate Applied:	35.95
Order Status:	Order Completed

Close the database

Lesson Summary

MOUS Topics

Exercises

Yugo National Bank

Open the Yugo National Bank2 database
Start a form in Design View and whose source is the Transactions table. Create it as a continuous form that includes the TransactionTypeID, the TransactionDate, the TransactionNumber, the DepositAmount, the WithdrawalAmount, and the ServiceCharge columns. Save the form as sbfTransactions. Configure to not allow data entry and not to show the navigation buttons
In the Form Footer section, add a text box with the following characteristics:
Name: txtDeposits
Control Source: =Sum([DepositAmount])
Format: $# ##0,00;($# ##0,00)
Still in the Form Footer section, add another text box with the following characteristics:
Name: txtWithdrawals
Control Source: =Sum([WithdrawalAmount])
Format: $# ##0,00;($# ##0,00)
Once again in the Form Footer section, add another text box with the following characteristics:
Name: txtServiceCharge
Control Source: =Sum([ServiceCharge])
Format: $# ##0,00;($# ##0,00)
Use the Visible property to hide the text boxes in the Form Footer section
Start a new form in Design View and whose source is the Customers table. Include the EmployeeID, the DateCreated, the AccountNumber, the AccountType, and the CustomerName fields. Save the form as CustomersTransactions. Configure to not allow data entry
Use the Subform/Subreport button and the Control Wizard button of the Ribbon to add the sbfTransactions sub-form to the form
In the Form Footer section, add a text box with the following characteristics:
Name: txtDeposits
Control Source: =Sum([DepositAmount])
Format: $# ##0,00;($# ##0,00)
Still in the Form Footer section, add another text box with the following characteristics:
Name: txtWithdrawals
Control Source: =Sum([WithdrawalAmount])
Format: $# ##0,00;($# ##0,00)
Once again in the Form Footer section, add another text box with the following characteristics:
Name: txtServiceCharge
Control Source: =Sum([ServiceCharge])
Format: $# ##0,00;($# ##0,00)
Design the form as you see fit. Here is an example:
Close the form