Homework answers / question archive / New Perspectives Access 2016 | Module 6: SAM Project 1b GRMH Recreation Centers Creating Forms To Display Data   GETTING STARTED Open the file NP_AC16_6b_FirstLastName_1

New Perspectives Access 2016 | Module 6: SAM Project 1b GRMH Recreation Centers Creating Forms To Display Data   GETTING STARTED Open the file NP_AC16_6b_FirstLastName_1

MS Access

Share With

---

New Perspectives Access 2016 | Module 6: SAM Project 1b

GRMH Recreation Centers

Creating Forms To Display Data

 

GETTING STARTED

• Open the file NP_AC16_6b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_6b_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. Use the Split Form tool to create a split form based on the tblTrainer table, save the form as frmTrainerSplitForm, and then close the form.
2. Open the frmMemberEntry form in Layout View and make the following changes:
   1. Select the control layout (which contains the Member ID, FirstName, LastName, Address, City, State, Zip, Phone, BirthDate, and Gender fields), and then in the Arrange tab, change the control padding to Narrow.
   2. Select the Address control pair, and then change the control margins to Medium.

Save and close the form.

1. Use the Datasheet form tool to create a datasheet form based on the tblBilling table, save the form as frmBillingDataSheet, and then close the form.
2. Use the Multiple Items form tool to create a multiple items form based on the tblTrainer table, save the form as frmTrainerMultiple, and then close the form.

Refer to Figure 1a on the following page while completing Steps 5–7.

 

 

 

Figure 1a: frmBilling Form in Form View

 

1. Open the frmBilling form in Layout View. Change the title in the Form Header section to Billing for Training, and then change the font color of the form title to Green, Accent 6 (10^th column, 1^st row of the Theme Colors palette). Save the form.
2. Switch to viewing the frmBilling form in Design View and make the following changes:
   1. Remove the BillingID control pair from the stacked layout. (Hint: To make this control pair easier to manipulate, you may want to move it below the other controls on the form.)
   2. Reduce the width of the BillingID text box to approximately 0.7".
   3. Anchor the BillingID control pair to the Bottom Left of the form.

Save the form, but do not close it.

1. With the frmBilling form still open in Design View, change the tab order for the fields in the Detail section so the BillingID field is the last field in the tab order. (Hint: Refer to Figure 1b on the following page.) Test the form in Form View, confirm that it looks similar to Figure 1a, and then save and close the form.

 

 

Figure 1b: frmBilling Form Tab Order

 

Refer to Figures 2a and 2b on the following pages while completing Steps 8–9.

 

 

Figure 2a: frmLocationsAndEmployees Form in Layout View

 

Figure 2b: frmLocationsAndEmployees Form in Design View

 

