help with code to add record to two tables related to current table

[Matthew]

Hello,



I've got a database with contacts (people) and organizations, joined in a
many-to-many relationship by way of a table for affiliations. I don't have
an easy way of adding a new contact - I've got a couple of hoops the user
has to go through to make sure that the organization is already there, if
it's not there they have to add it, then they make sure the contact is not
already there, then they add the contact, then they have to affiliate the
contact with the organization.



This is workable (barely) for general office use, but for some users I want
to streamline it. I want to emulate what the user's experience was when
they kept this data in an Excel spreadsheet. They fill in Name and
Organization as they tab through those columns. Can I create a form in
datasheet mode that will do this? Could I use a combo box, and some code on
the Not In List event? I can imagine how that would look, but how would I
also add the record to tblAffiliations at the same time?



Here's the model:



tblOrgs

*OrgID

OrgName



tblContacts

*ContactID

Name



tblAffiliations

AffID

ContactID

OrgID



Many thanks in advance!

Matthew

 

[GVaught]

Add OrgID to the Contacts table. This way you can associate a contact with
the organization via a main form/subform. The main form would be
Organizations and the subform would be Contacts. The OrdID in Contacts would
relate a contact with the organization.

 

[Applebaum]

We decided to have a many-to-many relationship between Orgs and Contacts so
that we could track a person's movement from one organization to another.
If there's an OrgID field in tblContacts, then a person could only be
affiliated with a single organization at a time.

Thanks,

Matthew