Solved

Trouble referencing subform from Screen.ActiveForm

Posted on 2004-09-30
12
493 Views
Last Modified: 2012-06-22
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
Comment
Question by:bastille2
  • 7
  • 4
12 Comments
 
LVL 16

Expert Comment

by:Nestorio
ID: 12192960
Try, Forms(ActiveForm)
0
 

Author Comment

by:bastille2
ID: 12193041
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12193176
Screen.ActiveForm.SubFormName.Form.ControlName would be my guess...but it is just that, not substantiated by testing.
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12193207
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
 

Author Comment

by:bastille2
ID: 12193316
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
 
LVL 12

Expert Comment

by:pique_tech
ID: 12193378
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:bastille2
ID: 12193399
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
 

Author Comment

by:bastille2
ID: 12193461
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
 

Author Comment

by:bastille2
ID: 12193535
Sorry, I meant I tested " Set MySubForm = Screen.ActiveForm.C.Form", my brains a bit overloaded too!
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 275 total points
ID: 12193631
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
 

Author Comment

by:bastille2
ID: 12193647
I'll try it and let you know.  Thanks
0
 

Author Comment

by:bastille2
ID: 12195777
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now