How to setup relation between seven subforms and mainform (1 Viewer)

ThomasD

New member
Local time
Today, 06:30
Joined
May 10, 2022
Messages
2
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

You've got your good things, and you've got mine.
Local time
Today, 01:30
Joined
May 21, 2018
Messages
8,463
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
 

Attachments

  • LinkedSubforms.accdb
    1.1 MB · Views: 176
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:30
Joined
May 21, 2018
Messages
8,463
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

New member
Local time
Today, 06:30
Joined
May 10, 2022
Messages
2
wow OO ... uhm ... i will try and let u know. thanks alot!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:30
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • Nwind 2007 Clean.accdb
    2.7 MB · Views: 182

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:30
Joined
Jan 20, 2009
Messages
12,849
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

AWF VIP
Local time
Today, 00:30
Joined
Apr 3, 2020
Messages
1,003
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
 

Users who are viewing this thread

Top Bottom