CMIS 450 

Assignment Setup:
You will need to copy the file LAB06.MDB from your instructor. Do not use earlier versions of the assignment as LAB06.MDB will contain all corrected work up to this point in the project.

This lab will provide you with experience designing and creating forms. You will design forms manually, as well as use various Wizards to assist you.

Exercise 1. Creating and Customizing Forms.
In this exercise, you will create the Reservations form for the Library case study. The final output of your form should resemble the figure shown below, (NOTE: YOURS MAY VARY DUE TO MINOR VARIATIONS IN COLOR STYLE, ETC). Additionally the Form Window Title will vary depending on the version of Windows you are using.
 

To create the form, select the Forms tab in the Database window, then click the New button. Select Design View and the Reservation table, and then click the OK button. Verify that the Control Wizards are turned off (in the Toolbox, the Control Wizards button should not be depressed or appear sunken).

From the View menu, click on Field List. Double-click the title bar of the Reservation table Field List to select all fields and drag the highlighted fields to the center of the detail section in the Form window. Leave about 1 inch at the top of the form. Close the Reservation table Field List.

View the form using Form View. The form should appear in single form mode (with one record displayed). Switch back to Design view and save the form with the form name of Reservations.

To change the default view of the form:
To change the form view from single to continuous form mode, open the Form property sheet by double-clicking the small black box where the rulers meet in the Form window. Select the Default View box, then select Continuous Form from the Default View list and close the property sheet. Now the form will display in continuous form mode with the Form View button. Change the default form view back to Single.

To add a title to the form:
Choose the Form Header/Footer from the View menu (a Form Header and a Form Footer section will appear on the Form window). In the Toolbox, choose the Label button, and position the pointer in the Form Header section about one inch from the left margin and ¼ inch down from the top margin by using the rulers displayed as a guide. Click and drag one and a half inches down and three inches to the right. Note that the Form Header section moved to accommodate the size of the control.

Type West Overshoe Library for the header. Start a new line by holding the CTRL key down and pressing ENTER. Type Reservations. De-select the control by clicking another area of the form. Re-select the control (the area with the Title is called a control - in this case it is a Title or label control) so that changes can be made to the formatting. Change the font to Times New Roman, 20 point bold, with the heading centered (use the toolbar). Save the form again.

To change the color of the control:
Select the Title control. Using the toolbar buttons, change the background color to light blue. Note that the background color of the label changes color. Save the form again.

To create a list box:
Next you will change information in the form so that instead of an ISBN number, the form displays a list box containing book titles. In order to create the list box information, a query must be developed.

Select the Database Window and create a select query containing the ISBN and Title fields from the Item and Title tables, respectively. Select Totals from the View menu and select a First total for the ISBN field and a Group By total for the Title Field. Sort the Title field in ascending order. Save the query as Reservation Titles.
 

Select the Reservations: Form window. In the Toolbox, select the List Box button and position the List Box control above and to the right of the ISBN control. Delete the label for this new control (do not position the new control over the top of existing controls). With the pointer on the List box, click the right mouse button and select Properties from the menu. Set the Row Source property sheet to Reservation Titles. Do not close the Properties window, but choose the Form View and note that the ISBN numbers are displayed in the list box. This is nice, but we want to display titles, not ISBN numbers.

Return to the Design View and in the List Box property sheet, change the Column Count property to 2. Also change the Width property to 2.5. Note that there is also a Column Widths property, but we are not using that particular property at this moment. Now switch to the Form View again and you will see both the ISBN and Title fields in the list box. Try scrolling down through the book titles list and selecting different titles. Note that selection of a title does not update the number in the ISBN control box. Switch back to Design View.

Change the Control Source property of the list box to ISBN. Select Form View again and scroll through the book titles list and the ISBN field will now update as you select each title. Switch back to Design View and change the Column Widths property to 0;2 where: 0=width of the first column, and 2 (inches) = width of the second column. Now switch to Form View and note that only the Title field shows in the list box, but that the ISBN field updates as each title is selected.

