Junction table ans subforms

[webgirly]

All I ever seem to do is sit about trying to figure this out and then give up and ask for help

I have three tables.

One for contacts
One for groups (groups like people attending meeting a, b c)
and a junction table as the top two create a many to many relationship.. one person can be part of multiple groups, and a group can have multiple members.

In the contacts table my primary key is an auto number, and is contactsID
I also have some contact details, and a groupID field

In the group table my primary key is GroupID and is an auto number,
it also have group name, and description

in the junction table I have a primary key, then GroupID, and ContactsID which are also set at primary keys and are set with the same values as the same named primary keys in the relavant tables (I beleive this defines them as foreign keys... i hope so anyway!)

I have the relationships set up as a one to may relationship from contacts to junction and groups to junction, contacts linking from ContactID to ContactID in each table, likewise with the Group table.

However, when I try to put everything into a main form for contacts,with a subform for groups, all I am getting is the autonumber... which isn't much good for my end user..... How do I solve this?

Thanks anyone that can help.......

 

[ScottGem]

You have a many to many relation. Therefore GroupID should NOT be in your Contacts table. In your Junction table you should have an autonumber as your PK and the GroupID and ContactID fields as FKs (not PKs).

Your subfrom should be bound to the junction table linked on ContactID. So it should have a single combobox to select the GroupID. The PK and ContactID controls should be hidden.

 

[webgirly]

Ah, okay, I think I've followed that alright.

How do I set a field as an FK instead of a PK?

 

[ScottGem]

An FK is not "set". Its simply a field added to a related table that holds the PK value of the related record. Generally, I name my FKs the same name as the original PK. And I use tablenameID as my naming convention for PKs.