Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

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
0
bastille2
Asked:
bastille2
  • 7
  • 4
1 Solution
 
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
 
pique_techCommented:
Screen.ActiveForm.SubFormName.Form.ControlName would be my guess...but it is just that, not substantiated by testing.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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
 
pique_techCommented:
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now