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: 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 Add Tables pane,
Double-Click tblSections, tblSectionsInstructors, and tblFaculty
Step3. Add the following fields to our query in the order they appear in the table:
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 entry 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, if necessary,
Click the Datasheet radio button
Step8. To name the forms, starting in the Form: field,
Double-Click the default value, type: frmAssignFaculty Tab key fsubAssignFaculty
Step10. 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.
Step11. To change the title, at the top of the form,
Triple-Click frmAssignFaculty, type: Assign Faculty to Sections Enter key
Step12. To delete the subform's label,
Click fsubAssignFaculty, press: Delete key
Step13. To widen the subform,
Click the subform, Press & Drag the left and right borders until the subform is as wide as you would like
Step14. Save the form and switch back to Form view.
Step15. 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
Step16. Repeat step 15 as many times as you would like for as many courses as you would like.
Step17. Save and close frmAssignFaculty.