Shelly Cashman Access 2019 Module 5:SAM Project 1a

Personal Insurance, Inc.

MULTIPLE-TABLE FORMS

GETTING STARTED

• Open the file SC_AC19_5a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_5a_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. Personal Insurance, Inc. is a national company that insures homeowners and renters. As a regional manager, you need to be able to modify tables and create multiple-table forms.

Open the Customers table in Design View. Use the Input Mask Wizard to create an input mask for the PhoneNumber field. Store the data without symbols in the mask. Save the change to the Customers table without closing it.

2. Switch the Customers table to Datasheet View, and then add a new field named CustomerNotes at the end of the table. Use Long Text as the data type for the field. Save the change to the table.

3. With the Customers table still open in Datasheet View, update the record for customer Xavier Wilcox (who has the CustomerID 11010) by completing the following tasks:

1. 1. Using the input mask, enter 717-555-3443 in the PhoneNumber field.
   2. Enter the text Has two rental properties in the CustomerNotes field.

Close the table.

4. Open the Claims table in Design View, and then add a new field with the name ClaimEstimate following the ClaimAmount field. Use Attachment as the data type for the field, and use Damage Estimate as the caption property for the field. Save the change to the table, and then close the table.
5. Modify the Customer Data Entry Form by completing these tasks:

1. 1. Open the Customer Data Entry Form in Design View.
   2. Open the field list for the form.
   3. Add the PhoneNumber field to the form directly above the Region label and text box. The left edge of the PhoneNumber text box is at the 5" mark on the horizontal ruler and the top of the label and control are approximately at the 1.3" mark on the vertical ruler.

Save the changes to the form, confirm that it matches Figure 1, and close the

Customer Data Entry Form.

Figure 1: Customer Data Entry Form—Design View

6. Select the Umbrella table, and create a new form in Design View. Update the form by completing these tasks:

1. 1. Add the PolicyNumber, CustomerID, Premium, and Coverage fields to the Detail section of the form. The left edges of the controls are at the 3" mark on the horizontal ruler. The controls are separated vertically by approximately 0.2". If necessary, reposition the controls and their labels after adding them to the form.
   2. Save the form with the name Umbrella Data Entry Form but do not close the form.
   3. Add a title to the form. The title of the form should match the name of the form.

Save the form, confirm that it matches Figure 2, and close the Umbrella Data Entry Form.

Figure 2: Umbrella Data Entry Form—Design View

7. Open the Customer Master Form in Design View, and ensure that the Use Control Wizards button is selected. Then, use the Subform/Subreport Wizard to add a subform with the following options:

1. 1. Use the Homeowners table for the subform.
   2. Select the PolicyNumber and Premium fields from the Homeowners table to add to the form.
   3. Accept the default link (Show Homeowners for each record in <SQL Statement> using CustomerID) to link the main form to the subform.
   4. Save the subform as Homeowners subform (which is the default name).
   5. If necessary, reposition the subform so that the top-left edge of the subform control is at the 2.5" mark on the vertical ruler and the .5" mark on the horizontal ruler. The bottom-right edge of the subform control is at 3.5" mark on the vertical ruler and the 5.5" mark on the horizontal ruler.

Save and close the form.

Figure 3: Customer Master Form—Design View

8. Open the Customer-Renters Master Form in Design View, and then modify it by resizing the width of the subform so that the right edge is at approximately 7.5" on the horizontal ruler. Confirm that the form matches Figure 4, and then save but do not close it.
9. With the Customer-Renters Master Form still open in Design View, modify the form title by completing the following tasks:

1. 1. Change the font color of the title to Dark Red (1st column, 7th row of the Standard Colors palette).
   2. Change the font size of the title text to 22 point.
   3. Apply the Etched special effect to the title.

Save the form, confirm that it matches Figure 4, and close the Customer-Renters Master Form.

Figure 4: Customer-Renters Master Form—Design View

10. Open the Claims Financial Form in Design View, and then modify the form by completing the following tasks:

1. 1. Delete the ClaimRepNumber control and the accompanying label.
   2. Change the label for the ClaimAmount control using Approved as the new label name.

Save and close the form.

11. Open the Umbrella Customer Form in Design View, and then modify the form to match Figure 5 by completing the following tasks:

1. 1. Select the FirstName, LastName, State, Premium, and Coverage controls and labels simultaneously.
   2. Adjust the spacing of these fields to have equal vertical spacing.

Save, but do not close the form.

12. With the Umbrella Customer Form still open in Design View, change the tab order so that users tab to the LastName field immediately after the FirstName field. Save and close the form.

Figure 5: Umbrella Customer Form—Design View

13. Open the Basic Homeowners Form in Design View, and then change the background color of the form to Light Gray 2 (1st column, 3rd row of the Standard Colors palette). Save the form, but do not close it.