1. Create a blank form in Design View, and then make the following updates:
   1. Add the LocationID, LocationName, Address, City, State, ZipCode, and Phone fields (in that order) from the tblLocation table to the Detail section of the form.
   2. Reposition the controls to the approximate location shown in Figure 2b. (Hint: The left edge of the text box controls should be at the 2" mark on the horizontal ruler.)
   3. Select the LocationName and Address text box controls (but not the labels), and then resize them to match Figure 2b. (Hint: The right edge of the controls should be at approximately the 4" mark on the horizontal ruler.)
   4. In the form header, add the title Locations and Employees. (Hint: Do not type the period.)
   5. Save the form with the name frmLocationsAndEmployees, but do not close the form.
   6. Use the Subform Wizard to add a subform to the frmLocationsAndEmployees form by completing the following steps:
      1. Insert the subform in the approximate location shown in Figure 2b.
      2. Include the EmployeeID, First, Last, and Title fields (in that order) from the tblEmployee table in the subform.
      3. Accept the default link using the common LocationID field between the form and subform.
      4. Save the subform using frmEmployeeSubform as the name.
      5. Delete the label associated with the subform.
      6. If necessary, resize and reposition the subform to the approximate location shown in Figure 2b.
      7. Switch to Layout View. Resize the Employee First, Employee Last, and Title fields in the subform to Best Fit so the data is visible.

Save and close the frmLocationsAndEmployees form.

Refer to Figure 3a below and Figure 3b on the following page while completing Steps 10–15.

 

Figure 3a: frmTrainersBilling Form in Form View

 

Figure 3b: frmTrainersBilling Form in Design View

 

1. Open the frmTrainersBilling form in Design View and complete the following steps:
   1. Delete the LastName label and change the caption for the FirstName label to Trainer. (Hint: Do not type the period.)
   2. Select the Trainer and Specialty labels, and then align their left edges.
   3. Select the LastName and Specialty textbox controls, and then align their left edges.

Save, but do not close, the form.

1. Add a title to the frmTrainersBilling Form Header section. Use Trainers and Billing for the title instead of the default text.
2. Make sure the form’s Record Source property is set to tblTrainer, and then use the Combo Box Wizard to add a combo box in the Form Header section by completing the following steps:
   1. Add a combo box to the approximate location shown in Figure 3b. (Hint: Position the pointer at the top of the Form Header and at the 5.5" mark on the horizontal ruler when inserting this combo box.)
   2. The combo box should find a record in the form based on a selected value.
   3. Select the TrainerID, LastName, and FirstName fields (in that order) for the combo box.
   4. Do not hide the key column.
   5. Resize the columns to Best Fit so all data is visible in the combo box.
   6. Use the label Trainer ID for the combo box.
   7. If necessary, resize and reposition the combo box control to approximately match the location shown in Figure 3b.
   8. Switch to Form View, and then use the Combo box to find the record for Trainer 778, Nick Collins.

Save the form, and then close it.

1. Open the frmBillingSubform form in Design View. Add a calculated control to the frmBillingSubform Form Footer section by completing the following steps:
   1. Insert a text box in the Form Footer section at the approximate location shown in Figure 4 on the following page. (Hint: Position the pointer just below the top of the Form Footer section on the vertical ruler and at approximately the 1" mark on the horizontal ruler.)
   2. Enter an expression in the text box control that displays the total number of invoices displayed in the form. (Hint: The calculated control should contain the Count function and the BillingID field.)
   3. Set the Name property of the calculated control to txtCountBilling. (Hint: Do not type the period.)
   4. Insert a second text box below the text box you just added) in the Form Footer section at the approximate location shown in Figure 4 on the following page. (Hint: Position the pointer at approximately the 0.5" mark on the vertical ruler and at approximately the 1" mark on the horizontal ruler.)
   5. Enter an expression in the text box control that displays the total amount of the invoices displayed in the form. (Hint: The calculated control should contain the SUM function and the Amount field.)
   6. Set the Name property of the calculated control to txtSumAmount. (Hint: Do not type the period.)

Save and close the frmBillingSubform.

 

Figure 4: frmBillingSubform Form in Design View

 

1. Open the frmTrainersBilling form in Design View. Add calculated controls to the Detail section of the form by following the directions below:
   1. Insert a text box control in the approximate location shown in Figure 3b on page 7.
   2. Change the Caption property of the label associated with the text box to Number of Invoices. (Hint: Do not type the period.)
   3. Add an expression that displays the txtCountBilling control value from the frmBillingSubform subform.
   4. Add a second text box control (below the control you just added) in the approximate location shown in Figure 3b on page 7.
   5. Change the Caption property of the label associated with the second text box to Total Amount. (Hint: Do not type the period.)
   6. Add an expression to the second text box that displays the txtSumAmount control value from the frmBillingSubform subform.
   7. Set the format for this calculated control to Currency.
   8. Resize and position the calculated controls as shown in Figure 3b on page 7.
   9. Set the Tab Stop property for both calculated controls to No.

Save the form without closing it.

1. Draw a rectangle around the calculated controls and their labels, as shown in Figure 3b on page 7. Set the rectangle’s properties to match those listed below:
   1. Set the Border Width property to 4 pt. (Hint: The border width is the same as the Shape Outline’s Line Thickness.)
   2. Set the Special Effect property to Raised.

Switch to Form View and test the form, and then save and close it.

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.