Shelly Cashman Access 2016 | Module 7: SAM Project 1a West Coast Outdoor Advertisers ADVANCED FORM TECHNIQUES GETTING STARTED   Open the file SC_AC16_7a_ FirstLastName _1

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

West Coast Outdoor Advertisers

ADVANCED FORM TECHNIQUES

GETTING STARTED

 

Open the file

SC_AC16_7a_

FirstLastName

_1.accdb

, available for download

from the SAM website.

 

Save the file as

SC_AC16_7a_

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 a

nd 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 by completing the following tasks:

a.

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

for the form to the

States and Billboards

query.

b.

Add the

Location

,

City

,

StateName

,

Illuminated

,

DEC

, and

Type

fields (in

that order) to the form in the approximate location shown in Figure 1 on

the following page.

c.

Adjust the sizing of the controls to approximately match those shown in

Figure 1.

d.

Add the

BillboardID

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

States and Billboard Record Source

Form

and close it.

Figure 1: States and Billboard Record Source Form – Design View

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

Light Gray 1

(1

st

column, 2

nd

row of the Standard Colors palette).

Save and close the

State Master Form

.

Figure 2: State Master Form – Design View

3.

Open the

Advertiser 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 Combo

Box Wizard to create the combo box with these options:

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

AdRep

table as the source of the combo box values.

c.

Add the

AdRepNumber

,

FirstName

, and

LastName

fields to the combo box.

d.

Sort the combo box by the

AdRepNumber

field in

ascending

order.

e.

Remove the check mark from the “Hide key column (recommended)”

check box.

f.

Ensure that

AdRepNumber

is selected as the field in which to store a

value. Select the

Store that value in this field:

option button, and then

select the

AdRepNumber

field.

g.

Assign

AdRepNumber

as the name of the combo box.

h.

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, and then save and close it.

Figure 3: Advertiser Master Form – Design View

4.

Modify the

AdReps and Advertisers Form

to match Figure 4 on the following

page by completing these tasks:

a.

Open the

AdReps and Advertisers 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

as shown in Figure 4.

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

AdReps and Advertisers Form

.

Figure 4: AdReps and Advertisers Form – Design View

5.

Open the

Rentals and Billboards Form

in Design View, and then use the

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

a.

Use the

Billboard

table for the subform.

b.

Select the

BillboardID

,

Location

, and

State

fields from the

Billboard

table

to add to the subform.

c.

Accept the default link (“Show Billboard for each record in Rentals Contact

Query using BillboardID”).

d.

Save the subform as

Billboard 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" mark on

the ruler.)

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 it.

Figure 5: Rentals and Billboards Form – Design View

6.

Open the

Rentals 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

.

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

Rentals Master Form

.

Figure 6: Rentals Master Form – Design View

7.

Open the

AdRep Search Form

in Design View, and then modify it 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 type 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

Etched

.

d.

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

8.

Open the

Advertiser 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: Advertiser Financial Form – Design View

9.

Open the

Billboards 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.

Change the name of the first tab to

Datasheet

. (

Hint:

Do not type the

period.)

c.

Change the name of the second tab to

Graph

. (

Hint:

Do not type the

period.)

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

Figure 8: Billboards by State Form – Design View

10.

Open the

Rentals by Advertiser Form

in Design View, and then make the

following changes:

a.

Delete the

AdvertiserID

control and label.

b.

Save the form, and then switch to Datasheet View.

c.

Resize all columns in the

Rentals by Advertiser Form

to best fit the data

they contain.

Save and close the form.

11.

Open the

AdRep Rental 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

AdRep Rental Data

Form

.

Figure 9: AdRep Rental Data Form – Datasheet Tab

12.

With the

AdRep Rental Data Form

still open in Design View, modify it to match

Figure 10 on the following page by completing these tasks:

a.

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

b.

Edit the chart object, and then change the chart type to

Clustered Bar

as

shown in Figure 10 on the next page.

c.

Change the placement of the legend to

Bottom

, as shown in Figure 10.

Save and close the form.

Figure 10: AdRep Rental Data Form – Chart Tab

13.

Open the

Billboard Master 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 Combo

Box Wizard to create the combo box with these options:

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 check mark 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. Save and close the form.

Figure 11: Billboard Master Form – Design View

14.

Open the

AdRep 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 database objects. Compact and repair your database, close it,

and then exit Access. Follow the directions on the SAM website to submit your completed

project.