Homework answers / question archive / Shelly Cashman Access 2016 | Module 7: SAM Project 1b All Around Outside Maintenance ADVANCED FORM TECHNIQUES GETTING STARTED   Open the file SC_AC16_7b_ FirstLastName _1

Shelly Cashman Access 2016 | Module 7: SAM Project 1b All Around Outside Maintenance ADVANCED FORM TECHNIQUES GETTING STARTED   Open the file SC_AC16_7b_ FirstLastName _1

MS Access

Share With

---

Shelly Cashman Access 2016 | Module 7: SAM Project 1b

All Around Outside Maintenance

ADVANCED FORM TECHNIQUES

GETTING STARTED

 

Open the file

SC_AC16_7b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

 

Save the file as

SC_AC16_7b_

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.

Create a new form in Design View using the following options:

a.

Open the Property Sheet for the new form, and then set the record source

for the form to the

States and Properties

query.

b.

Add these fields to the form, in the order listed, in the approximate

location shown in Figure 1 on the following page:

Address

,

City

,

StateName

,

Services

,

ClientNumber

.

c.

Adjust the sizing and positioning of the controls to approximately match

those shown in Figure 1.

d.

Add the

PropertyID

field to the form, and then move the control and its

label to the approximate location shown in Figure 1.

e.

Save the form with the name

Record Source Form

. (

Hint

: Do not include

the period.)

Close the form.

Figure 1: Record Source Form

2.

Open the

State Master Form

in Design View, and then make the following

changes:

a.

Add a title to the form as shown in Figure 2 on the following page.

b.

Expand the Form Header section to the approximate location shown in

Figure 2.

c.

Change the background color of the Detail section to

Medium Gray

(1

st

row, 3

rd

column of the Standard Colors palette).

Save and close the

State Master Form

.

Figure 2: State Master Form – Design View

3.

Open the

Client Master Form

in Design View. Add a combo box control to the

form in the position shown in Figure 3 on the following page. Use the options

below when creating the combo box using the Combo Box Wizard:

a.

Ensure that the

I want the combo box to get the values from

another table or query.

option button is selected.

b.

Select the

Manager

table as the table providing the values.

c.

Add the

ManagerID

,

FirstName

, and

LastName

fields to the combo box.

d.

Sort the combo box by the

ManagerID

field in

ascending

order.

e.

Remove the checkmark from the “Hide key column (recommended)”

check box.

f.

Ensure that the

ManagerID

field is selected as the field in which to store a

value.

g.

Select the

Store that value in this field:

option button, and then select

the

ManagerID

field.

h.

Assign

ManagerID

as the label of the combo box.

i.

If necessary, move the combo box control and label to match the

approximate location shown in Figure 3.

Confirm that your form matches Figure 3. Save the form, and then close it.

Figure 3: Client Master Form – Design View

4.

Modify the

Managers and Clients Form

to match Figure 4 on the following page

by completing the tasks below:

a.

Open the

Managers and Clients Form

in Design View.

b.

Add a text box control to the form in the position shown in Figure 4.

c.

Create a calculated control that concatenates the

FirstName

field, a space,

and the

LastName

field. (

Hint

: Your control should contain the formula

=[FirstName]&' '&[LastName]

).

d.

Change the label to

Name

. (

Hint

: Do not include the period.)

e.

If necessary, move and resize the text box control and label to match the

approximate location shown in Figure 4.

Save and close the

Managers and Clients Form

.

Figure 4: Managers and Clients Form – Design View

5.

Open the

Clients and Properties Form

in Design View, and then use the

Subform/Subreport Wizard to add a subform with the following options:

a.

Use the

Property

table for the subform.

b.

Select the

PropertyID

,

Address

,

City

, and

State

fields from the

Property

table to add to the subform.

c.

Accept the default link (“Show Property for each record in Client using

ClientNumber”).

d.

Save the subform as

Property subform

(which is the default name).

