Refering to a subform with a variable

[Rob G]

Hello,

I would like to refer to a subform using a variable, but I can't seem to get
the syntax correct.

For example, I want my code to look through each control on a subform, I
have the following code:


Function PrintControlNames(MainFormVariable As String, SubFormStringVariable
As String)

For Each ctr In Forms(MainFormVariable).(SubFormStringVariable).Controls
Debug.Print ctr.Name
Next

End Function

The code is fine with the MainFormVariable, but unless I explicitly put the
name of the SubForm (where the SubFormStringVariable is), I get an error
that it can't find the subform.

Any ideas?

Thanks.

-Rob

 

[Allen Browne]

You need to distinguish between the subform control and the form in the
subform control:

Function PrintControlNames(MainFormVariable As String, _
SubFormStringVariable As String)
Dim ctr As Control

For Each ctr In
Forms(MainFormVariable).(SubFormStringVariable).Form.Controls
Debug.Print ctr.Name
Next
End Function

If that still fails, then the Name of the subform control is incorrect. The
control may have a name that is different than the name of the form it
contains (its SourceObject).


You could write a recursive function that calls itself to list the items in
its subforms:

Function PrintControlNames(frm As Form, Optional ParentFormName As String)
Dim ctl As Control
Dim strThisForm As String

If Len(ParentFormName) = 0 Then
strThisForm = frm.Name
Else
strThisForm = ParentFormName & "." & frm.Name & ".Form"
End If

For Each ctl In frm.Controls
Debug.Print strThisForm & "." & ctl.Name
If ctl.ControlType = acSubform Then
Call PrintControlNames(ctl.Form, strThisForm)
End If
Next
End Function

Call it like this:
? PrintControlNames(Forms!Orders)

 

[Rob G]

Allen,

Thanks so much! I got it to work.

But I needed to add the Form reference before the subForm variable in the
For...Each . Instead of this:

For Each ctr In Forms(strCurrentFormName).(strSubFormName).Form.Controls

I did this:
For Each ctr In Forms(strCurrentFormName).Form(strSubFormName).Form.Controls


I don't know if it is redundant or not, but it now works!

Thanks again.

-Rob