Form/Subform Linked Fields w/Join Table on 2 Fields (1 Viewer)

padlocked17

Registered User.
Local time
Today, 05:07
Joined
Aug 29, 2007
Messages
276
I have a form that summarizes tasks and contains a subform that displays relationships that are created between 2 Tasks via a Join Table. The Join table has a ParentTask and ChildTask field. I want to link the subform to the parent form via the Linked Fields functionality. My issue is that because the Tasks could be in 2 different fields on the Join table, I can only link 1 field and therefore can only show relationships that exist between the Parent form and 1 field on the Join table.

Any recommendations for how to display relationships via either field in the Join table on the subform?
 

Ranman256

Well-known member
Local time
Today, 06:07
Joined
Apr 9, 2015
Messages
4,337
there should be 2 subform fields: OwnerID, Task.
there should only be 1 task field.
the sub table is designed wrong if you have > 1 task field. There should be many records for the 1 task field.
 

cheekybuddha

AWF VIP
Local time
Today, 10:07
Joined
Jul 21, 2014
Messages
2,237
Any recommendations for how to display relationships via either field in the Join table on the subform?
The answer you probably don't want to hear is to correct your table design.

If you describe the tables invlovled, field names, datatypes and relationships we will be able to guide better.
 

padlocked17

Registered User.
Local time
Today, 05:07
Joined
Aug 29, 2007
Messages
276
Here is a simplified diagram of what I've got. Big picture is that I've got multiple Tasks in the tblTask that I'm building relationships to themselves in a many-to-many amongst themselves. Tasks could be parents to multiple other tasks and also children to multiple other tasks. The Join table tblTaskTask will capture the hierarchical and associations of other types as well between Tasks.

Happy to entertain other ideas, but I think this accurately captures the intent and is normalized?? Happy to be corrected!

Screen Shot 2022-05-03 at 10.10.49 AM.png
 

cheekybuddha

AWF VIP
Local time
Today, 10:07
Joined
Jul 21, 2014
Messages
2,237
OK, you didn't explain about the hierarchical relationship.

I'm not sure you actually need tblTaskTask - just add the ParentTaskID to the tblTask (and the AssociationTypeID). If it is a Parent task then its ParentTaskID will be null.

Then you just join to tblTask.ParentTaskID. You may need to join on an expression like Nz(ParentTaskID, TaskID)
 

padlocked17

Registered User.
Local time
Today, 05:07
Joined
Aug 29, 2007
Messages
276
Or can a task have more than one parent?
Yeah that's the issue. I've got a lot more at play that what I'm posting, but trying to just keep the relevant bits to the conversation. I've actually got a TaskTreeID and ParentID in tblTask as well because all of the Tasks fall underneath of a particular Task Tree in which they are only allowed 1 parent (but could have multiple children). Because we are looking at creating multi-dimensional relationships between TaskTrees, I need a very flexible Parent/Child (many-to-many) relationship ability on the Tasks. There is also a GroupID on the tblTaskTask table that shows ownership of a created association to a particular group and multiple groups are allowed to create the same relationships between tasks for their own use and to assess the importance of certain tasks because multiple groups are creating similar relationships.

It's really a n to n relationship that I'm trying to capture. I just want to design the ability for a task to be selected and then any tasks either as a Parent or a Child in the tblTaskTask table to be shown with the relevant arrangement of the relationship shown. I was going down the route of a Union query but it just didn't feel right. The sfrmAssociations is view only and doesn't allow creation/deletion/modification of any of the records displayed.
 
Last edited:

bastanu

AWF VIP
Local time
Today, 03:07
Joined
Apr 13, 2010
Messages
1,401
Based on your initial post only I would say the easiest is to not use the master\child linking for that form\subform. Instead add a calculated field to the subform's recordsource where you concatenate the two fields: Both_IDs:"|" & [Field1] & "|" & [Field2] & "|"

Now add another calculated field: InStr([Both_IDs],"|" & Forms!frmYourMainFormName!RecordID & "|") and in the criteria row put >0.

And finally in the current event of the main form issue a Me.sfrmYourSubform.Form.Requery

Not that pretty but should work....
Cheers,
 

padlocked17

Registered User.
Local time
Today, 05:07
Joined
Aug 29, 2007
Messages
276
Based on your initial post only I would say the easiest is to not use the master\child linking for that form\subform. Instead add a calculated field to the subform's recordsource where you concatenate the two fields: Both_IDs:"|" & [Field1] & "|" & [Field2] & "|"

Now add another calculated field: InStr([Both_IDs],"|" & Forms!frmYourMainFormName!RecordID & "|") and in the criteria row put >0.

And finally in the current event of the main form issue a Me.sfrmYourSubform.Form.Requery

Not that pretty but should work....
Cheers,
I'm following the logic and implementing now to test it out. Looks like when I add the second calculated field it's asking for the Parameter "Both_IDs". If I remove the criteria the Parameter pop-up goes away. Naming appears to not be an issue. Do I need a 2-step query to handle this?
 

bastanu

AWF VIP
Local time
Today, 03:07
Joined
Apr 13, 2010
Messages
1,401
You might need a second query but just use the expression in the second field and skip the Both_IDs:

InStr("|" & [Field1] & "|" & [Field2] & "|","|" & Forms!frmYourMainFormName!RecordID & "|")

Cheers,
 

cheekybuddha

AWF VIP
Local time
Today, 10:07
Joined
Jul 21, 2014
Messages
2,237
I think you will need to base your sfrmResults on a union query:
SQL:
SELECT t.TaskID, 'Main' AS [Type]
FROM tblTask t
WHERE t.TaskID = [Parent].[txtRecordID]
UNION
SELECT tt1.ParentTaskID, 'Parent'
FROM tblTaskTask tt1
WHERE tt1.ChildTaskID = [Parent].[txtRecordID]
UNION
SELECT tt2.ChildTaskID, 'Child'
FROM tblTaskTask tt2
WHERE tt2.ParentTaskID = [Parent].[txtRecordID]
;

Probably easier just tot set the RecordSource in code on AfterUpdate of txtRecordID
 

Users who are viewing this thread

Top Bottom