Many-to-Many Subform?

[Richie2837]

We store records of our contacts and generate reports based on what contact type they are assigned to from a list box (they could be client, supplier, volunteer, etc.)

However, it's become apparent that our contacts should have multiple types assigned to their records (a client could also be a volunteer, a supplier could also be a service user, and so on).

Clearly the current method of selecting the Contact Type from a Listbox does not allow for multiple selections, so at the moment all our contacts can only have one type applied to them.

How do I change this so we can select multiple types for each Contact on our database? I'm guessing its something to do with a many-to-many relationship, but I just can't get my head around the specifics of what I need to do to get it to work.

Any help MUCH appreciated!

 

[MarkK]

Consider this...

tContact
ContactID (PK)
First
Last

tContactTypeDetail
ID (PK)
ContactID (FK)
ContactTypeID (FK)

tContactType
ContactTypeID (PK)
Description

It's like a one-to-many-to-one.

 

[Richie2837]

I get that PK is Primary Key, but FK?

 

[boblarson]

I get that PK is Primary Key, but FK?

FK = Foreign Key (a value which is a primary key in one table and entered in another table to link the two records together)

 

[Richie2837]

Okay, this is the Junction Table yes? I've created this now, what's the next stage?

 

[MarkK]

Make a datasheet subform out of the tContactTypeDetail table.
Make each FK a ComboBox that draws meaningful data from its related table. An example of a combo RowSource on the tContact side...

SELECT ContactID, First & " " & Last as Fullname FROM tContact ORDER BY Last;

Now, host that subform on your tContact AND your tContactType main forms.
You can also teach the subform to check the name of the parent form and hide the redundant combo.

'show both columns
me.ContactID.columnHidden = false
me.ContactTypeID.columnHidden = false
Select Case Me.Parent.Name
  Case "YourContactFormName"
    'don't show the contact on the contact form
    me.ContactID.columnHidden = true
  case "YourContactTypeFormName"
    'don't show the type on the type form
    me.contactTypeID.columnHidden = true
  case else
    'leave both columns visible
End Select

This way each contact shows a list of types, and each type shows a list of contacts, and you only have one additional object cluttering up your database.

 

[Ecron]

Thanks guys! I was having a similar problem and this helped me with it!