MS-Access: How to refer to subform control in SQL

Forms![main form name]![subform control name].Form![control name]


To refer to a control on a subform, use the following syntax: 

   Forms![main form name]![subform control name].Form![control name]
    

To refer to a control on a subreport, use the following syntax: 

   Reports![main report name]![subreport control name].Report![control
   name]
    

NOTE: Only subforms are discussed in the rest of this article, but all the information applies to both subforms and subreports. 

It is important to note that you cannot refer to controls on a subform with the following syntax: 

   Forms![subform name]![control name]
    

Private Sub Command3_Click()
    On Error GoTo err_Command3_Click

    Dim intHRGroupID As Integer
    Dim sql As String
    
        Debug.Print "############  begin   ##################'"
    
    Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].SetFocus
    HRGroupID = Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form![HR Manager Group ID].Text
    
    sql = "UPDATE [HR_TLV_HR_Staff_DB] z INNER JOIN ([HR Group to HR Mgr Relationships] x INNER JOIN [HR Manager Group] y ON x.[HR Manager Group ID] = y.[HR Manager Group Code] " & vbCrLf & _
          "    ) ON z.[ID] = x.[HR Manager Emplid] " & vbCrLf & _
          "SET x.[Included in Group] = 1 " & vbCrLf & _
          "WHERE y.[HR Manager Group Code] = " & HRGroupID & vbCrLf & _
          "  AND " & Forms![Update HR to HR Groups]![HR Mgr to Group Update subform].Form.Filter & " AND 1 = 1"
    
    Debug.Print sql
        
    sql = Replace(sql, "[HR Mgr to Group Update]", "z")
    
    Debug.Print "############  revised   ##################'" & vbCrLf
    Debug.Print sql
    
    DoCmd.RunSQL sql

exit_Command3_Click:
    Exit Sub
err_Command3_Click:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_Command3_Click
End Sub