3

For each table you should create a basic form that can be used to manage data for the table. Once forms have been created, your users will have a more user-friendly way of entering and managing data in your database. (Microsoft Access Datasheet View is not considered user-friendly).

3.1 Using The Form Wizard

There are many ways to create forms. We discuss the simplest approach here. In Microsoft Access, click the Create tab in the Forms group and then select the Form Wizard:

Figure 3.1 Microsoft Access – Form Wizard

Figure 3.1 Use Form Wizard

The Form Wizard steps you through a sequence of choices where you choose the tables/queries for the form, the fields to appear on the form, the layout for the form, the style, and the title to appear at the top of the form. At this point, you should create two forms in the MyUniversity database including one form for the Department table and one form for the Course table. As the wizard steps you through each case, you should select/enter:

1. All fields of the table to appear on the form (try clicking the  button when it shows to move the fields from the Available Fields column to Selected Fields column)
2. A Columnar layout
3. A style of your choice
4. A title of your choice

The last thing you do with the wizard is choose one of: Open the form and view data, or, to Modify the form’s design – choose to open the form to view data. Note that data appears according to information you provided for data types and properties.

A major difference now is that the user will see just one row at a time. Notice the navigation buttons at the bottom of the form where the user can click to navigate through the rows of the table or to add a new row:

Figure 3.2 Navigation buttons on a form

3.2 Modifying the Form

You can make forms easier to use by adding buttons for common operations for:

1. Adding a new row
2. Deleting the currently displayed row
3. Closing the form

3.2.1 Adding A Button

To add buttons to an existing form, you must open the form in Design View. At this point make sure that wizard capabilities are available – if necessary select the Use Control Wizards: (see below) to turn wizard capabilities on. To add a button you click the Button button:

Figure 3.3 Use Control Wizards when adding controls to a form

Next, you click a location on the form where you would like the control button to be placed. For example, click in the area labeled Form Footer and space will be added to the form’s design to accommodate the button.

Because Use Control Wizards is on, the system will take you through a series of Command Button wizard prompts where you specify the nature of the button type. In this activity, you need to add three buttons on your form. Consider selecting/entering the following at the pertinent prompts:

Figure 3.4 Types of Button categories

A Course form in Design View with three buttons in the Form Footer section:

Figure 3.5 Course form with three buttons in Form Footer

Anytime after creating a button, you can switch to Form View to test your design. If some button is not working as you like then just switch back to Design View, delete the button and try again.

3.2.2 Adding A Label

A label is a control that holds text for display purposes only. By default, Microsoft Access adds a label containing the table name in the Form Header area of the form.

Figure 3.6 Course form with header text in Form Header

To add a label, you must click the Label control, then click (and drag for sizing) where you want the label placed. You can then type the content for the label and adjust its properties for formatting (e.g. font size, color, …).

3.2.3 Adding a Calculated Field

A calculated field involves a calculation using existing fields which can be created in a table, query or form. In this example, we will be creating a calculated field in our form to determine the total number of student study hours for each credit hour. For instance, the expression will include multiplying the value of the credit hours (Course table) in a registered course times 2.5 study hours. The result of the expression will display the total number of hours a student should study for a course.

To add a control where a calculated value will be displayed in your form, you must click the Text Box control. Click (and drag for sizing) where you wish the control to be placed on the form. You will see two controls placed in the form: a label and a text box. For the label, click on the label and enter the text Study hours. In the text box, you would click on the text box and enter a formula. The expression for the number of study hours formula would be:  =[creditHours]*2.5  .

Figure 3.6 Text Box control on a form

Adjust the size and location of the controls as necessary. To do this can be a little tricky. To move a control you must select the control, and then click (and drag) the large dot in the control’s upper left corner:

To resize a control you must position the mouse so you can see a resizing indicator:

3.2.4 Sample Course Form Solution

Figure 3.7 Creating a Study Hours calculated field

Exercises

1. Open the Orders database (see databases for these notes or instructions from your instructor).  Create a new form named OrderDetails.  Select all fields from the OrderDetails table to display in your form. Add a textbox on your form to calculate an extended price formula. Enter the text Extended price as the label.  In the text box, enter an extended price formula calculating quantity times unitprice. Open your form in Form View and view the data to verify your calculated field displays properly.
2. Note that you can modify the properties of fields on a form. When you are in Design View for this form, you can right-click a the extended price field and select Properties. In the displayed Property Sheet pane, select the Format tab. Select the  Format property for this calculated field, click on the drop-down menu, and choose Currency to modify this format property. Save your OrderDetails form.
3. Open the Library database and create individual forms of your choice or as directed by your instructor for each of the Book, Loan and Member tables.

3.3 Advanced Forms

We have discussed simple forms based on single tables above. To manage a complex database, a user will need to work with data that is obtained from more than one table. We will discuss Microsoft Access queries and relationships later in this course. Once you have worked through those subjects, we suggest that you read Appendix A: Forms Involving Multiple Tables.