Excel

USING MICROSOFT ACCESS 2016 Guided Project 7-1

Guided Project 7-1 For this project, you enhance the functionality of a database for a friend’s music collection. You use Design view to create a main form and a subform, and customize the form to add sections, modify properties, enhance the look of the form, and add a calculated control. This project has been modified for use in SIMnet®.

Skills Covered in This Project • Create a main form in Design view. • Edit form in Design view. • Create a subform using the Datasheet

option. • Add Form Header and Form Footer sections

to a form • Add and edit a control in a form. • Change the tab stop property of a control.

• Change the border style property of a form. • Add a subform to a main form using the

SubForm Wizard. • Use an aggregate function on a subform. • Add a control on a main form that refers to a

control on a subform. • Apply a theme to a form. • Add an image to a form.

Step 1: Download start file

1. Open the MusicDatabase-07 database start file. 2. The file will be renamed automatically to include your name. Change the project file name if

directed to do so by your instructor. 3. Enable content in the security warning. 4. Build the main form.

a. Click the Form Design button [Create tab, Forms group] to open a new form. b. Click the Add Existing Fields button [Form Design Tools Design tab, Tools group] to open the

Field List. c. Click the Show all tables link to display the database tables. d. Click the plus symbol next to the Albums table to display the fields. e. Double-click the AlbumID field to add it into the Detail section. f. Double-click the AlbumName field to add it into the Detail section. g. Double-click the Artist field to add it into the Detail section. h. Close the Field List pane. i. Save the form as SongListingOnAlbumsByArtist.

5. Add header and footer sections, label controls, and adjust the size of the sections. a. Right-click inside the Detail section to open the context menu. b. Select the Form Header/Footer option to add Form Header and Form Footer sections to the

form. c. Open the Property Sheet and click the Format tab, if necessary. d. Choose Form in the Selection type box, if necessary, and enter 7.5" in the Width property. e. Choose Detail in the Selection type box and enter 4" in the Height property. f. Choose Form Header in the Selection type box and enter .8" in the Height property. g. Add a label control into the Form Header section and enter Songs on My Albums into the

label. h. With the label selected, change the Width property to 1.8", the Height property to .3", the

Top property to .2", the Left property to 2.25", and select 14 in the Font Size property, and Bold in the Font Weight property.

i. Hover your pointer on the lower border of the Form Footer section until the resize arrow appears.

j. Click, hold, and drag the resize arrow to decrease the height of the section so no space remains in this section.

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 1

1~ Song Listi ngOnAI bu msBy-'rtist

' ' ' I ' ' ' 1 ' ' ' I ' ' ' 2 ' ' 1 I ' '

USING MICROSOFT ACCESS 2016 Guided Project 7-1

k. Save your changes to the form. 6. Modify the controls in the Detail section of the form.

a. Select the AlbumID label. b. Right-click to open the context menu and select Delete. c. Select the AlbumID text box. d. Change the Visible property to No and the Top property to .1". You need this field to have

the relationship with the subform, but it is not a useful piece of information to display to the user.

e. Select the AlbumName text box. f. Change the Width property to 3.2", the Top property to .4", the Left property to 1.4", and

select Transparent in the Border Style property. On the Other tab, in the Tab Stop property, select No.

g. Select the AlbumName label. h. Click the Format tab, change the Caption property to Album, the Width property to .5", the

Top property to .4", the Left property to .8", and select Right in the Text Align property. i. Select the Artist text box and change the Width property to 1.5", the Top property to .7", the

Left property to 1.4", and select Transparent in the Border Style property. On the Other tab, in the Tab Stop property, select No.

j. Select the Artist label, click the Format tab, and change the Caption property to Recording Artist, the Width property to 1.1", the Top property to .7", and select Right in the Text Align property.

k. With the label still selected, press and hold the Shift key and select the Album label.

l. Click the Align button [Form Design Tools Arrange tab, Sizing & Ordering group] and select the Right option. Your form should look similar to Figure 7-95. Don’t worry about the location of the AlbumID field since it is not visible to the user.

m. Save and close the form. 7. Build the subform.

