Solved

Trouble referencing subform from Screen.ActiveForm

Posted on 2004-09-30
12
500 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
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.

 
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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Allow user to edit an outgoing email 2 25
SQL Group on First occurrence 9 25
aggregate query? 20 50
Modify report 8 8
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

777 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