Cant reference the subform to repopulate the recordsource Runtime Error 2450 Cant find the main form

Question

I have a MainForm Groups_Members with 2 SubForms as DataSheets Groups and Members. I am trying to populate the Members subform depending on the selected row in the Groups subform.

I am in the Subform Groups and this code works for the the Form Members repopulating the recordsource according to the selected row (more accurately by clicking on the id field, which is not really what i want but that is a more cosmetical issue) :

Private Sub ID_Click()
    Dim SQLsource As String
    
    SQLsource = "SELECT [tPerson].[ID], [tPerson].[Anrede], [tPerson].[TitelVorn], [tPerson].[Vorname], [tPerson].[Nachname], [tPerson_Funktion].[Funktion], [tPerson_tInstitution].[Wochenstunden], [tPerson_tInstitution].[tInstitution_ID] " & _
                "FROM (tPerson INNER JOIN tPerson_tInstitution ON [tPerson].[ID] =[tPerson_tInstitution].[tPerson_ID]) INNER JOIN tPerson_Funktion ON [tPerson].[ID] =[tPerson_Funktion].[tPerson_ID] " & _
                "Where [tPerson_tInstitution.tInstitution_ID] = " & CurrentRecord & " ;"
    
    If CurrentProject.AllForms("Members").IsLoaded = True Then
        Forms("Members").RecordSource = SQLsource        
    End If
End Sub

However adapting it to work for the Subform "Members" within the MainForm "Groups_Members" hasn't been successful and i haven't been able to figure out why.

This

Forms!["Praxen_und_Mitarbeiter"]!["Mitarbeiter"].Form.RecordSource = SQLsource

Gives me an runtime error 2450 cant find Form Groups_Members although the form is right in front of me.

and this

Me.Parent!("Mitarbeiter").Forms.RecordSource = SQLsource

prompts a Type Mismatch Error

I am clearly missing something and would appreciate anyone pointing out my mistake.

Is the subform container named Mitarbeiter? If code is behind Group subform: Me.Parent.Mitarbeiter.Form.RecordSource. Review fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp — June7

Steve Piller Steve Piller · Accepted Answer · 2020-10-13T21:01:30

The workaround described in the link provided by June7 works: https://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

The Short Version: Create an invisible Control Element on the Main Form and set the Control Source Property to the value in Subform A. Then Link corresponding the Value in Subform B to this Control Element.

This provides the desired effect. The referencing oddities remain unresolved but thats for another time.

Thanks to everyone who invested their time and to June7 for providing a solution.

Cant reference the subform to repopulate the recordsource Runtime Error 2450 Cant find the main form

1 Answers