Homework answers / question archive / New Perspectives Access 2016 | Module 6: SAM Project 1a Healthy Myles Fitness Center CREATING FORMS TO DISPLAY DATA GETTING STARTED ? Open the file NP_AC16_6a_ FirstLastName _1

New Perspectives Access 2016 | Module 6: SAM Project 1a Healthy Myles Fitness Center CREATING FORMS TO DISPLAY DATA GETTING STARTED ? Open the file NP_AC16_6a_ FirstLastName _1

MS Access

Share With

---

New Perspectives Access 2016 | Module 6: SAM Project 1a

Healthy Myles Fitness Center

CREATING FORMS TO DISPLAY DATA

GETTING STARTED

?

Open the file

NP_AC16_6a_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

NP_AC16_6a_

FirstLastName

_2.accdb

by changing the “1” to

a “2”.

o

If you do not see the

.accdb

file extension

in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

Open the

_GradingInfoTable

table and ensure that your first and last name is

displayed as the first record in the table. If the table does not contain your

name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1.

Use the Split Form tool to create a split form based on the

tblTrainer

table, save

the form as

frmTrainerSplitForm

, and then close the form.

2.

Open the

frmClient

form in Layout View and make the following changes:

a.

Select the

control layout

(which contains the

Client ID

,

Client First

,

Client

Last

,

Address

,

City

,

State

,

Zip

,

Phone

,

Birth Date

, and

Gender

fields), and

then in the Arrange tab, change the control padding to

Narrow

.

b.

Select the Client ID label and associated text control, and then change the

control margins to

Medium

.

c.

Save and close the form.

3.

Use the Datasheet form tool to create a datasheet form based on the

tblBilling

table, save the form as

frmBillingDataSheet

, and then close the form.

4.

Use the Multiple Items form tool to create a multiple items form based on the

tblTrainer

table, save the form as

frmTrainerMultiple

, and then close the form.

Refer to Figure 1a on the following page while completing Steps 5–7.

Figure 1a: frmEmployeesAndManagers Form in Form View

1.

Open the

frmEmployeesAndManagers

form in Layout View. Change the title in

the Form Header section to

Employees and Managers

, and then change the

font color of the form title to

Black

(2

nd

column, 1

st

row of the Standard Colors

palette). Save the form.

5.

Switch the

frmEmployeesAndManagers

form to Design View and make the

following changes:

a.

Remove the

Location ID

control pair from the stacked layout. (

Hint

: To

make this control pair easier to manipulate, you may want to move it

below the other controls on the form.)

b.

Reduce the width of the

Location ID

text box to approximately

0.5"

.

c.

Anchor the

Location ID

control pair to the Bottom left corner of the form.

Save the form, but do not close it.

6.

With the

frmEmployeesAndManagers

form still open in Design View, change the

tab order for the fields in the Detail section so the

LocationID

field is the last

field in the tab order. (

Hint

: Refer to Figure 1b on the following page.) Switch the

form to Form View, confirm that it matches Figure 1a, and then save and close

the form.

Figure 1b: frmEmployeesAndManagers Form – Tab Order

Figure 2: frmLocationsAndTrainers Form in Design View

7.

Create a blank form in Design View, and then make the following updates:

a.

Add the

Location ID

,

LocationName

,

Address

,

City

,

State

,

ZipCode

, and

Phone

fields (in that order) from the

tblLocation

table to Detail section of

the form.

b.

Reposition the controls to the approximate location shown in Figure 2.

(

Hint

: The left edge of the text box controls should be at the 2" mark on

the horizontal ruler.)

c.

Select the

LocationName

and

Address

text box controls (but not the labels)

