Exp19_Access_Ch07_HOEAssessment – Technical Services 1.0
Yellowstone County Technical Services is a small company that provides technical support for a number of businesses in Yellowstone County, Montana. You have been tasked with updating the customer tracking data-base to expand the input and output capabilities of the system. In your experience with the company, you have seen some of the common errors users make when performing data entry and have also seen what is effective and what is not effective in forms. In addition, you have seen which reports users utilize and have heard suggestions about changes they would like made.
Start Access. Open the file named Exp19_Access_Ch07_HOEAssessment_Technical_Services.accdb. Grader has automatically added your last name to the beginning of the filename.
You have decided to use the Form tool to create a Customers form. This form will enable users to look up customer information. You will make this read-only so users do not accidentally make errors when looking up information.
Select the Customers table in the Navigation Pane. Click the Create tab and click Form in the Forms group. Access creates a new form based on the Customers table. The form opens in Layout view, ready to edit. Click anywhere in the subform at the bottom of the window, click the border of the subform, and then press Delete to delete the subform. The subform is removed. Click Property Sheet on the Design tab in the Tools group if it is not already displayed. The Property Sheet displays on the right side of your screen.
Click the Data tab in the Property Sheet pane. Click the Allow Edits box, which currently displays Yes, and click the arrow at the right. Change the value to No. Repeat that to change the Allow Additions and Allow Deletions property values to No. Close the Property Sheet.
Change the title of the form to View Customer Data (No Edits). Switch to Form view. Attempt to type in the CompanyName box. You should not be able to change the field value.
Attempt to click the New and Delete buttons in the Records group on the Home tab.
You should not be able to add or delete a record. Click Save in the Quick Access Toolbar, and save the form as View Customers. Close the form.
You will use the Form tool to create an Access form to help manage customer call data. This form will enable you to record customer data. You will implement this using a menu.
Select the Calls table in the Navigation Pane. Click the Create tab, and click Form in the Forms group. Right-click the CustomerSatisfaction text box, point to the Change To option, and then select Combo Box from the shortcut menu. The CustomerSatisfaction text box changes to a combo box with an arrow on the right side of the box.
Click Property Sheet in the Tools group on the Design tab if it is not already displayed. Click the Row Source property on the Data tab of the Property Sheet, click the arrow at the right of the Row Source box, and then select Satisfaction Results. Click the Limit To List property, and change the value to Yes. Switch to Form view. Click the CustomerSatisfaction field. Notice an arrow now appears on the right of the box. Type the value 6 for the CustomerSatisfaction field, and press TAB. Access will display an error message that the text you entered is not an item in the list.
Click OK. Change the value for the first record’s CustomerSatisfaction field to 2, and press TAB. You will not receive an error message because the value is in range. Save the form as Calls Drop-Down, and close the form.
The users of the current Edit Customers report have reported problems with the tab order. You will fix the tab order. You will also fix an old form so that the tabs appear in the correct order and remove a tab stop.
Open the Edit Customers form in Form view. Press TAB. When you press TAB, the State field becomes active, rather than the CompanyName. Press TAB five more times, noticing the order in which the fields appear. The fields are not displayed in a logical order.
Switch to Design view, and click Tab Order in the Tools group on the Design tab. Ensure Detail is selected, and click Auto Order. Click OK. Notice State is no longer the second field on the list. The order now reflects the order of the fields, top to bottom. Because this is a Stacked Layout form, Access changes the tab order so it moves down one field at a time. Switch to Form view. Press TAB six times, and verify that the tab order progresses in a logical order. Save and close the form.
Open the Customers Old form in Form view. Press TAB. The Phone field becomes active. You will switch the tab order so Phone is the last field to become active. Switch to Design view. Click Tab Order in the Tools group on the Design tab. Ensure Detail is selected, and click Auto Order. Notice Phone is displayed at the top of the list, which would make it the first field displayed. This is not the logical order.
Click the record selector to the left of the Phone field. Drag the Phone field beneath the Zip field. Click OK. Click the CustomerID field. Display the Property Sheet, if it is not already displayed. Click the Other tab in the Property Sheet. Locate the Tab Stop property, and change the property setting to No. Pressing TAB is no longer necessary as the CustomerID field value is set by the AutoNumber. Switch to Form view. TAB through the fields. The default field is now the CompanyName, and pressing TAB will bring you through Address, City, State, Zip, and Phone, in that order. Note pressing TAB does not bring you to the CustomerID field. Save and close the form.
The Edit Customers form does not display the related call information for each customer. You will modify it so the subform containing the information appears.
Open the Edit Customers form in Design view. Click the Subform/Subreport control in the Controls group on the Design tab. Draw a box in the Detail area of the form under the Phone field. The size does not matter, as it will be resized later. The SubForm Wizard dialog box displays. Click Next to accept the default Use existing Tables and Queries option. Click the Tables/Queries arrow, and select Table: Calls. Double-click the HoursLogged, OpenedDate, and ClosedDate fields. Click Next. Click Next to accept the default relationship. Accept the default Calls subform name, and click Finish. Notice the Calls subform appears at the bottom of the screen. Resize the subform so that the right edge is at the 4.0” mark on the horizontal ruler and the bottom edge is at the 6.0” inch mark on the vertical ruler.
Save and close the form.
You have decided to modify reports to add calculations, and to modify the different header and footer sections of forms and reports to improve the print and on-screen readability. You will be making changes to an existing form and a calculated control to determine if a call is Open or Closed.
Open the Calls Drop-Down form in Design view. Drag the end of the Detail section (appearing right above the form footer) to about 4” on the vertical ruler. Click Text Box in the Controls group on the Design tab. Click beneath the last control in the form (CustomerSatisfaction) to insert the control.
Display the Property Sheet, if it is not displayed. Click the Data tab on the Property Sheet, click the Control Source box, and then click the ellipses next to the Control Source property to display the Expression Builder.
Type IIf(IsNull([ClosedDate]),”Open”,”Closed”) and click OK. This expression will display Open when the ClosedDate is null (in other words, when no value exists in the ClosedDate field) and Closed otherwise. Click the label for the new control (the word Text followed by a number), and press Delete. Switch to Form view. Ensure the first few records display Closed.
Click the Last record Navigation button, and ensure the last record in the table has a value of Open. As the open calls are going to be the most recent, most older calls will be closed, whereas most new calls will be open. Save and close the form.
You will make changes to an existing report to display the number of days each call has been open.
Open the Days Open report in Design view. Click the Text Box control in the Controls group on the Design tab. Click to the right of the ClosedDate text box in the Detail section of the report to add a new field. Click Tabular in the Table group on the Arrange tab. The new field lines up after the final column in the report. Click the Label control for the new column. Double-click to select the existing text. Press DELETE to remove the existing text, and type Days Open. Click the Text Box control for the new column (which currently displays the word Unbound).
Display the Property Sheet, if it is not displayed. Click the Data tab on the Property Sheet, and click Control Source. Click the ellipses to launch the Expression Builder. Type =[ClosedDate]-[OpenedDate] in the Expression box. Click OK. Switch to Report view. Verify the calculation correctly displays the number of days each call was open. Save and close the report.
You will adjust an existing form by adding a logo to the form header and adding a page header.
Open the Calls Drop-Down form in Design view. Click the form logo (to the left of the word Calls) in the Form Header section, and press DELETE. The form logo disappears. Click the Image control in the Controls group on the Design tab. Click to the right of the word Calls in the form header. Browse to the location of your data files, and select a07p1Logo.jpg. Click OK. A logo for Yellowstone appears in the form header.
Click the gray background of the Form Header section. Click the Shape Fill arrow in the Control Formatting group on the Format tab. Select Blue, Accent 1 (first row, fifth column). Recall you can see the names for each color by pointing to a color and waiting for the ScreenTip to appear. Click the Calls label. Click the Font Color arrow in the Font group on the Format tab. Select White, Background 1 (first row, first column). Click the Font Size arrow in the Font group on the Format tab. Change the size to 22. Right-click a blank area, and select Page Header/Footer from the shortcut menu.
A Page Header section displays above the Detail section, and a Page Footer section displays below the Detail section. Click the Label control in the Controls group on the Design tab. Click the left side of the Page Footer section. Type Created by First Last, replacing First with your first name and Last with your last name.
The page footer is not displayed because it will only appear when printed. Click the File tab, and click Print. Click Print Preview. Navigate to the second page. The footer should appear on each page. You will notice the form is too wide to fit on one page left to right. When printed, this might lead to extra pages. Right-click the Calls Drop-Down tab, and select Design View. Click the CallID text box, and change the width to 5.5″ on the horizontal ruler. All other controls adjust as well.
Point to the right edge of the Detail section, and drag it to about 7.5″ on the horizontal ruler. Click the File tab, and click Print. Click Print Preview. As a printed page in Portrait is 8.5″ wide, this report will now fit on one page left-to-right. Click Close Print Preview, and save and close the form.
You will create a new report based on the Calls table and use the group headers and footers to summarize the data.
Click the Calls table. Click the Report tool in the Reports group on the Create tab. Delete the report icon (which appears to the left of the word Calls) from the report header. Click Group & Sort in the Grouping & Totals group on the Design tab to display the Group, Sort, and Total pane at the bottom of the screen, if it is not already displayed. The Group, Sort, and Total pane displays at the bottom of the report. Click Add a group in the Group, Sort, and Total pane, and select CallTypeID. The report will be grouped by the type of call. Resize the RepID field so it takes up only as much room as necessary. The report should fit on one page, left to right.
Switch to Design view. Notice a CallTypeID Header appears between the Page Header and Detail sections. Click More in the Group, Sort, and Total pane. Click the without a footer section arrow and select with a footer section. A CallTypeID footer appears between the Detail and Page Footer sections. Click the HoursLogged text box. Click Totals in the Grouping & Totals group on the Design tab, and select Sum.
A Sum function appears in both the CallTypeID Footer and the report footer. Resize the CallTypeID footer and report footer to be about 1″ tall. You can drag the border down until you see a 1 on the vertical ruler. Resize the text boxes for the new sum fields to be about double the current height. Click the Insert Page Break control in the Controls group on the Design tab. Click the bottom of the CallTypeID footer. Six small dots appear on the left of the CallTypeID footer. Display the report in Print Preview.
All calls with a CallTypeID appear on page 1, with a total of 188 hours appearing on page 2. Due to the number of calls, your totals for CallTypeID 1 will appear on a page by itself. Scroll forward and notice a total of 208 hours for all calls with a CallTypeID of 2.
Save the report as Calls By Type, and close the report.