LinkMasterFields property in subform of subform

[jgoodnight]

Hello,

I've read through many forum posts on this issue today but can't seem to find a solution that fits my particular case.

I have three main tables:
- tblIssues (PK: IssueID)
- tblRecommendations (PK: RecommendationID)
- tblActions (PK: ActionID)

There is a many-to-many relationship between issues and recommendations and between recommendations and actions. I have implemented join tables between them to account for this relationship.

I have a main form, frmIssues, that has a subform, sfrmRecommendations (based on qryIssueRecommendation). I'm able to use these forms to view AND enter issues and recommendations... the linkMasterFields and linkChildFields (both set to "IssueID") properties are working correctly.

I'm trying to create a subform, sfrmActions (based on qryRecommendationAction), of sfrmRecommendations. To be clear: sfrmActions is a subform of sfrmRecommendations, which is a sfrm of frmIssues. The form accurately displays the actions for a given recommendation, but when I try to enter a new action I get the following error: The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object Recommendations."

I have tried using various versions of RecommendationID in the LinkMasterField property, including [RecommendationID], Recommendations.RecommendationID, and [Recommendations.RecommendationID].

I think the error has something to do with RecommendationID not existing on the main form (frmIssues). I've seen posts that suggest putting a hidden textbox on the main form with the control I need, but so far I haven't found a way to add RecommendationID to frmIssues.

Any suggestions?

[Stewart Ross]

Hi. Could you post the SQL for the queries involved? I am not clear from what you have said about your linking tables, as you have not posted any detail on these.

If there are two many-many relationships, each decomposed into two 1-many relationships by use of a link table, then the link tables will have the fields from both tables in the relationship carried forward. That is, if T1 relates many to many to T2 then link table T3 will have a composite key, T1PK and T2PK. Similarly for your other table.

If the relationships are defined in this way I would be surprised if you had single-key parent-child links from your main forms to your subforms throughout the multiple-level hierarchical relationship you describe; you would not be carrying forward the linking key from at least one table if not more. Can't tell without more detail, though.

I would also point out that users will quickly become lost if you nest subforms several levels deep as you seem to be suggesting. My preference would be to open a separate main-form/sub-form dialogue filtered from your other form to avoid too many levels of hierarchy for users to negotiate - it's going to get awfully crowded on screen trying to fit in a main form for data entry, a sub form, and a sub-sub form etc.

-Stewart

[jgoodnight]

Hi Stewart,

The linking tables contain three fields: An autonumber PK and the 2 PKs of the tables it is joining.

The SQL for the IssueRecommendation query is:

SELECT Recommendations.RecommendationID, Recommendations.Title, Recommendations.Description, Recommendations.Source, Recommendations.Date, Recommendations.Status, Recommendations.Background, Recommendations.POC, [IssueRecommendation Join].IssueID, [IssueRecommendation Join].RecommendationID, Issues.Title, Issues.Description, Issues.POC
FROM Recommendations INNER JOIN (Issues INNER JOIN [IssueRecommendation Join] ON Issues.IssueID = [IssueRecommendation Join].IssueID) ON Recommendations.RecommendationID = [IssueRecommendation Join].RecommendationID;

The SQL for the RecommendationAction query is:

SELECT [RecommendationAction Join].RecommendationID, Actions.Title, Actions.Description, Actions.Background, Actions.Agency, Actions.POC, Actions.[Begin Date], Actions.[End Date]
FROM Recommendations INNER JOIN (Actions INNER JOIN [RecommendationAction Join] ON Actions.ActionID = [RecommendationAction Join].ActionID) ON Recommendations.RecommendationID = [RecommendationAction Join].RecommendationID;

You are correct in that this is starting to look confusing... I've been trying to combat that with label formatting but am open to other design ideas. I'll read up on filtering.

[Stewart Ross]

Hmm. I'm sorry to say that I there is conflicting information in what you have mentioned so far that makes it very difficult for me to advise you further.

If, as you said in post #1, the relationship between Issue and Recommendation is m-m, and between Recommendation and Action is m-m, then the link tables are essential to decompose your two many-many relationships into workable 1-m's. If you want to implement a form for users to add recommendations and issues the linking parent-child fields cannot be just IssueID if the relationship between Issue and Recommendation really is m-m. I am using the word cannot because otherwise you would violate referential integrity by doing so, and I am sure that Access would object when you try to enforce the relationship (assuming as good practice you do use the relational facilities built in).

