Using the Form Wizard to Create a Form

As we have seen, using the Create Form button is not adequate when the subform has to pull data from more than one table. There are two main ways to create a form that pulls data from many related tables:

• Create a query that pulls together all of the fields you want and then build a form for the query.
• Use the Form Wizard, which steps you through the process and builds the needed query for you behind the scenes.

We will use the Wizard to build our form since it will make it easier to pull fields from multiple tables into a subform.

Step1. To select the main table for the form, in the Navigation pane, if necessary,

Click tblStudents

Step2. To create the form using the Form Wizard, on the Ribbon,

Click the Create tab, Click 

You see the first screen of the Form Wizard which allows you to choose the fields you want from tblStudents. The easiest way to get all the fields we need is to move all the fields to the new form, then remove the student_id field.

Step3. To move all of the fields of the table onto the new form,

Click 

All fields move from the list of Available Fields to the Selected Fields list.

Step4. To remove the student_id field from the Selected Fields list,

scroll to the top of the list, Click student_id, Click 

The top field is still selected, so if we start adding additional fields now they will show towards the top of the list. We would prefer for new fields to be added at the bottom, so that their default position will be after the student information. While we can always change field locations later, that would require additional time to edit the fields. We'll select the last field in the Selected Fields list to position the selection at the bottom before adding fields from other tables to the form. This will result in new fields being added to the bottom of the list where we want them.

Step5. To select the last field in the Selected Fields list,

scroll to the bottom of the Selected Fields list, Click date_enrolled

Now that we have the student information we need on the main form, we can select the fields we need for our subform. These fields come from several other tables.

Automatically Filling Field Values in a Form

Since one of the main uses of forms is data entry, as designers, we want to reduce or eliminate the possibility of a user entering bad or duplicate data using our forms. One way we can lessen the possibility of this happening is to have a form automatically fill data already stored in our database. An example of this could be designing a form to automatically look up a student's first and last name when we enter the student's id number. In our database, we would like information about a course section to be automatically populated in the form. Let's consider the following relationship:

In our form, we want to have Course Title, Section Number, and Semester Year automatically fill when we select a particular course section by Section ID. To make this happen, we need to include the Section ID field from tblEnrollments. By including the foreign key field from the related table, Access can look up the data in the remaining fields in our form.

In our case, we're not adding new courses or sections, we're just adding students to existing course sections. Since Access looks up the values for the Course Title, Section Number, and Semester Year fields when a Section ID is selected, we are reducing the user's need to re-enter data into these fields manually. Manually re-entering data is one of the most common ways to introduce bad or duplicate data into a database. Recall that duplicate data in a database is a violation of the rules of normalization introduced in Access: Structuring & Relating Data.

NOTE: The Course Title field is being pulled from tblCourses. This works because tblSections is on the many side of the relationship with tblCourses.

Since we designed this form with the task of assigning students to sections in mind, we're choosing to set it up to make adding new course sections impossible. If we did want to use this form to add new course sections, we would have to choose the unique Section ID (SectionKey) from tblSections.

We'll use the wizard to add the rest of the fields that we need for our form.

Step1. To add data from another table, from the Tables/Queries drop-down list,

Click , Click Table:tblEnrollments

NOTE: You may need to scroll up in the drop-down list to see Table: tblEnrollments.

You see the list of fields in the table in the Available Fields column.

Step2. To move the SectionID field to the Selected Fields area,

Double-Click SectionID

Step3. In the same manner, use the table below to add the fields listed to the form:

Note that we could add the Grade field at the same time as we add the SectionID field since they are from the same table. Similarly, we could add the CreditHours field at the same time as the CourseTitle field. As mentioned before, we're doing it this way to control the order; remember, we could also reorder the fields at a later point.

Step4. Once you have added all of the above fields, to continue,

Click 

Choosing How the Data is Grouped

We are asked to select a view for the data. The view refers to how the data will be grouped in our form. We can see a preview of each arrangement and decide which one will best meet our needs.

The first view has our data grouped by tblStudents. This organization puts all of the student fields into the main form area at the top, and the rest of the fields below, in a subform. This seems like a reasonable way to organize our form, but let's look at the other options before we make a decision.

We'll start with the by tblEnrollments option.

Step1. To get an alternate view of the data,

Click by tblEnrollments

In this view, all of the fields are together in the main form; there are no subforms. This might seem reasonable, except that once the form is created, a new line will appear for each course enrollment; that is, if a student were enrolled in more than one course, that student's name would appear multiple times in the list. For our purposes, we want all of a student's enrollments to appear together, without repeating each student's name.

Step2. To preview a different view of the data,

Click by tblSections

This creates more of a class roster view, where information about the specific class section is in the main form, and a list of students is in the subform. This might be useful if we wanted to view a specific class and enroll multiple students at a time.

Step3. To preview another way to organize the data,

Click by tblCourses

This creates a top level view of each course. For each course, there would be two subforms: a list of its Sections, and the class roster for each Section.

This ability to easily display different views shows the power of the Form Wizard, in that we can use it to easily switch what is displayed in our main form and subform.

For the purposes of enrolling students into their course sections, by tblStudents view is the most appropriate. Let's return to the original grouping.

Step4. To return to the original view,

Click by tblStudents

Let's look at the other option on this step of the wizard.

Exploring the Linked Forms Option

The two radio buttons at the bottom of the tblStudents view give us additional options for how we view the subform. We want to consider which of these will better suit our needs.

The first option, currently selected, is Form with subform(s). This option embeds the subform in the main form. Let's see how using linked forms would be different.

Step1. To get a sense of what linked forms would look like, underneath the preview section of the dialog box,

Click the Linked forms radio button

The display indicates that this option would associate the subform with a command button. We would have to click the button to open the subform.

This approach to building forms can be especially useful in situations where the main form has many subforms, because it allows us to launch the one needed at a given time by clicking its button. However, there is a significant drawback to using command button driven subforms, because they lose their context when opened.

In other words, if we were looking at a record for a specific student and opened up their registration subform, we would no longer see information about the student we were viewing; we would only see their existing enrollments in the subform without their name and other demographic information. We want to be able to see the student's information as we are working with their enrollments, so we will return to the Form with subform(s) view.

Step2. To return to Form with subform(s) view and continue making the form,

Click the Form with subform(s) radio button, Click 

The next screen allows you to choose how the data in your subform is displayed. Datasheet view is the most common since it is easy to navigate in the context of a subform. It is already selected as the default option.

Step3. To accept the Datasheet view layout,

Click 

Access moves to the next step of the form wizard.

Naming the Forms

The last screen of the wizard allows us to specify names for the form and subform.

Step1. To set the main form name, in the Form field,

Double-Click the current text, type: frmStudentRegistration Tab key

Access also asks for a name for the subform, because it creates a separate object for it. This could be helpful if we want to make specific edits to the subform later.

Step2. To name the subform, in the second form field, type:

fsubStudentRegistration

NOTE: The "frm" is the standard prefix for a form, and "fsub" is the standard prefix for a subform. Technically, a subform is still a form, but it's connected to another form.

In the bottom of the form, we can choose to open the form to view or enter information (i.e., in data entry mode) or go directly to modifying the form's design. We will accept the default option and open the form in data entry mode.

Step3. To finish the wizard,

Click 

The form is displayed with information for the first student, Gregory Wells, with his enrollment information in the subform:

By using the wizard to add additional fields, it is much easier to recognize the information in the subform.

Now that our form is created, let's use it to modify and add records.