Trouble referencing subform from Screen.ActiveForm

I'm having some trouble refering to a sub form in VB for my Access database.  I have a loop that cycles through all the controls on a form using (roughly):

Dim MyForm AS Form
Dim C AS Control
Dim MySubForm AS Form
Dim subC AS Control
Set MyForm = Screen.ActiveForm

For Each C In MyForm.Controls
  Case acTextbox, acCombobox, acListbox, acOptiongroup
     do some stuff
  Case acSubform
  Set MySubForm = Screen.ActiveForm!C     {this is where I have the problem}
    For Each subC In MySubForm.Controls
      Do some stuff
Etc.

Apparently "Screen.ActiveForm!C" doesn't work.  How should I reference the subform in this situation?

Bob
bastille2Asked:
Who is Participating?
 
pique_techConnect With a Mentor Commented:
Hm....  I tried this:

    Dim c As Control
    Dim sc As Control
    Dim f As Form
    Dim sf As Form
   
    Set f = Screen.ActiveForm
   
    For Each c In f.Controls
       
        If TypeOf c Is SubForm Then
            Set sf = c.Form
            For Each sc In sf.Controls
                Debug.Print sc.Name
            Next
        End If
       
    Next

and got exactly what I expected, a list of the names of all the controls on the subform.
0
 
NestorioCommented:
Try, Forms(ActiveForm)
0
 
bastille2Author Commented:
From my reading, a microsoft article said that if a field on a subform has the focus, the ActiveForm is the main form, not the subform.  Seems stupid but that's what it said.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
pique_techCommented:
Screen.ActiveForm.SubFormName.Form.ControlName would be my guess...but it is just that, not substantiated by testing.
0
 
pique_techCommented:
Or rather, more relevant to your post above:

Set MyForm = Screen.ActiveForm

For Each C In MyForm.Controls
  Case acTextbox, acCombobox, acListbox, acOptiongroup
     do some stuff
  Case acSubform
  Set MySubForm = Screen.ActiveForm.SubFormName.Form    'you have to refer to the subform control's Form property
    For Each subC In MySubForm.Controls
      Do some stuff
0
 
bastille2Author Commented:
Nestorio,
Yup, I tested it and Forms(ActiveForm) picks up the main form not the subform.

Pique_Tech,  
In your line "Set MySubForm = Screen.ActiveForm.SubFormName.Form"  I'm trying to pass the subform name as C from the loop through the main form controls.  Would I then use "Set MySubForm = Screen.ActiveForm.C.Form"?
0
 
pique_techCommented:
Yes, that should work, as C would at that point be (in Access's mind) a control on the main form.  

I'm sorry, I'm obviously a bit distracted and should have caught that first or second time through.  ; )
0
 
bastille2Author Commented:
Pique_Tech,

I tested MySubForm=Screen.ActiveForm.C.Controls with a breakpoint and at that line it gave error "Application defined or object defined error".  
0
 
bastille2Author Commented:
Just another thought - at this point C is the name of the control that is C.ControlType/Case acSubform.  

Maybe the name a form uses for a control (that happens to be a subform) is different from the name that the subfrom uses for itself?  One is a control name, the other is a form name?  Just a guess.
0
 
bastille2Author Commented:
Sorry, I meant I tested " Set MySubForm = Screen.ActiveForm.C.Form", my brains a bit overloaded too!
0
 
bastille2Author Commented:
I'll try it and let you know.  Thanks
0
 
bastille2Author Commented:
Pique_tech,

You've given good help so I'm awarding you the points.  However, I've decided that this approach is fundamentally too complex and error prone.  I'm going to take 10 steps back and try and develop a much simpler way to get the job done.

I'm sure I'll have more questions soon!
Thanks again,
Bob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.