Switch to Design View and delete the label for the ISBN control text box. Now select the ISBN control text box and use the right mouse button to open the Property window for the ISBN control. Set the Visible property of the ISBN control text box to No. Choose the Form View. The ISBN control no longer appears on the form; however, the field will still be updated in the Reservations table as you select different titles to reserve for a member. If you prefer, you may make the ISBN control text box visible and position it above the book titles list box so that you can assure yourself that the ISBN control text box continues to be updated. Save the form again to the name Reservations.

To create a combo box:
In this part of the exercise you will create a combo box that displays the last and first name of the members. Start by creating a query named Member Sort with all fields from the Member table, sorted in ascending order by Lastname, Firstname, and Middleinitial (since you know how to create queries, this will not be described in detail).

After creating the Member Sort query, return to the Design view and place a Combo Box control from the Toolbox above and to the left of the Member No. control (look at the original figure for an idea where the Combo Box needs to be placed). Delete the label for the Combo Box control. Access the Properties dialog box for the Combo Box control and set the Row Source property to the Member Sort query. Choose the Form View button and note that you can now display member numbers in the combo box.

Since we want to display member names in the box, return to Design View and change the following properties of the Combo Box: Column Count = 3; Control Source = Member_No. Choose the Form View button and you should be able to click the down arrow of the combo box and see both the Lastname and Firstname fields. If you cannot, you may have to make the combo box larger (wider).

Return to Design View and change the Column Widths property to a setting of 0;1;1 so that the Member_No field is no longer displayed. Also set the Text Align property to Left since we usually left justify names in a window.

Choose the Form View and note that only the Lastname and Firstname fields display, but that the Member_No field updates as each title is selected. Switch back to the Design View. You may at this point opt to make the Member_No control text box invisible by setting its Visible property to No, or you may wish to move the Member_No control text box to a different location on the form. The design option is up to you.

Finally, complete all necessary steps to make the form look like that provided at the beginning of this exercise. Also set the scroll bars to Neither on the Forms Property window. Save the form again to the name Reservations.

Exercise 2. Using the Form Wizards.
In this exercise, you will create and customize forms by using Wizards. You will create the same form you created in Exercise 1, this time by using Wizards.

To create a form using the AutoForm Wizard:
Select the Database window and highlight the Reservation table. Choose the New Object: AutoForm button on the toolbar and the Reservation form will appear showing the first record in the Reservation table. Choose the Design View and turn on Control Wizards (the button should appear sunken). Adjust the size of the form, and move the position of the controls so that there is room for the list and combo boxes to be added to the form.

To use the List Box Wizard:
Place a list box in the same area of the form as you did in the first exercise. The List Box Wizard dialog box will appear. Make certain that the I Want the List Box to Look Up the Values in a Table or Query option button is selected. Choose the Next button.

In the View box, choose the Queries option button. Select Reservation Titles from the list, then choose the Next button. Add all fields to the list and choose the Next button.

Hide the FirstOfIsbn field so that it is not displayed on the final form by setting the widths of the field columns by dragging the Isbn field such that there is zero width to the column. This is accomplished as follows. In the Column title bar, position the pointer between the line between Col1: and Col2:. The pointer changes shape to a thick bar with a multidirectional arrow pointing both left and right. Click and drag the pointer all the way to the left (hiding Col1). Widen Col2: to about 2 to 3 inches and choose the Next button.

Store the FirstOfIsbn field in the database. Choose the Next button. Choose the Store that Value in this Field option button, then select Isbn from the list. This will store the value you select to the ISBN field in a new record in the Reservations table. Now choose the Next button, then the Finish button. Delete the label from the List Box control and view the results. Select the various titles in the list box and note that the Isbn field is updated. Return to the Design view.

