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

Subject:MS ExcelPrice:19.99 Bought3

Share With

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”.
  • 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 by completing the following tasks:
   1. Open the Property Sheet for the new form, and then set the record source for the form to the States and Billboards query.
   2. 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.
   3. Adjust the sizing of the controls to approximately match those shown in Figure 1.
   4. Add the BillboardID field to the form, and then move the control and its label to the approximate location shown in Figure 1.
   5. 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

1. Open the State Master Form in Design View, and then make the following changes:
   1. Add a title to the form as shown in Figure 2 on the following page.
   2. Expand the Form Header section to the approximate location shown in Figure 2.
   3. 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

1. 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:
   1. Ensure that the I want the combo box to get the values from another table or query. option button is selected.
   2. Select the AdRep table as the source of the combo box values.
   3. Add the AdRepNumber, FirstName, and LastName fields to the combo box.
   4. Sort the combo box by the AdRepNumber field in ascending order.
   5. Remove the check mark from the “Hide key column (recommended)” check box.
   6. 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.
   7. Assign AdRepNumber as the name of the combo box.
   8. 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

1. Modify the AdReps and Advertisers Form to match Figure 4 on the following page by completing these tasks:
   1. Open the AdReps and Advertisers Form in Design View.
   2. Add a text box control to the form in the position shown in Figure 4.
   3. Create a calculated control that concatenates the FirstName field, a space, and the LastName field. (Hint: Your control should contain the formula =[FirstName]&' '&[LastName]).
   4. Change the label to Name as shown in Figure 4.
   5. 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

1. Open the Rentals and Billboards Form in Design View, and then use the Subform/Subreport Wizard to add a subform with the following options:
   1. Use the Billboard table for the subform.
   2. Select the BillboardID, Location, and State fields from the Billboard table to add to the subform.
   3. Accept the default link (“Show Billboard for each record in Rentals Contact Query using BillboardID”).
   4. Save the subform as Billboard subform (which is the default name).
   5. 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.)
   6. 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

1. Open the Rentals Master Form in Design View. Use the Command Button Wizard to add a command button with the following options:
   1. Select Form Operations as the category and Close Form as the action.
   2. Select the text option button with the text Close Form.
   3. Assign the name Close Form to the button.
   4. 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

1. Open the AdRep Search Form in Design View, and then modify it by completing the following tasks:
   1. Select the Name to Find control. (Hint: Select the control, not the label.)
   2. 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.)
   3. With the Property Sheet still open, select the Name to Find label, and then change the special effect for the label to Etched.
   4. Close the Property Sheet, save the changes, and then close the form.
   5. 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

1. Open the Billboards by State Form in Design View, and then complete the following tasks:
   1. 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.
   2. Change the name of the first tab to Datasheet. (Hint: Do not type the period.)
   3. 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

1. Open the Rentals by Advertiser Form in Design View, and then make the following changes:
   1. Delete the AdvertiserID control and label.
   2. Save the form, and then switch to Datasheet View.
   3. Resize all columns in the Rentals by Advertiser Form to best fit the data they contain.

Save and close the form.

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

1. 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:
   1. Select the Chart tab control, and then select the chart.
   2. Edit the chart object, and then change the chart type to Clustered Bar as shown in Figure 10 on the next page.
   3. 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

1. 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:
   1. Ensure the Find a record on my form based on the value I selected in my combo box. option button is selected.
   2. Add the City field as the field to include in the combo box.
   3. Ensure that there is a check mark in the “Hide key column (recommended)” check box.
   4. Assign the label City to Find to the combo box.
   5. 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

1. Open the AdRep Combo Sort Form in Design View, and then modify the combo box control by completing the following tasks:
   1. Select the Name to Find control. (Hint: Select the control, not the label.)
   2. Open the Property Sheet for the control, and then click the Row Source property.
   3. Click the Build button, and then sort the last names in ascending order.
   4. 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.