CSCI A114 / INFO I111 Lab Notes Six First semester 2001-2002

PROJECT SIX: CREATING AN APPLICATION SYSTEM USING

• MACROS,
• WIZARDS, AND
• THE SWITCHBOARD MANAGER

This lab assignment is to be turned in (shown to the lab instructor next class period, that is, on Friday. It is to be graded during the same class period and grades are to be posted at the end of the day or Saturday but not later). The notes below contain highlights from the lab manual, which contains the complete text. Please refer to the lab manual for complete details. Do not use the notes below any more than a sketch of what the lab should be.

Please save all the macros you create, so we can look at them.

Same goes for the switchboard pages and items. .

The management of Bavant Marine Services is pleased with the tables, forms and reports that you have created thus far. They have additional requests, however.

1. They now realize they should have included phone numbers for marinas in the database. They would like for you to add the phone number field to the Marina table, the Billing Summary Report, and the Marina Update form. They would like to be able to type only the digits in the phone number and and then have Access format the number appropriately. If the user enters 6165552312, for example, Access will format the number as (616) 555-2312.

2. Bavant is pleased with the Marina Type combo box you placed in the Marina Update form, which allows users to select a marina type from a list. They realized, however, that this combo box is not visible in Datasheet view.

3. Finally, they have heard about switchboard systems that enable a user to click a button or two to open any form or table, preview any report, or print any report. They would like for you to create such a system for them because they believe this will increase employee productivity.

Please start from the database you created for Lab Assignment 5 (which was Project 5).

Then create the Billing Summary report.

1. Choose Tables, Marina, New Object: AutoForm, select Report, then Report Wizard, click OK.
2. Select Marina Number, Name, Warranty, Non-warranty, push Next.
3. No grouping levels (click Next), no sort order (click Next).
4. Then choose Tabular layout and portrait orientation.
5. Make sure "Adjust the field width so all fields fit on a page" is selected. Click Next.
6. Choose Corporate style, click Next, type title

   Billing Summary Report

7. Click Finish, then if report appears for preview click Close when you're done previewing.

1. Choose Tables, Marina, New Object, Form, Form Wizard.
2. Add all fields except Marina Type and Tech Number.

3. Choose Columnar layout, and Standard style, then type title

   Marina Update Form

4. Click Finish then close the form.

1. Click Forms, right-click Marina Update Form, click Design View.
2. Maximize the window if necessary. If a field box displays click its Close button. Dock toolbox if necessary.
3. Move Warranty to the right, and place xNon-warranty under it.
4. Click Text Box button on toolbox, then click under Non-warranty.
5. Click inside text box and type

   =[Warranty]+[Non-warranty]

6. Place the new field properly, change its label to

   Total Amount

7. Change the format of the new field to Currency: right-click, Properties, Currency.
8. Now make sure Control Wizards button is recessed, click Combo Box and place it on form under the new field.
9. Select the "I will type in the values that I want", push Next, then type:
   • BIR
   • IWO
   • RSO
10. Push Next, click store that value in this field: Marina Type, click Next, type label text:

    Marina Type

11. Click Finish, then place the field properly.
12. Now click Combo Box in toolbox, place it under the previous one.
13. Choose "I want the combo box to look up the values in a table or query", click Next.
14. Click Technician table, Next, add: Tech Number, First Name, Last Name, click Next.
15. Remove check mark on "Hide key column (recommended)" to make sure Tech Number displays.
16. Resize columns properly, click Next, make sure Tech Number is selected, click Next.
17. Click Store value in this field: Tech Number.

18. Type label for the combo box: Technician Number.

19. Click Finish, align the fields, we now add a title.
20. Point to bottom border of the Form Header. Mouse pointer changes shape, drag the bottom lower.
21. Click label in toolbox, place it in the Form Header, type title:

    Marina Update Form

22. Click outside the title, then right click it, Properties.
23. Change Back Color to white, Fore Color to Blue, and Special Effect: Raised.
24. Also change Font Size to 14 and Text Align to Distribute then close the sheet.
25. Align, resize optimally, make sure all looks good. We now change tab stops.
26. Right click Total Amount control, Properties, set Tab Stop to No. Close Prop sheet.
27. Close and save the form. Use the form to see how it works.

1. Using Lookup Wizard

1. Click Tables, right-click Marina, choose Design View.
2. Click Data Type column of Marina Type and choose Lookup Wizard.
3. Click the "I will type in the values that I want", then click Next.
4. Type: BIR, IWO, RSO, then click Next
5. Make sure Marina Type is entered for the label, then click Finish.

1. Click row selector for Marina Type, press INSERT. Add Phone Number field.
2. Data type for it should be Text, type Phone Number for description. Click Input Mask, Build (...).
3. Make sure Phone Number is selected. If you're asked about saving the table say Yes.
4. Click Next, click "With the symbols in the mask like this option button". Close, save.
5. Enter some data using the input mask, see how it goes.

Open the Marina table in the datasheet view to see how it is.

4. Modifying a Report

1. Click Reports, right-click Billing Summary Report, choose Design View, maximize.
2. Select Warranty, Non-warranty in Page Header and Detail Section (use SHIFT). Make them all less wide.
3. Enlarge Name in Page Header and Detail Section. Bring back the resized Warranty and Non-warranty controls.
4. Click the Field List drag Phone Number in the Detail Section. Delete the label.
5. Change Name to Name/Phone in Page Header. Close Field List.
6. Click Text Box and place it in the Detail Section under Non-warranty.
7. Right click new control and choose Properties, Control Source, then type

   =[Warranty]+[Non-warranty]

