Homework answers / question archive / Shelly Cashman Access 2019 | Module 7: SAM Project 1a Personal Insurance, Inc

Shelly Cashman Access 2019 | Module 7: SAM Project 1a Personal Insurance, Inc

MS Access

Share With

---

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

Personal Insurance, Inc.

Advanced Form Techniques

• GETTING STARTED

• Open the file SC_AC19_7a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_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. Personal Insurance, Inc. is a national company that insures homeowners and renters. As a regional manager, you need to be able to create forms that have elements such as combo boxes for finding records, and command buttons to make forms more user-friendly.
   
   Create a new form in Design View by completing the following tasks:
    
   1. Open the Property Sheet for the new form, and then apply the Record Source for the form to the NortheastHomeowners query.
   2. Add the CustomerID field to the form, and then move the control and its label so that the left edge of the label is at the 1.5" mark on the horizontal ruler.
   3. Add the FirstName, LastName, State, and Premium fields (in that order) to the form. The left edges of the text boxes are at the 2" mark on the horizontal ruler.
   4. Adjust the sizing of the controls so that the right edges of the text boxes are at the 3.5" mark on the horizontal ruler.
   5. Save the form using NE Homeowners Source Form as the name, confirm that the form matches Figure 1, and close it.

 

 

2. Open the Claims Master Form in Design View, and then make the following changes:
    
   1. Add a title to the form.
   2. Expand the Form Header section so that the bottom of the Form Header section is at the 1" mark on the vertical ruler.
   3. Change the background color of the Detail section to Light Gray 1 (1st column, 2nd row of the Standard Colors palette).
      
      Confirm that the form matches Figure 2, and then save and close the Claims Master Form.

 

 

3. Open the Homeowners Master Form in Design View. Use the Combo Box Wizard to add a combo box to the Detail section of the form with these options:
    
   1. To display the current data from the bound record source, select I want the combo box to get the values from another table or query.
   2. Select the Customers table as the source of the combo box values.
   3. Add the CustomerID, FirstName, and LastName fields to the combo box.
   4. Sort the combo box by the CustomerID field in ascending order.
   5. Remove the check mark from the "Hide key column (recommended)" check box.
   6. Select Store that value in this field and assign CustomerID as the field in which to store a value.
   7. Assign CustomerID as the name of the combo box.
   8. Move the combo box control and label so that the bottom edges are at the 2" mark on the vertical ruler and the left edge of the control is at the 3" mark on the horizontal ruler.
      
      Confirm that your form matches Figure 3, and then save, and close it.

 

 

4. Modify the ClaimsRep and Claims Form by completing the following tasks:
    
   1. Open the ClaimsRep and Claims Form in Design View.
   2. Add a text box control to the form. The left edge of the control is at the 4.1" mark on the horizontal ruler.
   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. Update the label, using Name as the new text.
   5. If necessary, move and resize the text box control and label so that the left edge of the control is at the 4.1" mark on the horizontal ruler and the bottom edges of the label and control are at the .5" mark on the vertical ruler.
      
      Confirm that the form matches Figure 4, and then save and close the ClaimsRep and Claims Form.

 

 

5. Open the Customers and Renters Form in Design View, and then use the Subform/Subreport Wizard to add a subform with the following options:
    
   1. Use the Renters table for the subform.
   2. Select the PolicyNumber, PersonalProperty, Deductible, and Premium fields from the Renters table to add to the subform.
   3. Accept the default link (Show Renter for each record in <SQL Statement> using CustomerID).
   4. Save the subform as Renters subform (which is the default name).
   5. Resize the width of the subform. The right edge of the subform is at the 7" mark on the horizontal ruler.
   6. If necessary, move the subform so that the top-left edge of the subform control is at the 1" mark on the horizontal ruler and approximately the 2.2" mark on the vertical ruler. The bottom-right edge of the subform control is at the 7" mark on the horizontal ruler and approximately the 4" mark on the vertical ruler.
      
      Confirm that the form matches Figure 5, and then save and close Customers and Renters Form.

 

 

6. Open the Umbrella 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 so that the left edge of the button is at the 4" mark on the horizontal ruler and the top edge of the button is at the 2" mark on the vertical ruler.
      
      Confirm that the form matches Figure 6, and then save and close the Umbrella Master Form.

 

 

7. Open the ClaimsRep 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 using Name_to_Find as the new name.
   3. With the Property Sheet still open, select the Name to Find label, and then change the special effect for the label to Etched.
      
      Close the Property Sheet, save the changes, and then close the form.
8. Open the Claims Payment Form in Design View, and then place a rectangle around the Name to Find label and control. The top-left edge of the rectangle is at the 2.8" mark on the horizontal ruler and the .5" mark on the vertical ruler. The bottom-right edge of the rectangle is at the 5.4" mark on the horizontal ruler and the 1" mark on the vertical ruler. Confirm that the form matches Figure 7, and then save the form, and close it.

 

 

9. Open Customers by Policy Form in Design View, and then complete the following tasks:
    
   1. Add a tab control to the form. The top-left edge of the control is at the 1" mark on the horizontal ruler and the 1" mark on the vertical ruler. The bottom-right edge of the control is at the 5" mark on the horizontal ruler and the 3" mark on the vertical ruler.
   2. Change the name of the first tab using Datasheet as the tab name.
   3. Change the name of the second tab using Chart as the tab name.
      
      Confirm that the form matches Figure 8, then save the form, and close it.

 

 

10. Open the Homeowners by Customer Form in Design View, and then make the following changes:
     
    1. Delete the CustomerID control and label.
    2. Save the form and then switch to Datasheet View.
    3. Resize all columns in the Homeowners by Customer Form to best fit the data they contain.
       
       Save and close the form.
11. Open the Approved Claim Data Form in Design View, and then select the Datasheet tab control. Resize the subform so that the top-left edge is at approximately the .75" mark on both the horizontal and vertical rulers and the width is approximately 5". Confirm that the form matches Figure 9, and then, save, but do not close, the Approved Claim Data Form.

 

 

12. With the Approved Claim Data Form still open in Design View, modify it by completing the following 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.
    3. Change the placement of legend to Bottom.
       
       Confirm that the form matches Figure 10, and then save and close the Approved Claim Data Form.

 

 

13. Open the Customer Master Form in Design View. Add a combo box control to the Form Header. The combo box control is at the 5" mark on the horizontal ruler and the bottom edges of the label and control are at the 1" mark on the vertical ruler. 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 State 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 State to Find to the combo box.
    5. If necessary, move and resize the combo box control so that the left edge of the combo box control is at the 5" mark on the horizontal ruler and the right edge of the control is at the 6" mark on the horizontal ruler. The bottom edges of the label and control are at the 1" mark on the vertical ruler.
       
       Confirm that your form matches Figure 11. Save and close the Customer Master Form.

 

 

14. Open the Rep 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.
       
       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.