a. Select the Songs table in the Navigation Pane. b. Click the More Forms button [Create tab, Forms group] and then select Datasheet to open a

new form in Datasheet view. c. Position the pointer over the SongID column header, right-click to open the context menu

and select Delete. d. Position the pointer over the AlbumID column header, right-click to open the context menu

and select Delete since you don’t need to include the foreign key in the subform. e. Switch to Design view. All of the controls are selected. Click inside the Detail section to

deselect the controls. f. Select the SongTitle text box and enter 4.6" in the Width property. g. Choose Form in the Selection type box and enter 6" in the Width property. h. Choose Detail in the Selection type box and enter 2.5" in the Height property.

7-95 Design view of main form

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 2

~ 5on gs.5u1J1o r111 _ ....,. __ ....,,S_o-ng""T"'it"'l-e------------.---:-Le- n-gt-:ch,---, - -1

--------------Blan k Space Sty le

Out o f the Woo ds

All You Had to Do Was stay

Shake it Off

. ' ... . ' .. .. .. ' ... . . . . . . ' .. ... ' . ~ .. •Ii 0. 0 • • I • .. • • I I 0. • • . . . -..... -.. .. -.. son· · :n~Ee .. ... . H .. ... .. . ..... .. .... .. ... ,,, ... , .. .. ,, ....

3: 32

3: 51

3 :51

3 :55

3 :13

.. .. . ... . , .. . .... ... .. , ... ... . . ... .... .. ... ... . .. ,, ... ... ... ... . SongTltl

, ,, ... , .. ... ,, ... .. ,,, .. ,,, ... ,,, .

USING MICROSOFT ACCESS 2016 Guided Project 7-1

i. Save the form as SongsSubform. j. Switch to Datasheet view. The

form should look similar to the form shown in Figure 7-96.

k. Close the form. 8. Add the subform onto the main form.

a. Open the SongListingOnAlbumsByArtist form in Design view.

b. Click the Use Control Wizards button [Form Design Tools Design tab, Controls group] to toggle it on if the Use Control Wizards option is not selected.

c. Click the Subform/Subreport button [Form Design Tools Design tab, Controls group]. d. Click inside the Detail section of the form below the fields (near the ½" width tick mark and 1"

height tick mark). The SubForm Wizard launches. e. Select the Use an existing form radio button. f. Select the SongsSubform form from the list and click Next. g. Accept the Choose from a list radio button and the highlighted Show statement and click Next. h. Accept the suggested name for the subform and click Finish to add the subform to the main form. i. Save the form. j. Switch to Layout view. Note that the AlbumID text box displays in Layout view, even though

the Visible property is set to No. k. Click the Next record arrow in the Navigation bar of the main form to advance to the next

record. Verify that the subform updates to display the ten songs on Kenny Garrett’s Seeds from the Underground album.

9. Customize the subform to adjust field widths, and remove the border, Navigation bar, and label. a. Type 40 in the Current Record box of the Navigation bar of the main form and press Enter. b. Scroll down in the subform until the The Chieftains in Orbit (with NASA Astronaut Cady

Coleman) song is visible. This song has the longest title in the database. The entire title should be visible in the text box.

c. Switch to Design view. d. Open the Property Sheet and click the Format tab, if necessary. e. Choose SongsSubform in the Selection type box and

enter 5.6" in the Width property. f. Select Transparent in the Border Style property. g. Click the Select All box in the subform to select the

subform (Figure 7-97). In the Property Sheet, the Selection type updates to show Form and the Select All box in the subform updates to display a black square.

h. Select No in the Navigation Buttons property. i. Click to select the SongsSubform label. j. Right-click to open the context menu and click Delete. k. Save the form. If Access displays a message box that the main form has changed since you

opened it, click Yes to save the changes. l. Switch to Form view. Verify that the border, Navigation bar, and label have been removed.

Notice that the vertical scroll bar displays on the screen because all the songs from the 1989 album cannot fit in the window size of the subform.

m. Click the Next record arrow. The scroll bar disappears because the songs on the Seeds from the Underground album all fit in the window size of the subform.

7-96 Datasheet view of subform

7-97 Select All box in the subform

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 3

