New Perspectives Access 2013

Tutorial 6: SAM Project 1a

Physical Therapy Specialists, P.C.

creating forms to display dataName

PROJECT DESCRIPTION

Jennifer Christie wants to view data in the Physical Therapy Specialists database in specific ways, and she asks you to create several forms for her. First, you’ll create a split form so she can see data in the tblTherapist table in a form and in a datasheet at the same time. Then she wants to view data about employees and their managers. Finally, she asks you to create a custom form to display therapists and their current invoices from the tblBilling table data. The custom form should include a control that lets her select the therapist’s data to view by clicking the therapist’s name in a list.

 

GETTING STARTED

·         Download the following file from the SAM website:

o    NP_Access2013_T6_P1a_FirstLastName_1.accdb

·         Open the file you just downloaded and save it with the name:

o    NP_Access2013_T6_P1a_FirstLastName_2.accdb

o    Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access 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 tblTherapist table, save the form as frmTherapistSplitForm, and then close the form.

2.            Open the frmPatient form in Layout View and make the following changes:

a.    Select the control layout (which contains the Patient ID, Patient First, Patient Last, Address, City, State, Zip, Phone, Birth Date, and Gender fields), and then change the control padding to Narrow.

b.    Select the Patient ID control pair, and then change the control margins to Medium.

Save and close the form.

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

Figure 1a: frmEmployeesAndManagers Form in Form View

Microsoft product screenshots used with permission from Microsoft Corporation.Copyright © 2014 Cengage Learning. All Rights Reserved.

3.            Open the frmEmployeesAndManagers form in Layout View. Change the title in the Form Header section to Employees and Managers, and then change the font color of the form title to Black (2nd column, 1st row in the Standard Colors palette). Save the form.

4.            With the frmEmployeesAndManagers form still open in Layout View, make the following changes:

a.    Remove the Location ID 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.)

b.    Reduce the width of the Location ID text box to approximately 0.5 inches.

c.    Anchor the Location ID control pair to the bottom right of the form.

Save the form, but do not close it.

5.            Switch the view of the frmEmployeesAndManagers form to Design View. Change the tab order for the fields in the Detail section so the LocationID 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: frmEmployeesAndManagers Form – Tab Order

Copyright © 2014 Cengage Learning. All Rights Reserved.

 

 

Refer to Figure 2 on the following page while completing Steps 6–7.

 

 

Figure 2: frmLocationsandTherapists Form in Layout View

Copyright © 2014 Cengage Learning. All Rights Reserved.

6.            Create a blank form in Design View, and then make the following updates:

a.    Add the Location ID, LocationName, Address, City, State, ZipCode, and Phone fields (in that order) from the tblLocation table to Detail section of the form.

b.    Reposition the controls to the approximate location shown in Figure 2. (Hint: The left edge of the text box controls should be at the 2-inch mark on the horizontal ruler.)

c.    Select the LocationName and Address text box controls (but not the labels) and resize them to match Figure 2. (Hint: The right edge of the controls should be at approximately the 4-inch mark on the horizontal ruler.)

d.    Save the form with the name frmLocationsandTherapists, but do not close the form.

7.            Add a subform to the frmLocationsandTherapists form (using the Subform Wizard) by completing the following steps:

a.    Insert the subform in the approximate location shown in Figure 2.

b.    Include the TherapistID, First, Last, and Specialty fields (in that order) from the tblTherapist table in the subform.

c.    Accept the default link using the common LocationID field between the form and subform.

d.    Save the subform with the name frmTherapistSubform.

e.    Delete the label associated with the subform.

f.     If necessary, resize and reposition the subform to the approximate location shown in Figure 2.

g.    Switch to Layout View. Resize the Therapist First, Therapist Last, and Specialty fields in the subform to best fit the data they contain.

Save and close the frmLocationsandTherapists form.

 

Refer to Figure 3 and Figure 4 while completing Steps 8–15.

 

Figure 3: frmTherapistBilling Form in Form View

Copyright © 2014 Cengage Learning. All Rights Reserved.


Figure 4: frmTherapistBilling Form in Design View

Copyright © 2014 Cengage Learning. All Rights Reserved.

8.            Open the frmTherapistBilling form in Design View and complete the following steps:

a.    Delete the Therapist Last label and change the caption for the Therapist First label to Therapist.

b.    Select the First and Last controls, and then align their left edges.

Save, but do not close, the form.

9.            With the frmTherapistBilling form still open in Design View, complete the following steps:

a.    Resize the width of the First, Last, and Specialty controls (in the Detail section of the form) so that the right edges of the controls are approximately at the 4-inch mark on the horizontal ruler.

b.    Resize the width of the TherapistID, LocationID, and Certification controls (in the Detail section of the form) so that the right edges of the controls are approximately at the 2.5-inch mark on the horizontal ruler.

10.         With the frmTherapistBilling form still open in Design View, move the Accepts Minors label to the approximate position shown in Figure 3. Save the form.

11.         Add a title to the frmTherapistBilling Form Header section. Use Therapists and Billing for the title instead of the default text.

12.         Make sure the form’s Record Source property is set to tblTherapist, and then add a combo box (using the Combo Box Wizard) in the Form Header section by completing the following steps:

a.    Add a combo box to the approximate location shown in Figure 4. (Hint: Position the pointer at the top of the Form Header and at the 5-inch mark on the horizontal ruler when inserting this combo box.)

b.    The combo box should find a record in the form based on a selected value.

c.    Select the TherapistID, Last, and First fields (in that order) for the combo box.

d.    Do not hide the key column.

e.    Resize the columns to best fit in the combo box.

f.     Use the label Therapist ID for the combo box.

g.    If necessary, resize and reposition the combo box control to approximately match the location shown in Figure 4.

Save the form and close it.

13.         Open the frmBillingSubform form in Design View. Add a calculated control to the frmBillingSubform Form Footer section by completing the following steps:

a.      Insert a text box in the Form Footer section at the approximate location shown in Figure 5 on the following page. (Hint: Position the pointer at approximately the 0.5-inch mark on the vertical ruler and at approximately the 1-inch mark on the horizontal ruler.)

b.      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.)

c.      Set the Name property of the calculated control to txtSumAmount.

Save and close the frmBillingSubform.

Figure 5: frmBillingSubform Form in Design View

Copyright © 2014 Cengage Learning. All Rights Reserved.

14.         Return to viewing the frmTherapistBilling form in Design View. Add calculated controls to the Detail section of the form by following the directions below:

a.    Insert a text box control in the approximate location shown in Figure 4.

b.    Change the Caption property of the label associated with the text box to Number of Invoices.

c.    Add an expression that displays the txtCountBilling control value from the frmBillingSubform subform.

d.    Add a second text box control (below the control you just added) in the approximate location shown in Figure 4.

e.    Change the Caption property of the label associated with the second text box to Total Amount.

f.     Add an expression to the second text box that displays the txtSumAmount control value from the frmBillingSubform subform.

g.    Set the format for this calculated control to Currency.

h.    Resize and position the calculated controls as shown in Figure 4.

i.      Set the Tab Stop property for both calculated controls to No.

Save the form without closing.

15.         Draw a rectangle around the calculated controls and their labels, as shown in Figure 3. Set the rectangle’s properties to match those listed below:

a.    Set the Border Width property to 3 pt. (Hint: The border width is the same as the Shape Outline’s Line Thickness.)

b.    Set the Special Effect property to Shadowed.

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 exit Access. Follow the directions on the SAM website to submit your completed project.

Leave a comment