Creating a Data Entry Combo Box

Right now, our form allows us to register students for new classes. However, there is a problem: we have to know the section ID number of every class section we want to enroll a student into. We can make enrolling students much easier by creating a data entry combo box that has more recognizable information.

Unlike the unbound combo box we created earlier, bound combo boxes, which facilitate data entry, are tied to the underlying data in the database. We will create a combo box that allows us to see more easily identifiable data when enrolling a student. Regardless of how we choose a section, we want Access to store the appropriate key field behind the scenes. For example, if we select the section by Course name and section number, we still want Access to store the SectionID field in tblEnrollments.

Since this edit will take place in the subform, we will open it directly.

Step1. To open the subform in Design View, in the Navigation pane,

Right-Click fsubStudentRegistration, Click

We see the subform in Design View:

Just as it did before, the subform does not display as a datasheet in Design View. However, the subform will display as a datasheet in Form view; this is a setting in the Property Sheet.

We will want to replace the section ID with a combo box. To accomplish this, we'll delete the Section ID field to make room. Then we will add the combo box, and figure out how to effectively integrate it into our form.

Step2. To remove the existing SectionID field,

Click the SectionID field, press: Delete key

Note that the label was removed along with the field, which is what we wanted.

Step3. To begin creating the combo box, on the Ribbon,

Click the Form Design tab, Click

Step4. To place the combo box where we want it on the form,

Click above the CourseTitle field

The Combo Box Wizard appears. This time the record navigation option is not available since we are in a subform. We want to accept the default option again because the combo box should get its values from another table or query.

Step5. To move to the next screen in the wizard,

Click

We see a list of tables and queries which could be used as data sources for the combo box. We will use tblSections since it contains the information we need to identify an individual section.

Step6. To select tblSections as the source for our combo box,

scroll down, Click Table: tblSections, Click

We move to the next step of the wizard.

Selecting the Fields for the Combo Box

The next step in the wizard requires a bit of thought; let's consider each of the fields and decide if they should be included.

• SectionKey is needed because it is what matches to the SectionID field we have from tblEnrollments. It is, in fact, the field we want to return from the combo box since it is the key to entering a record successfully. When we return it, it will be stored in the SectionID field in our subform and thus will be able to pull all the related fields as if we had entered the record manually.
• CourseCode is needed because it identifies the course we want.
• SectionNumber is needed because it identifies the specific section of a course.
• SemesterYear is needed because it will help us ensure we are looking at the correct semester.
• Location identifies where the course is taught which may help distinguish particular sections.
• The MeetingTimeID and MeetingDayID fields may not be as useful, since they are only meaningless ID number fields and do not display user-friendly data. The meaningful information is located in another table, but the Combo Box Wizard only allows us to select fields from one object.

NOTE: We could include the meaningful data about meeting times and days if we based our combo box on a query that included the necessary fields.

Now that we've thought things through, we're ready to proceed.

Step1. To select the desired fields for the combo box,

Double-Click SectionKey, CourseCode, SectionNumber, SemesterYear, Location

The fields have now been moved from the Available Fields list to the Selected Fields list.

Step2. To continue,

Click

We can now choose the sort order for our records. Let's sort them by course code and section number so that we can see all sections for a course together.

Step3. To sort by CourseCode and SectionNumber,

Click in the row 1 drop-down, Click CourseCode, Click in the row 2 drop-down, Click SectionNumber

Our results will now be sorted first by course code, and then by section number.

Step4. To continue,

Click

We see a preview of what the drop-down will look like. The key column (SectionKey) is hidden by default, because in general it has no meaning for most users.

Step5. To continue,

Click

The next screen allows us to choose between remembering the value for later use, or storing the value. We want to store the SectionKey value. Remember, whatever fields we use to identify the section we want, Access needs to store the SectionKey value to relate the underlying data.

Step6. To store the selected value in the SectionID field,

Click the "Store that value in this field:" radio button, Click the drop-down, Click SectionID

NOTE: Although the value we're storing is called the Section Key in tblSections, we're storing that value in tblEnrollments, where it's called SectionID.

Now Access knows what to do with the value from our combo box.

Step7. To continue,

Click

In the last screen of the wizard, we can apply a label to the combo box.

Step8. To label the combo box and finish creating it, type:

Course Code Enter key

The combo box is created and we see it in Design View. We are now ready to see how it works.

Viewing the Data Entry Combo Box

Since the subform is usually viewed as a datasheet, we have an additional viewing option: Datasheet View. To see how the combo box works in the subform, let's switch to Datasheet View.

Step1. Switch to Datasheet view.

We see the combo box now showing as the last column in the datasheet. Note that it is currently displaying course codes, since Course Code is the first field displayed after the hidden field in the drop-down.

Let's test the combo box to see how it works.

Step2. To test the combo box, in the first record,

Click the Course Code column, Click

We see a list of course sections that we can choose from, organized by course code and section number. We won't need to know the SectionID value to find the section we want.

Let's close the drop-down list without making any changes, since we can't see whose enrollment would be changed while in the subform.

Step3. To close the drop-down list, press:

Esc key

Let's save our changes.

Step4. Save the subform.

There are some things that we can change to improve our combo box. Let's look at those next.

Now that we have the combo box basically working, let's move the combo box to the first column of the datasheet.

Fixing the Tab Order

Even though we positioned the combo box where the Section ID field was when we were in Design View, it is still listed as the rightmost column in Datasheet view. This is because it was the last field we added to the form. Access will leave it as the last field in Datasheet view unless we change the Tab Order, or the order we move through the fields as we press the tab key.

Let's fix the tab order now.

Step1. Switch back to Design View.

Step2. To correct the tab order, on the Ribbon,

Click the Form Design tab, Click

You see the Tab Order dialog box:

Notice that the combo box is the last item listed. We want the combo box to be first, as it would be in the natural order of fields in Design View.

Step3. To position the combo box in desired place,

Click

This moves the combo box up to the top of the list, where we want it.

NOTE: We can also set the tab order to something other than the actual displayed order on the form. To do this, click the gray box to the left of the field name, then press and drag it up or down to the desired position.

Step4. To save the new Tab Order settings,

Click

Now that we've changed the tab order, let's save our changes, then verify that the combo box is in the correct position.

Step5. Save the subform.

Step6. Switch to Datasheet view.

We can see the Course Code column is now first in the datasheet. Next, we'll try narrowing the focus of our form to just the current semester's enrollments.