8. Change Format to Currency, then close Property sheet, then delete the label of the control.
9. Add a label Total Amount in the Page Header section. View the report.
10. Close and save the report.

1. Click Forms, right-click Marina Update Form, choose Design View, maximize window.
2. Click Field List, add Phone Number under Zip Code. Align.
3. View, close, save.

In Design View right-click on the form, choose Tab Order. Click and drag selected rows.

7. Creating and Using Macros.

1. Click Macros, New. First column is action column, second column is comment column.
2. Add Echo, press F6 to move to arguments. Turn Echo On to off. Move to the Comment column and type:

   Turn echo off to avoid screen flicker.

3. Add the following in the same way:

   Hourglass  Turn on hourglass
   OpenForm   Open Marina Update Form
   Hourglass  Turn off hourglass
   Echo       Turn echo on

4. Close, save, name: Open Marina Update Form.

Click Macro, right-click the macro's name, choose Run.

9. Modifying a Macro.

1. Click Macro, right-click Open Marina Update Form macro, choose Design View.
2. Select fourth row, press INSERT. Add

   Maximize   Maximize the window

3. Close and save.

MACRO NAME	ACTION	ARGUMENT(S)	COMMENT
Open Technician Master Form	Echo	Echo on: No	Turn echo off to avoid screen flicker
	Hourglass	Hourglass on: Yes	Turn on hourglass
	Open Form	Form Name: Technician Master Form	Open Technician Master Form
	Maximize		Maximize the window
	Hourglass	Hourglass On: No	Turn off hourglass
	Echo	Echo on: Yes	Turn echo on
Open Marina Table	Open Table	Table Name: Marina	Open Marina Table
		View: Datasheet
	Maximize		Maximize the window
Open Technician Table	OpenTable	Table Name: Technician	Open Technician table
		View: Datasheet
	Maximize		Maximize the window
Preview Billing Summary Report	OpenReport	Report Name: Billing Summary Report	Preview Billing Summary Report
		View: Print Preview
	Maximize		Maximize the window
Print Billing Summary Report	Open Report	Report Name: Billing Summary Report	Print Billing Summary Report
		View: Print
Preview Marina Amount Report	OpenReport	Report Name: Marina Amount Report	Preview Marina Amount Report
		View: Print
	Maximize		Maximize the window
Print Marina Amount Report	OpenReport	Report Name: Marina Amount Report	Print Marina Amount Report
		View: Print
Preview Technician / Marina Report	OpenReport	Report Name: Technician / Marina Report	Preview Technician / Marina Report
		View: Print Preview
	Maximize		Maximize the window
Print Technician / Marina Report	OpenReport	Report Name: Technician / Marina Report	Print Technician / Marina Report
	View: print

11. Creating and Using a Switchboard

1. In the Database window click Tools, Database Utilities, SwitchBoard Manager.
2. Click Yes to create a new SwitchBoard. Click New, type View Form, OK.
3. Add
   • View Table
   • View Report
   • Print Report
4. With Main Switchboard selected click Edit. New, then enter:

   Text               Command             Switchboard 
   View Form          Go to Switchboard   View Form 
   View Table         Go to Switchboard   View Table
   View Report        Go to Switchboard   View Report
   Print Report       Go to Switchboard   Print Report
   Exit Application   Exit Application    N/A
   

5. You're done with the Main Switchboard

Switchboard Page   Switchboard Item               Command              Argument 

Main Switchboard   View Form                      Go to Switchboard    Switchboard: View Form 
                   View Table                     Go to Switchboard    Switchboard: View Table
                   View Report                    Go to Switchboard    Switchboard: View Report
                   Print Report                   Go to Switchboard    Switchboard: Print Report
                   Exit Application               Exit Application     None

View Form          Marina Update Form             Run Macro            Macro: Open Marina Update Form 
                   Technician Master Form         Run Macro            Macro: Open Technician Master Form 
                   Return to Main Switchboard     Go to Switchboard    Switchboard: Main Switchboard 

View Table         Marina Table                   Run Macro            Macro: Open Marina Table 
                   Technician Table               Run Macro            Macro: Open Technician Table 
                   Return to Main Switchboard     Go to Switchboard    Switchboard: Main Switchboard 

View Report        View Billing Summary Report    Run Macro            Macro: Preview Billing Summary Report 
                   View Marina Amount Report      Run Macro            Macro: Preview Marina Amount Report 
                   View Technician/Marina Report  Run Macro            Macro: Preview Technician/Marina Report 
                   Return to Main Switchboard     Go to Switchboard    Switchboard: Main Switchboard 

Print Report       Print Billing Summary Report   Run Macro            Macro: Print Billing Summary Report 
                   Print Marina Amount Report     Run Macro            Macro: Print Marina Amount Report 
                   Print Technician/Marina Report Run Macro            Macro: Print Technician/Marina Report 
                   Return to Main Switchboard     Go to Switchboard    Switchboard: Main Switchboard 

To use the switchboard click the Forms object, right-click the switchboard, click Open.