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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 BrockCIOCommented:
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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.