By definition, if the relationship between tables T1 and T2 is m-m then there is no guaranteed single unique row in T1 for each T2 - and equally, there is no guranteed unique single row in T2 for each T1. Relational databases cannot implement such an m-m relationship directly - that is what the decomposition into two 1-m's resolves, as you have done. However, in your main-form/sub-form you are implementing a link to T2 through the PK of T1 - which does not implement the decomposed relationship with the link tables you have defined. It is implementing a 1-m relationship instead - which means that you would not be able to use your form to show all T2 rows belonging to multiple T1s as it stands. Main/Subform combinations are intended for 1-m relationships - and from what you have said this is not such a relationship.

In post #3 you mention the two queries which join the issues and actions to their respective link tables. Although you are trying to use these joining queries, the sequence of events does not match: the linking table can only link an existing record on each side of the relationship (an existing action to an existing recommendation). I am not surprised as it stands you cannot add an action record using the linking query - so far as I can see you cannot add such a record using that query, although you will be able to show records that already exist.

Too many conflicting things going on here; but of one thing I am certain - either the relationship between your Issues and Recommendations and Recommendations and Actions tables are incorrectly defined as m-m, or you have not implemented the relationship as anything other than 1-m in each case. The relationships cannot simultaneously be 1-m the way you are carring the PK forward in your subforms and m-m decomposed using linking tables.

-Stewart

[jgoodnight]

Stewart,

Thanks for taking so much time to consider my problem. Your insight led me to the realization that the implementation I thought was working didn't actually get the job done.

The overall goal is to allow the user to enter an issue, select recommendations to solve the issue (or enter a new recommendation if need be), and select actions supporting that recommendation (or enter a new action if need be).

I've refined the tables/relationships to the following:

tblIssues
PK IssueID
Title
Desc
etc.

tblRecommendations
PK RecommendationID
Title
Desc.
etc.

tblActions
PK ActionID
Title
Desc.
etc.

tblIssueRecommendation Join
PK IssueID, RecommendationID

tblRecommendationAction Join
PK RecommendationID, ActionID

I'll never enter a recommendation without having an issue to relate it to (or an action without having a recommendation to relate it to), so I thought it made sense for my form to start with Issues and drill down from there. Is this a possibility? Are there other possible ways to manage this data?

I'm new to this forum and to Access programming (as I'm sure is apparent), so if this is not the right place for this question please let me know.

Thanks again for your time,

Jonathan

[Stewart Ross]

Hi Jonathan. All database design involves judgment about the relations between tables; practical implementations often involve conscious compromises, such as (for example) recognising that although a relationship may be m-m in the worst case (a recommendation may can cover many issues, and each issue can have many recommendations) in practice single issues may most often be involved, so a 1-m approach would be justified - and much easier to implement. I mention this because your revised design still implements the relations involved as m-m, and implementing this as two 1-m's is likely to be a challenge for you.

To implement your current m-m decomposed to two 1-m relations you would most likely need to have an issues entry form and a recommendations entry form, then a main/subform combination to select which issue links to which recommendation (and vice versa). Although not difficult to implement, this does not 'feel' right to me (thinking about what a user would intuitively do). Perhaps a 1-m approach is a better compromise - but with the limitation that recommendations would never apply to any more than one specific issue. Such an approach would allow a more intuitive use of a main/subform combination to enter issues and recommendations, then another to enter actions associated with those recommendations.

Please feel free to bounce your thoughts about this here; design is more of an art than a science when it comes to decisions about relationships between tables at this level.

-Stewart

[NeoPa]

Just to clarify Jonathan, is the table [tblRecommendations] referred to in your explanatory posts the same object as the [Recommendations] as used in the SQL posted, or is there something I'm missing here (I hope not)?

Other than this strange anomaly, it seems that you're getting to grips with some of these concepts quite well for a beginner.

Welcome to Bytes :)

[jgoodnight]

Hi Neo and Stewart,

Neo - Thanks for the kind words. Yes, I was using the "tbl" in front of all the table names as I thought that was the generally accepted way of referring to tables in forums like this. The actual table names do not have the "tbl" included.

Stewart - I started over using a 1-m relationship between Issues and Recommendation and between Recommendations and Actions. Everything is working out well! Thanks again for your help.