Referencing a subreport's 'container' 1

[VickyC]

greetings - How can a subreport reference the main report's control that contains it? This would be easy if there were only one subreport, but my main report contains 4 identical subreports, and each subreport's Recordsource contains a query that must 'know' the name of its 'container'. Hope this makes sense.

thanks in advance for any help
Vicky

 

[dhookom]

You can reference a subreport's parent by using its Parent property.

I'm not sure how/why this would be important to a subreport's recordset since attempting to change it based on something from its parent will result in problems.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

As Duane pointed out this can be done in a form but becomes a little problematic when using a report.

Here is the code in a subform for it to determine which subform control it is in and then do something based on that subform control.

The trick is that Me refers not to the "form" as people often say. It refers to a specific instantiation of the form object.

Private Sub Form_Load()
  On Error GoTo errlbl
  Dim ctl As Access.Control
  Dim frm As Access.Form
  For Each ctl In Me.Parent.Controls
    If ctl.ControlType = acSubform Then
      If ctl.Name = "subFrmNames1" Then
        Set frm = ctl.Form
        If frm Is Me Then
           Me.RecordSource = "qryName1"
           Exit Sub
        End If
      ElseIf ctl.Name = "subfrmNames2" Then
        Set frm = ctl.Form
        If frm Is Me Then
          Me.RecordSource = "qryName2"
          Exit Sub
        End If
      End If
    End If
  Next ctl
  Exit Sub
errlbl:
  MsgBox Err.Number & " " & Err.Description
End Sub

However, I doubt you will have much success going this route with reports. There probably is a much cleaner way to do what you want.

 

[MajP]

So a general function to return a subform instance container

Public Function getSubFormControl(subFrm As Access.Form) As Access.Control
  Dim ctl As Access.Control
  For Each ctl In subFrm.Parent.Controls
    If ctl.ControlType = acSubform Then
      If ctl.Form Is subFrm Then
        Set getSubFormControl = ctl
        Exit Function
      End If
    End If
 Next ctl
End Function

called from the subform, like:

msgbox "My Container = " & getSubFormControl(me).name

 

[VickyC]

Thank you to Duane and MajP for responding. The Parent property just gives me the subreport's parent report. I need the name of the control on the parent report that contains the subreport. Vicky

 

[dhookom]

It makes my brain hurt trying to think of:
1) how to get this property
2) why you need to determine this property
3) what you might intend to do with the property that pertains to the record source of the subreport

Throw me a couple aspirin ;-)

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
With a report the utility becomes is probably little, but I use this concept occasionally in a form. I may instantiate the same subform several times on a form, with each subform instance having a different recordsource. However, the events may act differently depending on which instantiation of the subform it is. So on a given event I may have something like

select case getSubFormControl(me).name
case "name1"
dosomething
case "name2"
dosomethingElse
case "name3"
doSomethingElse2
end select

The only other way to do that is to make a copy of the form to use as the subform. I do not like to do that because of version control, unless the subform is simple.

However, academically this is a really good lesson. It provides an understanding of multiple instances of a class, and demonstrates how to get a specific instance.

 

[MajP]

http://www.4shared.com/document/tlINth2U/GetSubFrmControl.html

Here is a simple demo that shows the same subform applying different filters and doing different things for the same event, based on its instantiation.