Is it possible to have 1:M:1 and 1:M:M forms

[KevvieMetal]

I'm hoping it's possible to create 1:M:1 and 1:M:M forms. Simple version is I have three tables, Students, HostFamilies, and Placements. While most students stay in the same placement over the course of a year, some do move and have several families (and/or schools). The pertinent fields are:

Students
Sdt_ID | Autoincrement (primary key)
Name | Short Text
<other fields>

HostFamilies
HF_ID | Autoincrement (primary key)
HF_Name | Short Text
<other fields>

Placements
Sdt_ID | Number (long integer, primary key field 1)
Pcmnt_Num | Number (primary key field 2)
Pcmnt_type | Short text (permanent, temporary, etc.)
HF_ID | Number (long integer)
School_ID | Number (long integer)

I would like to have a form for the Students table that has both a subform for Placements (it would be tabular) that I can use to select a particular placement number, which will in turn populate a second subform from the HostFamilies table with the HF data linked through the placement in question. Ideally, it will look something like this: 

[#2]

Hi,
If you don't create a "placement" you should use a list instead of a subform.
Otherwise :
In form create an independent textbox named HF_ID. No link field !
In subform placement on current_form event use this code :

if not isnull(me.HF_ID.value) then
    'copy placement number in new HF_ID textbox in form
    me.parent.HF_ID.value = me.HF_ID.value
endif

In form select container of subform hostfamily, in properties LinkMasterFields and LinkChildrenFields write 2 fields of the (new) relation :

LinkMasterFields : Sdt_ID;HF_ID
LinkChildrenFields : Sdt_ID:HF_ID

If you are refresh problem on host_family subform add this before the Endif instruction.

Me.Parent.Controls("sfHostFamily").Requery

sfHostFamily is the name of CONTAINER of subform and not name of subform.

[#3]

@loufab
Thanks. So it's not possible natively, but can be achieved with just a bit of code and proper form design. I'm new to Access so I'll need to get familiar with the coding. I appreciate your help.

[#4]

@KevvieMetal The sample database here demonstrates the use of a main form/sub form design to handle M:M relationships.

[#5]

@loufab 

I must be doing something wrong because I can't get it to work. While I know table relationships and such from database work long ago, I'm a total noob to Access. I'm sorry I'm not catching on too well.

[#6]

@George Hepworth 

Thanks. I'm pretty good with basic two-table relationships (1:1, 1:M, and M:M), but I'll definitely dig into that. What I'm really bumping up against is the more complicated relationships. It doesn't help that I don't usually get the chance to work on this until after 9pm -- I'm already exhausted when I start!

[#7]

For many-to-many relationships one intermediate table must be used.

Example :
Table X ----- Intermediate table X-Y ---- Table Y
element A----A...1-----element 1
element A---A...2-----element 2
element B----B...2-----element 2
element B----B...1-----element 1

[#8]

I get that. I'm no good after 7:00pm.