Forum Discussion

KevvieMetal's avatar
KevvieMetal
Copper Contributor
Mar 21, 2023

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

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: 

access

7 Replies

    • KevvieMetal's avatar
      KevvieMetal
      Copper Contributor
      Mar 22, 2023

      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!

  • loufab's avatar
    loufab
    Copper Contributor
    Mar 22, 2023

    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.








    • KevvieMetal's avatar
      KevvieMetal
      Copper Contributor
      Mar 22, 2023

      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.

    • KevvieMetal's avatar
      KevvieMetal
      Copper Contributor
      Mar 22, 2023
      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.