To use the Combo Box Wizard:
Place a combo box control in the same area of the form as you did in Exercise 1. The Combo Box Wizard dialog box will appear. Make sure that the I Want the Combo Box to Look Up the Values in a Table or Query option button is selected, then choose the Next button. Set the View radio buttons to select Queries. Choose the Member Sort query from the list and choose the Next button.

Add the Member_No, Lastname, and Firstname fields to the list and choose the Next button. Hide the Member_No field so that it is not displayed on the final form and choose the Next button. Store the Member_No field in the database and choose the Next button. Choose the Store that Value in this Field option button and select Member_No from the list. This will ensure that the appropriate value is stored to the Member_No field in the Reservations table. Choose the Next button, then the Finish button.

View the results in the Form View. Select various members and titles. The form should operate in the same manner as the Reservations form created in Exercise 1. Finally, complete all necessary steps to make the form look like that provided at the beginning of this exercise. Also set the scroll bars to Neither on the Forms Property window. Save the form to the name Reservations Wizard and close the form.

Exercise 3. Creating a Form with a Subform by using Wizards.
In this exercise you will create the Members form that can be used to add new members into the database or to view existing records. The Members form also contains a subform that displays information about the juveniles (children) associated with each member. To create a form that contains a subform, start by creating each form as a standalone form. After both forms have been created, combine both forms together into one form.

To create a form to be used as the main form:
First create a query named Member Adult that contains all fields from the Member table and all fields from the Adult table except for the Member_No field. Close the query after it is saved.

Use the Form Wizard to create a new form based on the Member Adult query. Add all the fields and choose the Next button. Choose Columnar layout, and then choose Standard style. Name the form Member and choose the Finish button.

Return to Design View and make the following changes to the form. Use the Member form that appears below as a guide. Make sure you leave room below the Expr_Date field for the subform that will be added later. Save your changes.

To create a form to be used as a subform:
Begin by creating a query named Member Juvenile that contains the Lastname, Firstname, and Middleinitial fields from the Member table. Add the Adult_Member_No and Birthdate fields from the Juvenile table. Save the query and close it.

Use the Form Wizards to create a new form based on the Member Juvenile query. Make sure that the Select a Table/Query box contains Member Juvenile, and then choose the Form Wizards button. Add all the fields to the form and choose the Next button. Select Tabular and choose Next. Select Stone for the style of the form, and choose the Next button. Save the form as Juvenile, and then choose the Finish button.

To combine the main form and the subform:
You combine the Juvenile subform and Member main form by first ensuring that the Member Form is open in Design view. Now choose the Database Window button on the toolbar. The Database Window will appear in front of the Form window. Drag and drop the Juvenile form onto the Member form and position it as shown in the following figure.

NOTE: Ignore the button on the form labeled Juvenile. That button is added and coded in a later laboratory.

You will need to delete the label for the subform and position the controls on the form as shown in the figure. View the data in the subform. How many children does Nancy Davolio have? Is the data accurate? Why or why not? Return to the Design View.
 

To set relationships between fields in the main form and the subform.
Since the Member_No field in the Member form and the Adult_Member_No in the Juvenile form have different field names, a relationship between the two fields has to be set manually. Do this by opening the properties sheet by selecting the subform control and choosing Properties from the View menu.

Set the Link Master Fields property to Member_No. The Link Master Fields property refers to the field name(s) in the main form or report. Set the Link Child Fields property to the Adult_Member_No field. Note that the Link Child Fields property refers to the field name(s) in the subordinate object, in this case the subform.

Now view the data in the subform. How many children does Nancy Davolio have? Close and save the form. Open the Juvenile form in Design view and change the Default View property of the form to Datasheet. Close and save the form. Open the Member Form. What changes were made to the Member form? What changes were made to the Juvenile subform?

To add a record to a form:
After creating the form, test the design by adding data. Open the Member form in Form View and advance to a new record (use the pointer arrows at the bottom of the form). Add your name as a new member. Advance to the next record to register the change. Open the Member table to view the new record in the table. I will look for the record with your name when grading this part of the project.

