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,
Step2. To remove the existing SectionID field,
Click the SectionID field,
press: Delete key
Step3. To begin creating the combo box, on the Ribbon,
Click the Design tab, Click
Step4. To place the combo box where we want it on the form,
Click above the CourseTitle field
Step5. To move to the next screen in the wizard,
Step6. To select tblSections as the source for our combo box,
scroll down, Click Table: tblSections, Click
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
Step2. To continue,
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
Step4. To continue,
Step5. To continue,
Step6. To store the selected value in the SectionID field,
Click Store that value in this field:,
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.
Step7. To continue,
Step8. To label the combo box and finish creating it, type:
Course Code Enter
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.
Step2. To test the combo box, in the first record,
Click the Course Code column, Click
Step3. To close the drop-down list, press:
Step4. Save the subform.
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 Design tab, Click
Step3. To position the combo box in desired place,
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,
Step5. Save the subform.
Step6. Switch to Datasheet view.