Forms provide a flexible way to view, add, edit, and delete data and are much more efficient than using tables or queries to do so. We will create a form that will allow us to assign faculty members to sections.
In Access 2016: The Basics, we saw how easy it is to create a relational form by first selecting the primary table. This time, we will first create a query with all the data we need to make our form work, and then create a form based off of that query.
Building a Query
In order to assign instructors to courses, we will need values from several different tables. Let's begin by creating a new query and pulling in date values from multiple tables.
Step1. To begin building a query, on the Ribbon,
Click the Create tab, Click
- The course code, to easily identify the correct course.
- The course title and section number, to easily identify the correct section.
- The semester and year, to be sure we have the correct course/section combination.
- The faculty roles and their names, including their ID numbers for resolving ambiguity.
Step2. To add all of our tables, in the Show Table dialog box:
Double-Click tblSections, tblSectionsInstructors, and tblFaculty, Click
Step3. Add the following fields to our query in the order they appear in the table:
|Source Table||Field Name|
Step4. Save the query as "qryAssigningFaculty."
Step5. Switch to Datasheet view to see the results of the query.
Step6. Close qryAssigningFaculty.
Creating a Form
Today, let's use the Form Wizard to build our form. We're using the wizard so we can modify some of the properties of the form before it's created.
Step1. To select the query as our data source, in the Navigation pane,
Step2. To start the form wizard, on the Ribbon,
Click the Create tab, Click
Step3. To add all of the fields from the query to the form,
Step4. To explore the other view options,
Clickeach table in the "How do you want to view your data?" list box
Step5. To select by tblSections, in the list box,
Click tblSections, Click
Step6. To select datasheet and continue,
Click the Datasheet radio button, Click
Step7. To name the forms, starting in the Form: field,
Double-Click the default value,
type: frmAssignFaculty, press: Tab key,
type: fsubAssignFaculty, Click
Step8. To switch to Layout view, on the Ribbon,
NOTE: If the field list is obscuring your view, you can close it using the X in the upper-right corner.
Step9. To change the title, at the top of the form,
type: Assign Faculty to Sections
Step10. To delete the subform's label,
Click fsubAssignFaculty, press: Delete key
Step11. To widen the subform,
Click the subform, Press & Drag the left and right borders until the subform is as wide as you would like
Step12. Save the form and switch back to Form view.
Step13. To assign an additional faculty member to section 1011 of BIOL-L101 in the spring of 2018,
Click the Instructor ID field for the empty record, Click, Clickany faculty member, Click the Role field, Click, Clickany role
Step14. Repeat step 13 as much as you would like for as many courses as you would like.
Step15. Save and close frmAssignFaculty.