To change the tab order of the form:
The sequence in which fields on the form are accessed is termed the Tab Order. For this form you need to change the order so that the Photo field is accessed last.

Open the Member form in Design View. Right-click on the small black box in the upper left corner of the form, where the rulers meet. From the pop-up menu, select Tab Order. Highlight the Photo field by clicking the gray area to the left of the field name. Drag the photo field to where it is before Juvenile. Switch to the Form View and try tabbing through all the fields. The Photo field should be highlighted last.

Exercise 4. Using Expressions In Forms.
In this exercise, you will add the member’s first name and last name in the header section of the form. To do this you will create an expression by using the Expression Builder.

Open the Member form in Design view and choose the Text Box button in the Toolbox. Add the control to the Form Header and delete the label for the Text Box control. Display the properties sheet for the control and select the Control Source property. Note that a down arrow and the Build button appear. Choose the Build button and the Expression Builder dialog box appears.

Double-click the Member folder in the lower left window. Select the <Field List in the middle box. A list of Member form fields is displayed in the right box. Double-click the Firstname field in the list, and choose the Ampersand button. Double-click the Lastname field. Your expression window will now contain the following expression:

[Firstname] & [Lastname]

Choose the OK button and the expression will appear in the Control Source property. Change to Form View to view your results. Note how the first and last names are concatenated. Take the steps necessary to separate the Firstname and Lastname fields with a blank space.

Exercise 5. Displaying and Filtering Information Through Forms.
In this exercise you will display and filter information through a form. Open the Member form in Form view and choose the Filter by Form button on the toolbar. The Filter window appears.

In the Lastname field, type Like "P*". Choose the Apply Filter button on the toolbar to execute the filter. You will now only see records that begin with the letter P. To show all records, deactivate the Apply Filter button.

Reapply the filter by clicking the Apply Filter button again. Return to Filter by Form and save the filter (query) with the name Lastname Like P. Close the filter and the form.

Exercise 6. More With Controls.
In this exercise you use Button Wizards to create a form containing various button and line controls.

To use the Command Button Wizard:
Begin by selecting the Forms tab in the Database window, then click the New button. Select Design View but do not base the form on a table or query.

Make certain that the Control Wizards are on. Choose the Command Button in the Toolbox and draw a 1-by-1 inch square in the Detail section of the form. The Command Button Wizard dialog box will appear.

In the Categories box, select Form Operations. Select Close Form and choose the Next button. Select the "Exit" picture, then click the Next button.

Name this button Exit Form. Choose the Finish button. From the File Menu, choose Save and save the form with the name Stop. Choose the Form View to view your results.

To use the Option Group button:
In the Database window, select the Forms tab and click on the New button. Choose AutoForm: Columnar and select the Copy table. Switch to Design view and resize the form to allow room to add controls to the right side.

Choose the Option Group button in the toolbox. Move the cursor to the right half of the Detail section of the form and create an area about 2-by-2 inches in size. The Option Group Wizard dialog box will appear. In the Label Names box, type the following and then choose the Next button.

Copy 1
Copy 2
Copy 3

Select the option "No, I don’t want to select a default," and choose the Next button. Because the values listed are appropriate for the West Overshoe Library as there are three copies of each book, accept the default and choose the Next button.

Make the necessary selections to store the value in the Copy_No field. Choose the Next button. Select the Raised option button for the style. Select Option buttons for the button type and choose the Finish button.

Delete the label for the control. Change the form to Form view. Scroll through the records and notice that the option group reflects the corresponding Copy_No field. Save the form with the name Option Group Form.

TURN IN FOR GRADING:
Turn-in a single high-density 3.5 inch floppy disk (your work should easily fit on a single disk) with your name printed on the disk using a felt-tip pen (do not use pencil or I will not grade the disks). The disk should have the forms you designed and stored in the LAB06.MDB database file. The disks will be returned to you after I grade them.

You do not have to turn-in your answers to the questions posed in the exercises above. The questions are provided merely to stimulate your thought processes.