and resize them to match Figure 2. (

Hint

: The right edge of the controls

should be at approximately the 4" mark on the horizontal ruler.)

d.

In the form header, add the title

Locations and Trainers

, and then

change the font to

Arial

.

Save the form with the name

frmLocationsAndTrainers

, but do not close the

form.

8.

Use the Subform Wizard to add a

subform

to the

frmLocationsandTrainers

form

by completing the following steps:

a.

Insert the subform in the approximate location shown in Figure 2.

 

b.

Include the

TrainerID

,

First

,

Last

, and

Specialty

fields (in that order) from

the

tblTrainer

table in the subform.

c.

Accept the default link using the common

LocationID

field between the

form and subform.

d.

Save the subform using

frmTrainerSubform

as the name.

e.

Delete the label associated with the subform.

f.

If necessary, resize and reposition the subform to the approximate location

shown in Figure 2.

g.

Switch to Layout View. Resize the Trainer ID, Trainer First, Trainer Last, and

Specialty fields in the subform to Best fit, so the data is visible.

Save and close the

frmLocationsAndTrainers

form.

Refer to Figure 3 below and Figure 4 on the following page while completing Steps 10–

15.

Figure 3: frmTrainersBilling Form in Form View

Figure 4: frmTrainersBilling Form in Design View

9.

Open the

frmTrainersBilling

form in Design View and complete the following

steps:

a.

Delete the

Trainer Last

label, and then change the caption of the

Trainer

First

label to

Trainer

. (

Hint

: Do not type the period.)

b.

Select the

First

and

Last

controls, and then align their left edges.

Save, but do not close, the form.

10.

Add a title to the

frmTrainersBilling

Form Header section. Use

Trainers and

Billing

for the title instead of the default text.

11.

Make sure the form’s Record Source property is set to

tblTrainer

, and then use

the Combo Box Wizard to add a combo box to the Form Header section by

completing the following steps:

a.

Add a combo box to the approximate location shown in Figure 4. (

Hint

:

Position the pointer at the top of the Form Header and at the 5.5" mark on

the horizontal ruler when inserting this combo box.)

b.

The combo box should find a record in the form based on a selected value.

c.

Select the

TrainerID

,

Last

, and

First

fields (in that order) for the combo box.

d.

Do not hide the key column.

e.

Resize the columns to best fit in the combo box.

f.

Use the label

Trainer ID

for the combo box.

g.

If necessary, resize and reposition the combo box control to approximately

match the location shown in Figure 4.

h.

Switch to Form View, and then use the Combo box to find the record for

Trainer 605, Angela Lorenzo.

Save the form and close it.

12.

Open the

frmBillingSubform

form in Design View. Add a calculated control to the

frmBillingSubform

Form Footer section by completing the following steps:

a.

Insert a text box in the Form Footer section at the approximate location

shown in Figure 5 on the following page. (

Hint

: Position the pointer at just

below the top of the Form Footer section on the vertical ruler and at

approximately the 1" mark on the horizontal ruler.)

b.

Enter an expression in the text box control that displays the total number

of invoices presented in the form. (

Hint

: The calculated control should

contain the Count function and the

BillingID

field.)

c.

Set the Name property of the calculated control to

txtCountBilling

. (

Hint:

Do not type the period.)

d.

Insert a second text box (below the text box you just added) in the Form

Footer section at the approximate location shown in Figure 5. (

Hint

:

Position the pointer at approximately the 0.5" mark on the vertical ruler

and at approximately the 1" mark on the horizontal ruler.)

e.

Enter an expression in the text box control that displays the total amount

of the invoices displayed in the form. (

Hint

: The calculated control should

contain the SUM function and the

Amount

field.)

f.

Set the Name property of the calculated control to

txtSumAmount

. (

Hint:

Do not type the period.)

Save and close the

frmBillingSubform

.

Figure 5: frmBillingSubform Form in Design View

13.

Return to viewing the

frmTrainersBilling

form in Design View. Add calculated

controls to the Detail section of the form by following the directions below:

a.

Insert a text box control in the approximate location shown in Figure 4 on

page 6.

b.

Change the Caption property of the label associated with the text box to

Number of Invoices

. (

Hint

: Do not type the period.)

c.

Add an expression that displays the

txtCountBilling

control value from the

frmBillingSubform

subform.

d.

Add a second text box control (below the control you just added) in the

approximate location shown in Figure 4.

e.

Change the Caption property of the label associated with the second text

box to

Total Amount

. (

Hint

: Do not type the period.)

f.

Add an expression to the second text box that displays the

txtSumAmount

control value from the

frmBillingSubform

subform.

g.

Set the format for this calculated control to

Currency

.

h.

Resize and position the calculated controls as shown in Figure 4.

i.

Set the Tab Stop property for both calculated controls to

No

.

Save the form without closing it.

14.

Draw a rectangle around the calculated controls and their labels, as shown in

Figure 3 on page 5. Set the rectangle’s properties to match those listed below:

 

a.

Set the Border Width property to

3 pt.

(

Hint

: The border width is the same

as the Shape Outline’s Line Thickness.)

b.

Set the Special Effect property to

Shadowed

.

Switch to Form View and test the form, and then save and close it.

Save and close any open objects in your database. Compact and repair your database,

close it, and then exit Access. Follow the directions on the SAM website to submit your

completed project.