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.