CSCI A114 / INFO I111
Lab Notes Ten

First semester 2000-2001

PROJECT FIVE: ENHANCING FORMS WITH OLE FIELDS, HYPERLINKS, AND SUBFORMS

The management of Bavant Marine Services has found that it needs to maintain additional data on its technicians. Managers need to store the start date of each technician in the database. They also would like for the database to contain a description of the technician's specialties, the technician's picture, and the address of the technician's Web page.

They also would like to have a form created that incorporates some of the new fields along with some existing fields. In addition, the management would like for the form to contain the marina number, name, warranty amount, and non-warranty amount for the marinas of each technician. They would like to be able to display two or three marinas on the screen at the same time, as well as be able to scroll through all the marinas of a technician and be able to access his or her Web Page directly from the form. They will need queries that use the Start Date and Specialties fields as criteria.

Finally, they are concerned the database is getting larger than necessary and would like to compact the database to remove any wasted space.

You must help Bavant Marine Services make these changes.

Start by getting the initial data for this project.

This project creates a form.

This form has:

  1. new fields: Specialities, Picture, Web Page
  2. a subform

1. Start by changing the structure of the Technician table. Add four fields: Start Date, Specialties, Picture and Web Page.

Open Technician in Design View, and add the fields in this order after the YTD Earnings field. Here are the details:

Field Name Data Type Description
Start Date Date/Time Start Date
Specialties Memo Note containing details of technician's specialties
Picture OLE Object Picture of technician.
Web Page Hyperlink Address of technician's web page.
Close Technician and save changes.

2. Open the Technician table in Datasheet view. Add the following data to the Start Date column:

If the dates do not display with four digit years, click the Start menu, click Settings, and then click Control Panel. Double-click Regional Settings, click the Date tab, and then change the short date style to MM/dd/yyyy. Click the OK button after that.

3. Update the Memo fields as follows:

Specializes in fiberglass repair. Can handle routine engine maintenance and also minor canvas repair.(Anderson)
Can handle all types of engine maintenance and repair. Can also do electrical work. (Nichols)
Specializes in electrical problems including electronics repair. Can also handle routine engine maintenance. (Gomez)
Note that you can resize the column containing the Specialties field and the rows of the table so that a larger portion of the Specialties field text will display (if you want). Drag the lines between column headings, and/or those between the row selectors. Instructor shows.

4. Check the home pages of the three technicians:

  1. Trista Anderson
  2. Ashton Nichols
  3. Teresa Gomez
Download their pictures (in .bmp format). To insert data into an OLE field you will use the Insert Object Command on the OLE field's shortcut menu. (Choose Create from File and then browse for your pictures).

5. Update the Hyperlink fields: choose Hyperlink on the shortcut menu, then click Edit Hyperlink..., type the hyperlinks of the three technicians then click OK. When you're done simply close the table and save the changes.

You're now ready for the main part of the lab.

ADVANCED FORM TECHNIQUES

1. Choose Forms, then right-click Create form by using wizard and choose Open.

In Tables/Queries choose Technician, add selected fields:

Then from Marina table select: Then click Next. You will want to view your data by Technician. Make sure that Form with subform(s) is selected (that's how marina data will be included) and click Next again.

3. Choose the Datasheet layout for the subform, then click Next.

4. Choose the Standard style, click Next.

5. Type

Technician Master Form
as the title of the form, and
Marinas
as the name of the subform. Click the Finish button, then close the form by clicking its Close button.

Now we modify the form design a bit.

First you can modify the subformdesign, by adjusting the columns.

1. Then open the Technician Master Form in Design View. If toolbox does not display click Toolbox button on toolbar. Dock the toolbox at the bottom of the screen before going further.

2. Move the Picture way right. Make it way bigger. Move Specialties closer to it. Move the label for Specialties above the field. Resize the label. Reorganize the remaining fields.

3. Change label alignment as follows: select the label for Tech Number then press SHIFT and keep pressed while clicking on the remaining labels. Right click the Web page field then choose Properties. Change Text Align to Right. Close Properties sheet.

4. Now resize the labels, then set their size to the optimum size.

5. Change the Size Mode of the Picture to Zoom by using the shortcut menu, Properties, Size Mode property and Zoom. Then close Properties.

6. Change the special effects and colors of labels: select all labels including the Marinas label for the subform. Right-click any one of them, go to Properties, Special Effect, choose Raised. Change the Fore Color to blue, and the Back Color to white (use the Build button, ...)

7. Click the View button (top left) to view the form, then return to the design grid to adjust the size of the picture. Go back and forth until the size is just about right.

8. Add a title to the form: enlarge the Form Header section. Click Label button on toolbar then click inside the Form Header section. Type title: Technician Master Form. Deselect title by clicking outside it, then right click, choose Properties, Special Effect: Etched. Go down to Font Size, and make it 12. Make Font Weight Bold. Close Properties, resize label. Close form, save.

VIEW DATA AND WEB PAGES USING FORM

Open the form, note that there are two rows of navigation buttons. Check out the web pages. Close the form.

USING DATE AND MEMO FIELDS IN A QUERY.

Include the slashes in date fields.

Use wildcards as when you search in a file (like "*electrical*") with memo fields.

COMPACTING THE DATABASE

With database closed: Tools, Database Utilities, Compact and Repair Database..., then select the database to be compacted, give new name, then go for it. Check the sizes of the uncompacted and compacted databases.

SUMMARY

Project 5 introduced you to some additional field types. To maintain the additional data required at Bavant Marine Services, you needed to learn how to create and work with date, memo, OLE, and Hyperlink fields. You have added the new fields requested by Bavant Marine Services to the Technician table in its database.

You also learned how to use such fields in a form. You then learned how to build a form on a one-to-many relationship. One technician displayed on the form at the same time as the many marinas serviced by that technician. You learned how to use the form to view technician and marina data as well as to view the technician's Web page. You saw how to use date and memo fields in queries to answer two important questions for the organization.

Finally, you learned how to compact a database to remove any wasted space.

Here's what your database should look like at the end.

Last updated: October 3, 2000 by Adrian German for A114/I111