asked on

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

Nestorio

Try, Forms(ActiveForm)

bastille2

ASKER

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.

pique_tech

Screen.ActiveForm.SubFormName.Form.ControlName would be my guess...but it is just that, not substantiated by testing.

pique_tech

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

bastille2

ASKER

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"?

pique_tech

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. ; )

bastille2

ASKER

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".

bastille2

ASKER

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.

bastille2

ASKER

Sorry, I meant I tested " Set MySubForm = Screen.ActiveForm.C.Form", my brains a bit overloaded too!

ASKER CERTIFIED SOLUTION

pique_tech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bastille2

ASKER

I'll try it and let you know. Thanks

bastille2

ASKER

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