How to setup relation between seven subforms and mainform

[ThomasD]

Hi, i have a main-form and 6 subforms. Some are should be 1:1 and some 1:m

Mainforn is the contractual partner with a IdNumber
This contractual partner can have multiple contracts
Each contract have just one location
Each location have:

• just one maintenance cycle
• just one maintenance detail-description
• multiple contact persons
• multiple residental units
  • each residental unit have multiple mantenance protocolls

so ... i build eight tables & forms
- frmContractualPartner
- frmContract
- frmLocation
- frmResidentalUnit
- frmMantenanceDetails
- frmMaintenanceCycle
- frmContactPerson
- frmProtocoll

in designView i put all forms/subform together but only the Main and the only the 1st subForm are working. Is there any trick that they work together based on theyr relations?

regards thomas

 

[MajP]

The way I do this is to have multiple hidden text boxes. This is the easiest and requires little code.
First one is txtContractLocation

Then in frmContract in the on current event you set
txtContactLocation = me.contractID (where contractid is the PK of the Contact)

In frmLocation you link the subform control
LinkMasterFields: [txtContractLocation]
LinkChildFields: [ContractID] where contract id is the foreign key

now add txtLinkLocation to the main form and hidden

in the location subform on current event you set
txtLinkLocation = me.locationID

Now link the remaining subform controls to the txtLinkLocation

 

[MajP]

S

FYI. You can also link subform to subform. I have to play with the nomenclature to get the correct format for the naming.
I think it is simply NameofSubformControl.form!nameOfField

Try this nomenclature. To reference frmContract
Link Master Fields: [frmContract].form![ContractID]

 

[ThomasD]

wow OO ... uhm ... i will try and let u know. thanks alot!

 

[MajP]

It is weird but you rarely see people link subforms using the subform reference, but suprisingly the first time I saw it done was in a very old Northwind template database. The nomenclature is as I suggested. Look at the form Customer Orders and look at the Master, Child links in the second subform control.

 

[Galaxiom]

FYI. You can also link subform to subform. I have to play with the nomenclature to get the correct format for the naming.
I think it is simply NameofSubformControl.form!nameOfField

A child subform doesn't even have to be on the same main form or even be a subform per se.. It can be on another form entirely but the child form needs to be requeried using the Current event of the master form to stay in synch. It is an effective way to connect a separate editing form to the current record on a form.

 

[strive4peace]

hi @ThomasD

>How to setup relation between seven subforms and mainform

before forms are made, real relationships should be defined between tables -- and then when subforms are added LinkMasterField and LinkChildFields properties can be automaticallu filled by Access.

Here's a short video youcan watch about that

Enforce Referential Integrity (RI) on Access Relationships (3:11)

If you didn't make relationships to begin with but now have, you can fill the linking properties for each subform control yourself