Link parent form to child form to "grandchild" form with SQL, not master/child fields

I have a parent form of movie volumes ("frmVolumes", home-recorded VCR movies), and one volume can have multiple programs (shown in "fsubPrograms", individual movies or TV shows).  Those link with no problem.  When the frmVolumes displays, the appropriate programs display in fsubPrograms, based on frmVolume's recordsource's primary key, which is a hidden field on frmVolumes.

However, I can't get another level of subform (fsubActors) to display appropriate actors based on the highlighted program's recordsource's primary key, which is a hidden field on fsubPrograms.  It looks like all actors are loading.

I think my problem lies in the order in which Access loads forms.  Rather than use form properties for link master and child fields, I am using SQL strings in the Form_Current event.  This is a new technique for me, but I have it working and understand it for one main form and one subform.  In my experience, the subform's recordsource is retrieved before the main form's recordsource.

Linking the subform to another yet a further subform seems to be the problem.  Another version of this database that does use form properties for linking does work properly, and I get the correct actors.  I have also tested the query to verify that it only brings back the actors I want.

Here are my queries, just for a validation.  Can someone explain what I need to do so that the three recordsources are loaded in the correct order?  Am I missing a Requery/Refresh/Repaint?

frmVolume/Form_Current event:
    strSQL = "SELECT * " & _
        "FROM [tblPrograms] " & _
        "WHERE [VolumeIdLink_FK] = " & Me.txtHidden_VolumeId & " " & _
        "ORDER BY [ProgramsVolumeName], [Sequence]"
    [Form_fsubPrograms].RecordSource = gstrSQL

Open in new window

fsubPrograms/Form_Current event:
    strSQL = "SELECT DISTINCTROW [tblActors].*, [tblPrograms].[ProgramID_PK], [tblPrograms].[Title] " & _
        "FROM [tblActors] RIGHT JOIN ([tblPrograms] RIGHT JOIN [trelProgramActorJoin] " & _
        "ON [tblPrograms].[ProgramID_PK] = [trelProgramActorJoin].[ProgramIdJoinLinkPAJ_FK]) ON [tblActors].[ActorID_PK] = [trelProgramActorJoin].[ActorIdJoinLinkPAJ_FK] " & _
        "WHERE [trelProgramActorJoin].[ProgramIdJoinLinkPAJ_FK] = " & Me.txtHidden_ProgramId & " " & _
        "ORDER BY [tblPrograms].[Title]"
    [Form_fsubActors].RecordSource = strSQL

Open in new window

By the way, table "trelProgramActorJoin" is a table that only holds keys to "tblPrograms" and "tblActors" and establishes the relationships between them.
Alan VargaAccess DeveloperAsked:
Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
Rather than use form properties for link master and child fields ...

But do so, and skip the custom filtering, and it will work as intended.

Alan VargaAccess DeveloperAuthor Commented:
Thank you, Gustav.  I now have a hybrid mixture; the parent-to-child linking is done via SQL, but the child-to-grandchild linking is done via master/child fields in form properties.

Just to confirm: I can only use SQL for parent-child linking, correct?

Gustav BrockConnect With a Mentor CIOCommented:
I can only use SQL for parent-child linking, correct?

Not sure what you mean. But the sources for the parent and the child must be related to each other with a one-to-many relationship.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Alan VargaAccess DeveloperAuthor Commented:
I tried a couple of different things after really thinking this through.  The relationship between the child subform (fsubPrograms) and the grandchild form (fsubArtists) is one-to-many, but the way I used form designer both of those appear as siblings on the parent form (frmVolumes).

I attempted making fsubArtists a true subform of fsubPrograms, but I ran out of stack space during the assignment of the recordsource for fsubActors using a SQL command in the Form_Current of fsubPrograms.

I will the master/child links in the form properties for frmVolumes/fsubActors as you suggested, and the three will work as I intended.  It was an interesting experiment, but Access isn't designed to work the way I thought it did.

Thank you for your insight on the one-to-many relationships.  I can now close this question.
Alan VargaAccess DeveloperAuthor Commented:
I appreciate the concise explanations provided during my investigation of this programming experiment; they were very helpful in deciding how to proceed.
Gustav BrockCIOCommented:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.