e.

Resize the width of the subform to match that shown in Figure 5 on the

following page. (

Hint

: The right edge of the subform is at the 6" ruler

mark.)

f.

If necessary, move the subform to match the approximate location shown

in Figure 5.

Confirm that the form matches Figure 5, and then save and close the form.

 

Figure 5: Clients and Properties Form – Design View

6.

Open the

Property Master Form

in Design View. Use the Command Button

Wizard to add a command button with the following options:

a.

Select

Form Operations

as the category and

Close Form

as the action.

b.

Select the text option button with the text

Close Form

. (

Hint

: Do not

include the period.)

c.

Assign the name

Close Form

to the button.

d.

Position the button as shown in Figure 6 on the following page.

Save and close the

Property Master Form

.

Figure 6: Property Master Form – Design View

7.

Open the

Manager Search Form

in Design View, and then modify the form by

completing the following tasks:

a.

Select the

Name to Find

control. (

Hint

: Select the control, not the label.)

b.

Open the Property Sheet for the control, and then change the name of the

control to

Name_to_Find

. (

Hint

: Do not include the period.)

c.

With the Property Sheet still open, select the

Name to Find

label, and then

change the special effect for the label to

Shadowed

.

d.

Close the Property Sheet, save the changes, and then close the form.

8.

Open the

Client Financial Form

in Design View, and then place a rectangle

around the

Name to Find

label and control, as shown in Figure 7 on the following

page. Save the change to the form, and then close it.

Figure 7: Client Financial Form – Design View

9.

Open the

Properties by State Form

in Design View, and then complete the

following tasks:

a.

Add a tab control to the form in the approximate position shown in Figure

8 on the following page, resizing it to match the figure.

b.

Use

Datasheet

as the name of the first tab.

c.

Use

Graph

as the name of the second tab.

Save the changes to the form, and then close it.

Figure 8: Properties by State Form – Design View

10.

Open the

Client Properties form

in Design View, and then make the following

changes:

a.

Delete the

ClientNumber

control and label.

b.

Save the form, and then switch to Datasheet View.

c.

Resize the

Address

and

City

columns to best fit the data they contain.

Save and close the form.

11.

Open the

Manager Client Data Form

in Design View, and then select the

Datasheet tab control. Resize the subform to the approximate size shown in

Figure 9 on the following page. Save but do not close the

Manager Client Data

Form

.

Figure 9: Manager Client Data Form – Datasheet Tab

12.

With the

Manager Client Data Form

still open in Design View, modify it to match

Figure 10 on the following page by completing the tasks below:

a.

Select the Chart tab control, and then select the chart.

b.

Edit the chart object and change the chart type to

Clustered Bar

.

c.

Change the placement of the legend to

Bottom

.

Save and close the form.

Figure 10: Manager Client Data Form – Chart Tab

13.

Open the

Property Search Form

in Design View. Add a combo box control to the

form in the position shown in Figure 11 on the following page. Use the options

below when creating the combo box using the Combo Box Wizard:

a.

Ensure the

Find a record on my form based on the value I selected

in my combo box.

option button is selected.

b.

Add the

City

field as the field to include in the combo box.

c.

Ensure that there is a checkmark in the

Hide key column

(recommended)

check box.

d.

Assign the label

City to Find

to the combo box.

e.

If necessary, move and resize the combo box control and label to match

the approximate location shown in Figure 11.

Confirm that your form matches Figure 11, and then save and close it.

Figure 11: Property Search Form – Design View

14.

Open the

Manager Combo Sort Form

in Design View, and then modify the

combo box control by completing the following tasks:

a.

Select the

Name to Find

control. (

Hint

: Select the control, not the label.)

b.

Open the Property Sheet for the control, and then click the

Row Source

property.

c.

Click the

Build

button, and then sort the last names in

ascending

order.

d.

Save the changes to the query, close the query window, save the changes

to the form, and then close the form.

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.