ACCT 2381

Lesson6B Exercises

Instructions

Exercises are based on cases from the Adamski text. Some have been modified by the instructor.

Pine Hill Music School

  1. Download the database namedPinehill 6Band save it to your working disk. Be sure to save your design changes often so you do not lose any work.
  1. Add calculated controls to a form.
  2. Open frmContract in Design view.
  3. Move the LessonType, LessonLength, MonthlyLessonCost, MonthlyRentalCost, and TeacherID fields as group to the 2-inch mark on the vertical ruler (LessonType should start at 2 inches).
  4. Add a calculated control that displays the total of the MonthlyLessonCost and MonthlyRentalCost fields.
  5. Place the control underneath the Contract End Date field.
  6. Set the calculated control’s Tab Stop property to No.
  7. Format the value as currency with no decimal places.
  8. Change the label for the calculated control to read “Total Monthly Cost”.
  1. Add a combo box to find records on frmContract.
  2. Set the Record Source for the form to tblContract
  3. Add a combo box in the Form Header section to find ContractID field values. See pages 348-351 for refresher instructions.
  4. The label for the combo box should read “Select Contract ID”.
  5. Test your record-selecting combo box to make sure the specified record displays. Choose Contract ID 3150. The record for Maddie Thorsten should display.
  1. Format a form.
  2. Format all labels in the Detail section (except Total Monthly Cost) to bold red.
  3. Add a 2-point line in the Form Header section.
  4. Position the line under the title.You may need to expand the form header section in order to place the line.
  5. The line should be placed from the left margin to the right margin of the form.
  6. Add a 2-point line at the bottom of the Detail section to match the layout of the line in the header section. HINT: You can use copy and paste to create a line of exactly the same length as the line in the header section.
  7. Add a label below the line in the Detail section in which you enter your name.
  8. Make the font size 14 points.
  9. Format the label to dark red, bold.
  10. Make sure the label box is large enough to fully display your name.
  11. Make sure the tab order for the form is top-to-bottom, left-to-right for the text boxes in the Detail section starting with ContractID.
  12. Save your changes to frmContract and close the form.
  1. Create a new form with a subform.
  2. Create a new blank form in Design view.Set the Record Source to tblTeacher.
  3. Add all the fields from tblTeacher to the Detail section of the form in a columnar layout.
  4. Adjust the field size of the School field so that it is large enough to fully display the longest entry.
  5. Add a title that reads “Teachers and Contracts”.
  6. Add a subform at the bottom of the Detail section. Place the subform at the .25 inch mark on the horizontal ruler. Include all fields from the tblContract in the subform except for TeacherID. Name the subform Contracts.
  7. Expand the subform so that it’s right border is at the 6.5 mark. Autosize the subform columns.
  8. Save the form as TeacherContracts.
  1. Modify the Student Info form by adding a calculated control to calculate the student’s age.
  1. Open the Student Info form in Design view.
  2. Place an unbound text box on the Student Info form below the Birth Date field.
  3. Age can be calculated by deducting the birth date from today’s date. HINT: The Date() function returns the current date.
  4. The answer will be displayed in days. In order to convert the age to years, divide by 365.
  5. Using format tab on the Property sheet, set the format to Fixed and the number of decimals to 0.
  6. The label for this control should read “Age of Student”.
  7. You do not want users to enter the student’s age. You want this field to automatically update each year so that the age is always current. So that users will not attempt to enter data in this field, make the following changes:
  8. Set the tab stop property to No.
  9. Even with the tab stop property set to No, a user can still click in the field and make changes. Using the Propertysheet, select the Data tab. Set the Enabled property to No.
  10. Make sure the Age of Student label and text box align with the other labels and text boxes on the form.
  11. Make sure the tab order is top-to-bottom and that the cursor moves through the fields in order of layout.
  12. Save your changes and close the form.

7.Close the Pinehill database.

Parkhurst Health & Fitness Center

1.Download the Fitness 6Bdatabase and save it to your working disk.

2.Create a custom formin design view named frmProgramsandMembers.Save your design changes often so you don’t lose any work.

a.Set the Record Source property to Program.

b.Add the Progam ID, Program Type, Monthly Fee, and Physical Required forms to the Design Grid in a columnar layout as shown below.

c.Arrange, align, and size the fields to approximately match the example. Adjust the size of the text boxes so that the data displays appropriately.

3.Use the subform control to add a subform based on the Member table.

a.Place the subform in the Detail section along the left margin.

b.Display the FirstName, LastName, HomePhone, JoinDate, and MembershipStatus fields in the subform.

4.Add a calculated field in the Detail section displaying the total number of records in the subform.

a.Save the changes to your form and close the main form.

b.Open the subform in Design View.

c.Expand the footer section of the subform.

d.Add an unbound text box to the subform footer. Enter an expression in the unbound textbox to count the members. (HINT: You can count anything, but when counting try to select a field for which each record will have an entry. In this example, everyone has a last name so you might want to count that. A phone may not be entered for each member so using that field might result in an inaccurate count.)

e.Select the unbound textbox and open the Property Sheet. Set the name of the unbound textbox to Total Members.

f.Save your changes to the subform and close the subform.

g.Open the frmProgramsandMembers main form in Design view.

h.Place an unbound textbox on the main form (this will be the field labeled Total Members in the example).

i.Select the unbound textbox and open the Properties window (if necessary). Using the Expression Builder (under the Control Source field on the Data tab), open the subform and select the field called Total Members. You should now see a count of members in each program displayed on the main form.

j.Set the calculated control’s ControlTip Text property to Total num of members in this program.

k.Set the calculated control’s Tab Stop property to No.

5.Enhance the main form.

a.Use the Title tool to add a form header with the label Programs.

b.Place a medium purple background in the form header.

c.Change the font color for all labels on the main form to medium purple.

d.Add a footer to the main form. Place a label in the footer in dark purple that contains your name.

6.Open the form named frmMembershipAppin design view.

a.Add a calculated field namedExpiration Date which calculates the membership expiration date as one year from the application date (Date Joined+ 365).Place the Expiration Date next to the Date Joined field.

b.Make sure the calculated control has an appropriate label.

7.Close the Fitness database.

Submit the following files to the Lesson 6B Exercises folder in NS Online Assignments:

Pinehill 6B.accdb, Fitness 6B.accdb