I have tried to see if I can make sense of this. What I am actually trying to
do is set up a database pulling together lots of information about employees
which will involve several many-to-many relationships. As an example,
employees and courses. I have 3 tables, simplified below:
employeeID
Employee
CourseID
Course
EmployeeID
CourseID
The latter being the link table.
I would like to set up 2 forms -
Employee form with a sub form to enter courses that employee attends
Course form with a sub form to enter the employees that did that course
Is that possible? How do you tie in the link table? I have already entered
the name of some course into the course table and some employees into the
employee table. How do they then appear on the link table?
The Northwind analogy would be: Employees = Orders; Courses = Products; your
link table = OrderDetails.
This is a classic many to many, and you're on the right track! What you need
to do is have a Form based on the Employee table (into which you can enter
employee personal details), with a Subform based on the link table. The
Master/Child Link Field of the subform would be the EmployeeID; on the subform
you would have a combo box based on Courses which would let you select which
course the employee is enrolling in. This combo could have code in its
NotInList event to open the Courses data entry form if you want to enrol the
employee in a course which has not yet been defined in the database.
The flipside would be a Form based on Courses, with a subform (again) based on
the link table. The master/link field would now be the CourseID, and you'ld
have a combo to select the employee. The combo would *store* the employeeID
but display the employee's name.