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?
strSQL = "SELECT * " & _
"FROM [tblPrograms] " & _
"WHERE [VolumeIdLink_FK] = " & Me.txtHidden_VolumeId & " " & _
"ORDER BY [ProgramsVolumeName], [Sequence]"
[Form_fsubPrograms].RecordSource = gstrSQL
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
By the way, table "trelProgramActorJoin" is a table that only holds keys to "tblPrograms" and "tblActors" and establishes the relationships between them.