Title: Entering Data in One of the Independent Entity Tables and the Associative Entity Table of a M:M Rela
1Entering Data in One of the Independent Entity
Tables and the Associative Entity Table of a MM
Relationship
- If you came to this presentation via a web
browser, - right-click and choose Full Screen before
proceeding. - Click mouse or press space bar to continue.
- This presentation was prepared by Professor Steve
Ross, with the advice of other MIS Faculty, for
use in MIS Classes at Western Washington
University. Please contact Dr. Ross for
permission to use in other settings..
2The Database
3Access Relationships
4The Challenge
- Create a form for the entry of building data,
including worker assignments and ManagerID - Information about workers must be entered prior
to use of this form
5Creating the Form, Step 1
- Specify the tables or views that the form affects
- tblBuilding, tblAssignment
- To enter data into two tables that are in a 1M
relationship, we must create a form for the 1
table and a subform for the M table
6Creating the Form, Step 1a
- Specify the 1 table or view that the form
affects - tblBuilding
- For each field in the 1 table or view, indicate
whether it is editable and constrained - BuildingID autonumber, visible but not editable
- Address editable
- City editable
- State editable
- Zip editable
- Description editable
- Status editable
- ManagerID editable, FK to tblWorker
7Creating the Form, Step 1b
- Specify the table or view that the M subform
affects - tblAssignment
- For each field in the M table or view, indicate
whether it is editable and constrained - WorkerID editable, FK to tblWorker
- BuildingID FK to tblBuilding, inserted
automatically - StartDate editable
8Designing for Usability Foreign Keys in Subforms
- Users shouldnt have to know or remember foreign
key values - Instead, present them a set of choices ordered in
a meaningful manner - If there are only a few choices and the list
rarely changes, radio buttons may be used - If there are several choices and/or the list
changes or is not known in advance, use a combo
box based on a query - A foreign key that relates the subform table to
the parent form table will be entered
automatically by Access
9Creating the Form, Step 2
- Create a draft view of the form, giving
approximate placement and type of controls for
each data item
I usually do this by hand, not in a graphic
program such as this
Standard Form Heading
BuildingID (read only, no label) Address City
State
Zip Description Status Manager
Assignments Person
Start Date
Address
StartDate
City
StartDate
State
Zip
StartDate
Description
Status
These will be text boxes bound to the relevant
fields
These will be combo boxes, bound to the relevant
fields but displaying data from the related tables
10Creating the Form, Step 3a
- Use the application generator to create version 1
of the form. This one will probably have text
boxes for the foreign keys.
11Creating the Form, Step 3b
- Use the application generator to add a subform.
This will probably have text boxes for the
foreign keys and be in datasheet view.
12Creating the Form, Step 4a
- Convert the foreign key controls to combo
boxes - The combo box row source is either a named view
(e.g., vueRankID) or a SQL statement (e.g.,
SELECT RankID,Rank FROM tblRank)
Technique will be demonstrated in class.
13Creating the Form, Step 4b
- The Subform Wizard will automatically set the
link fields between the two forms. - If there is a problem,
- Open the form in design view
- Right-click on the subform and open its property
sheet
Technique will be demonstrated in class.
14Creating the Form, Step 4c
- Open the subform in design view
- Convert the foreign key controls to combo
boxes - Change the default view to Continuous Forms
- Set Dividing Lines and Record Selectors to
yes
Technique will be demonstrated in class.
15Creating the Form, Step 5
- Apply check constraints and other properties to
controls. - Once controls are set, apply other design
standards.
The form illustrated here does not necessarily
meet all MIS 421 design standards.