How to easily add record to many-to-many tables

[Applebaum]

Hello,

I'm trying to create an easy datasheet form for data entry based on three
tables. The three tables are actually two in a many-to-many relationship,
with the third between them:

tblContacts
-----------------------
ContactID (pk)
Name

tblCompanies
----------------
CompanyID (pk)
Company

tblAffiliations
---------------
AffiliationID (pk)
ContactID
CompanyID

We need this to be a many-to-many relationship so we can track who worked
where and when.

I want an easy datasheet form for my users to fill in Name and Company.
After the record is entered, I want the following to happen:

1) New record added to tblContacts.
2) New record added to tblCompanies.
3) New record added to tblAffiliations, using the appropriate ContactID and
CompanyID.

I'll worry about preventing duplicates later. Can this be done?

Many thanks in advance!!

Matthew

 

[Applebaum]

Sorry,

I figured it out, with the humbling help of the Form Wizard. I'm trying to
do the next step, which introduces a table for Courses, another table for
Course Attendances, and the ability to have a single form to add a person,
their company, and select which course they took.

Thanks,

Matthew

 

[John Vinson]

I figured it out, with the humbling help of the Form Wizard. I'm trying to
do the next step, which introduces a table for Courses, another table for
Course Attendances, and the ability to have a single form to add a person,
their company, and select which course they took.

Thanks,

The simplest way to do this is to pick the "one" side which will need
most frequent updating and use it as the Master Form; use the
"junction" table as the Subform, with a Combo Box to select the value
from the other table. in this case I'd suggest a form for the Person
(since you'll presumably be adding many more people than courses),
with a subform for the course enrollment table.

In the Not In List event of the combo box, you can pop up a separate
form for the Courses (or whatever the other "one" side table is). See
the sample code at http://www.mvps.org/access for "not in list".

 

[Applebaum]

The sample code at http://www.mvps.org/access for "not in list" was exactly
what I needed!

Thank you!

Matthew