~ .e,rowse for Themes .. ,

~ S!ve Current Theme ...

~ Aa El

USING MICROSOFT ACCESS 2016 Guided Project 7-1

10. Add a calculated control onto the subform and enter an aggregate function. a. Switch to Design view. b. Click the Detail section bar of the subform to select it. c. Right-click inside the Detail section to open the context menu and select the Form

Header/Footer option to add those sections to the subform. d. Click, hold, and drag the vertical scroll bar on the subform down to the Form Footer section. e. Hover your pointer on the bottom border of the Form Footer section, and then click, hold,

and drag the resize arrow down to increase the height of that section to approximately .5". f. Add a text box control to the Form Footer section of the subform. g. Open the Property Sheet and click the Data tab, if necessary. h. Click the Build button on the Control Source property. i. Enter =Count([SongTitle]) into the Expression Builder and click OK. j. Enter SFSongTitleCount in the Name property on the Other tab. k. Delete the label that was added with the text box, as it is not needed. l. Save the form.

11. Add a text box to the main form and reference a control from the subform. a. Add a text box control to the right of the ArtistName text box. b. Switch to the Data tab, and click the Build button on the Control Source property. c. Enter =[SongsSubform].[Form]![SFSongTitleCount] into the Expression Builder and click OK. d. Click the Format tab, change the Width property to .3", the Top property to .7", the Left

property to 5", and select Transparent in the Border Style property. On the Other tab, in the Tab Stop property, select No.

e. Click the label control of that text box. f. Click the Format tab of the Property Sheet, enter Total

number of songs in the Caption property and change the Width property to 1.5", the Top property to .7", and the Left property to 3.5".

g. Save the form. 12. Enhance the look of the form.

a. Choose Form in the Selection type box and enter 7.5" in the Width property. (Although you set this earlier, sometimes it adjusts based on other changes made while editing the form.)

b. Click the Themes button [Form Design Tools Design tab, Themes group] to display the gallery of available themes.

c. Click the Integral theme, the fourth button in the first row of the Office group (Figure 7-98). Depending on your computer settings, the theme buttons may display in a different order. The form updates to apply this new theme.

7-98 Theme Gallery choices

d. Click the Insert Image button [Form Design Tools Design tab, Controls group] to open the Image Gallery dialog box.

Download resources

e. Select the Browse button to open the Insert Picture dialog box, locate the AlbumArt.png image file, and click OK. (Downloaded from the Resources link.)

f. Move the pointer to the left of the label in the Form Header section and click to insert the image on the form.

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 4

Songs on My Albums

A.I bum Seeds from l'he Un derground

Recording ArlT:rt Kenny Garren Total nv1nber o f :rongs. 10

Son9 Ti ~ L eng!¼,

*

J. M oc Wiggin~

Hcy11es Here

Detroit

Seeds from the U11 der-g rau n a Du-Wo~e> VJ elco·ine Earth Song Ballad Jarrett Lcivi=, I Bo.n~

__________ 9~3

&26 7 :37

7 :'27 ..f:2 1 8:08

8:1 6 8:43 6:22 3,.38

No Hiter !search

USING MICROSOFT ACCESS 2016 Guided Project 7-1

Step 2 Upload & Save

Step 3 Grade my Project

7-99 Form view of completed main form with a subform

g. Set the following properties for the image: Enter .15" in the Top property and .5" in the Left property.

h. Choose Form Header in the Selection type box and enter 1" in the Height property. i. Click the label control in the Form Header section. Enter .3" in the Top property and 2.25" in

the Left property. j. Choose Form in the Selection type box and enter 6.5" in the Width property. k. Save and close the form.

13. Open the SongListingOnAlbumsByArtist form in Form view. The completed form displays. a. Click the Next record arrow in the Navigation bar of the main form to advance to the next

record. Verify that the subform updates to display the ten songs on Kenny Garrett’s Seeds from the Underground album and that your form looks similar to Figure 7-99.

b. Close the form. 14. Close the database.

15. Upload and save your project file.

16. Submit project for grading.

Access 2016 Chapter 7 Creating Advanced Forms Last Updated: 1/11/18 Page 5