data entry form for many-to-many

[andy]

I think this is more of a forms question than design
question, so bear with me . . . .

I am designing a database primarily needed to manage our
relationships with community members. Since many contacts
relate to us in more than one capacity things can get
complicated.

I have a many-to-many relationship between a "Contacts"
table (PK=contact ID) and a table of contact types
(PK=contacttypeID). They are joined by a "relationships"
table with two fields (PK=contact ID + contacttypeID).

The problem is: how do I set up a primary data entry
form with a subform that allows me to mark multiple
contact types?

I cannot set it up properly to let me input new records
and select the various contact types for each.

Does this sound familiar? Please help.

[Sandra Daigle]

Hi Andy,

If your main form is Contacts then you just need a subform for Relationships
with ContactId as the LinkMasterFields and LinkChildFields set to the
ContactId field. Typically this type of subform would be a form in
Continuous view.

You could get a bit fancier and use a pair of MultiSelect Listboxes - for an
example of this you can download my SelectRecords sample on
[url]http://www.daiglenet.com/MSAccess.htm[/url]. It's just another way to deal with
data items that have a Many to Many type relationship.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

andy wrote:

> I think this is more of a forms question than design
> question, so bear with me . . . .
>
> I am designing a database primarily needed to manage our
> relationships with community members. Since many contacts
> relate to us in more than one capacity things can get
> complicated.
>
> I have a many-to-many relationship between a "Contacts"
> table (PK=contact ID) and a table of contact types
> (PK=contacttypeID). They are joined by a "relationships"
> table with two fields (PK=contact ID + contacttypeID).
>
> The problem is: how do I set up a primary data entry
> form with a subform that allows me to mark multiple
> contact types?
>
> I cannot set it up properly to let me input new records
> and select the various contact types for each.
>
> Does this sound familiar? Please help.

[stevephillips79]

hi,
You can take advantage of many-to-many relationships in your Access databases, using a form to join many to many fields, so thanks if you are up for checking it out.