CSCI A114 / INFO I111 Lab Notes Thirteen First semester 2000-2001

PROJECT EIGHT:

CUSTOMIZING FORMS USING VISUAL BASIC FOR APPLICATIONS (VBA), CHARTS, AND PIVOTTABLE OBJECTS

First, they would like some improvements to the Marina Update Form. This includes placing buttons on the form for moving to the next record, moving to the previous record, adding a record, deleting a record, and closing the form.

They also want users of the form to have a simple way of searching for a marina using the marina's name.

They also would like an additional form, one that lists the number and name of technicians. This form should include a subform listing details concerning open workorders for each technician. It also should include two charts that graphically illustrate the number of hours spent by each technician in each of the service categories.

Finally they would like a PivotTable form that summarizes workorder data.

Your task is to modify the Marina Update Form in accordance with their requests and to create the two additional forms.

Download the database from here.

1. To Add Command Buttons to a Form:

1. Click Forms, right-click Marina Update Form, choose Design View.
2. Maximize, make sure Control Wizards button is recessed on the toolbox.
3. Click Command Button, place it under Zip Code. Record Navigation: Goto Next Record.
4. Click Next, Text, then type: Next Record. Click Finish.
5. Do the same for Previous Record button, to be placed diretcly to the right of Next Record button.
6. Place a button directly to the right of Previous Record button (Record Operations, Add New Record).
7. Click Next, Text: Add Record, Finish.
8. Place Delete Record button directly to the right of the Add Record button.
9. Place Close Form button (Form Operations, Close Form) to the right of Delete Record.
10. View form.

1. Make sure you're in the Design Grid.
2. Right-click control for the Marina Number field, Properties, make name MarNumb.
3. Close Text Box: Marina Number properties sheet, right-click Add Record button, Build Event.
4. The VBA code for the button displays. This code was generated automatically by Access.
5. Under the DoCmd.GoToRecord , , acNewRec line add:

   MarNumb.SetFocus

   and then press Enter.
6. Close window, click View, test button.

1. Make sure you're in Design Grid, Control Wizards recessed.
2. Click Combo Box on toolbox, place it above Warranty.
3. Choose "Find a record on my form based on the value I selected in my combo box."
4. Click Next, add Name as a field in the combo box, click Next again, resize the column, click Next again.
5. Type &Name to Find as the label for the combo box, Finish.
6. Resize the label, &N means you can press ALT+N to select the combo box.

1. Click View to display the form, use the combo box.

1. Right-click the control for it, Properties. Note the number of your Combo?? box.
2. Click Row Source, point to Build (...), choose Ascending for Sort row in Name field.
3. Close the SQL Statement: Query Builder window and save.
4. Close the Property sheet for your Combo?? box.
5. Right-click the Form selector, choose Properties. Go to On Current.
6. Click Build, Code Builder, OK. You will use your Combo?? name here (mine is Combo29).
7. Type:

   Combo29 = MarNumb ' Update the combo box 

   under Private Sub Form_Current()
8. Close, close Form Properties sheet.
9. Right-click combo box, click Properties, set Tab Stop to No. Close, save.

1. Open Form, use buttons, watch combo box, use combo box by itself.

1. Make sure you're in Design View. Click Rectangle button.
2. Place it to surround the combo box: click top left, drag, release in bottom right place.
3. Right-click border, Properties, Format, Special Effect: Raised. Back Style: Transparent.
4. Close Rectangle Property sheet. Close form, save.

1. Same as before, see how it goes.

1. Tables, Marina, New Object: Query, Design View. Maximize, Add Open Workorders.
2. Double click: Tech Number, Marina Number, Name from Marina.
3. Double click: Location, Category Number, Total Hours (est), Hours Spent from Open Workorders.
4. Change caption of Total Hours (est) to Est Hours
5. Change caption of Hours Spent to Spent Hours
6. Click Ascending in Sort for Tech Number and Marina Number
7. Close and save query as Marinas and Workorders by Technician

1. Tables, Technician, New Object: Form, Design View.
2. Field list must display. Bring Tech Number, First Name, Last Name in, close field list.
3. Organize a bit. All fields should be at the top, on one line.

1. Control Wizards recessed, click Subform/Subreport. Place under Tech Number.
2. The Use existing Tables and Queries button should be selected, click Next.
3. Select Query: Marinas and Workorders by Technician in Tables/Queries. Add all fields, click Next.
4. The "Choose from a list button" should be selected, click Next. Type

   Open Workorders for Technician

   as the name of subform, then click Finish.

12. Modifying the Subform:

1. Database window, Forms, right-click Open Workorders for Technician form, Design View.
2. Close field box, maximize window if necessary. Select Tech Number control in Form Header.
3. Press SHIFT, keep pressed, select Tech Number in Detail too. Press DELETE.
4. Press View, resize Marina Number column, close subform, save.

1. Technician Workorder Data form in Design View: click and drag to resize.

1. Click Insert on the Menu Bar, then click Chart.
2. Place it in Detail section, under subform. Click Queries button in the Chart Wizard dialog box, the Marinas and Workorders by Technician query, then click Next.
3. Select Category Number and Total Hours (est) by clicking them, click Next.
4. Select chart in upper left, click Next, confirmation screen appears. Click Next.
5. Chart Wizard will indicate the fields used for linking, click Next.
6. Type Estimated Hours by Category and click Finish. View.

1. Use methods described above to add a second chart.
2. In this chart select Hours Spent rather than Total Hours (est) and call it

   Hours Spent by Category

1. Click View on the menu bar, select Form Header/Footer. Resize, add Label.
2. Title should be: Technician Workorder Data.
3. Properties:
   • Font Size: 14
   • Text Align: Distribute
   • Special Effect: Raised
4. Close the Label property sheet, View, push Next Record.

17. Creating a PivotTable Form:

1. Database window, Queries, Marinas and Workorders by Technician, New Object: Form.
2. PivotTable Wizard, click OK, then click the Next button.
3. Add Tech Number, Marina Number, Category Number, and Total Hours (est), click Next.
4. Click Layout, drag fields as follows:

             | Tech Num
   ----------+--------
   Category  |   
   Marina Nu |    Sum of Total Hours 

   then click OK, and then, Options.
5. Verify you have checked:
   • Grand totals for columns
   • Grand totals for rows
   • AutoFormat table
   • Preserve formatting
   • Repeat item labels on each printed page
   • Enable drilldown
   • Save password
   • For every page show...
   then click OK.
6. Click Finish, save as Estimated Hours PivotTable

1. Forms, right-click the Estimated Hours PivotTable then click Open.
2. Click Edit PivotTable Object, Excel starts. Adjust columns.
3. Look at the output, when done close everything.