locked
trouble with looping through an access 2010 form RRS feed

  • Question

  • I know this question has been asked before but something happened and well, it doesn't make any sense to me. 

     

    I have a access 2010 form with lots of controls on it (labels, textboxes, buttons). Now, I want to loop through each of these controls and access the tag property. Note, the controls I want to access are in a Tab Control. Typically, you loop through like this:

     

    Dim ctl As Control

    For Each ctl in Me.Controls

     Select Case ctl.ControlType

     Case acTextBox

      ' do something

     End Select

    Next ctl

    This should work, but it does not. In my version of access (2010) VBA does not give me access to the ControlType or Tag or many other properties!! Ok, what is going on here? What am I doing wrong?

     

    Ed Cohen

     

    Update, I just found a neat function at http://allenbrowne.com/AppPrintMgtCode.html#HasProperty. Combined with some testing code, that solved the problem. Sorry to both anyone!

     

    Ed Cohen


    Edgar Cohen
    • Edited by Edgar Cohen Thursday, December 8, 2011 8:37 PM
    Thursday, December 8, 2011 7:54 PM

Answers

  • Hi Edgar

    Edgar Cohen wrote:

    Dim ctl As Control

    For Each ctl in Me.Controls

    Select Case ctl.ControlType

    Case acTextBox

    ' do something

    End Select

    Next ctl

    This should work, but it does not. In my version of access (2010) VBA
    does not give me access to the ControlType or Tag or many other
    properties!! Ok, what is going on here? What am I doing wrong?

    The controls in other controls are not automatically populated to the Form instance Me.
    You will have to loop inside the loop. If you find a container control (ControlTypes are acTabCtl, acPage and acSubForm). These container controls are the controls that may have child controls inside. As the child controls may be such container controls again, so you will need a recursive function call to reach all controls within your form.

    Here an example that will debug the controls and container controls in the debug window.

    Public Function getControls(FormName As String)
     Dim ctl As Control
     Dim subCtl As Control
     For Each ctl In Forms(FormName).Controls
         If ctl.ControlType = acTabCtl _
             Or ctl.ControlType = acSubform _
             Or ctl.ControlType = acPage Then
             Debug.Print "--> " & ctl.Name
             Call getSubControls(ctl)
             Debug.Print "<-- " & ctl.Name
         End If
     Next
    End Function
    
    Public Function getSubControls(ctl As Control)
     Dim subCtl As Control
     For Each subCtl In ctl.Controls
         If subCtl.ControlType = acTabCtl _
         Or subCtl.ControlType = acSubform _
         Or subCtl.ControlType = acPage Then
             Debug.Print "--> "; subCtl.Name
             Call getSubControls(subCtl)    '-> recursive call
             Debug.Print "<-- "; subCtl.Name
         Else
             Debug.Print subCtl.Name
         End If
     Next
    End Function

    copy/paste above code into a new standard module and run it in the immediate window of the IDE with following line of code (replace NameOfYourForm with your real form name:
    call getControls("NameOfYourForm")

    Above code not deeply tested and may contain some bugs, but should be good enough to explain the approach

    HTH
    Henry

    • Marked as answer by danishani Thursday, February 16, 2012 1:06 AM
    Friday, December 9, 2011 10:51 AM
  • Note, the controls I want to access are in a Tab Control. Typically, you loop through like this:

    Hello Ed,

    I realize that you have a solution to your problem but I thought that it is worth pointing out what I believe to be the problem with the code you posted. You said that the controls are in a Tab control so you need to loop through the specific page of the tab control. Your posted code loops through all controls in the form.

    Private Sub Command12_Click()
      Dim ctl As Control
     
      'In the following line "TabCtl5" is the name of the tab control _
       and Page6 is obviously the specific page required.
      For Each ctl In Me.TabCtl5.Pages("Page6").Controls
        Select Case ctl.ControlType
          Case acTextBox
            MsgBox ctl.Name
        End Select
      Next ctl

    End Sub

    Following added with Edit as an after thought.

    Can also loop through the pages of a Tab control and can loop through all controls in all pages of the Tab control.

    Private Sub Command13_Click()
      'Iterate through pages of Tab control _
       and then interate through controls on the page.
      Dim pge As Page
      Dim ctl As Control
     
      For Each pge In Me.TabCtl5.Pages
        Select Case pge.Name
          Case "Page6"
            For Each ctl In pge.Controls
              MsgBox ctl.Name
            Next ctl
          Case "Page7"
            'code here
        End Select
      Next pge
    End Sub

    Private Sub Command14_Click()
      'Iterate through all controls in all pages of Tab control _
       Also returns Page tabs as separate controls.
      'Note .Controls is not tacked on end of Me.TabCtl5. Don't _
       know the reason why Controls on end does not work.
      Dim ctl As Control
     
      For Each ctl In Me.TabCtl5
        MsgBox ctl.Name
      Next ctl
    End Sub


    Regards, OssieMac
    • Edited by OssieMac Tuesday, January 10, 2012 12:46 AM
    • Marked as answer by danishani Thursday, February 16, 2012 1:06 AM
    Tuesday, January 10, 2012 12:12 AM

All replies

  • Hi Ed,

     

    Only way I got this to work is by opening the Form in Design modus from another Form, then save it again.

    See below code I have used for testing:

     

    Private Sub Command0_Click()
    
    DoCmd.OpenForm "frmCTLtst", acDesign
    
    Dim ctl As Control
    
    For Each ctl In Form_frmCTLtst.Controls
             
             If ctl.ControlType = acTextBox Then
                
                 ctl.Tag = "Test"
    
            End If
          
    Next ctl
    
    DoCmd.Close acForm, "frmCTLtst", acSaveYes
    
    End Sub


    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, December 8, 2011 9:08 PM
  • Hi Edgar

    Edgar Cohen wrote:

    Dim ctl As Control

    For Each ctl in Me.Controls

    Select Case ctl.ControlType

    Case acTextBox

    ' do something

    End Select

    Next ctl

    This should work, but it does not. In my version of access (2010) VBA
    does not give me access to the ControlType or Tag or many other
    properties!! Ok, what is going on here? What am I doing wrong?

    The controls in other controls are not automatically populated to the Form instance Me.
    You will have to loop inside the loop. If you find a container control (ControlTypes are acTabCtl, acPage and acSubForm). These container controls are the controls that may have child controls inside. As the child controls may be such container controls again, so you will need a recursive function call to reach all controls within your form.

    Here an example that will debug the controls and container controls in the debug window.

    Public Function getControls(FormName As String)
     Dim ctl As Control
     Dim subCtl As Control
     For Each ctl In Forms(FormName).Controls
         If ctl.ControlType = acTabCtl _
             Or ctl.ControlType = acSubform _
             Or ctl.ControlType = acPage Then
             Debug.Print "--> " & ctl.Name
             Call getSubControls(ctl)
             Debug.Print "<-- " & ctl.Name
         End If
     Next
    End Function
    
    Public Function getSubControls(ctl As Control)
     Dim subCtl As Control
     For Each subCtl In ctl.Controls
         If subCtl.ControlType = acTabCtl _
         Or subCtl.ControlType = acSubform _
         Or subCtl.ControlType = acPage Then
             Debug.Print "--> "; subCtl.Name
             Call getSubControls(subCtl)    '-> recursive call
             Debug.Print "<-- "; subCtl.Name
         Else
             Debug.Print subCtl.Name
         End If
     Next
    End Function

    copy/paste above code into a new standard module and run it in the immediate window of the IDE with following line of code (replace NameOfYourForm with your real form name:
    call getControls("NameOfYourForm")

    Above code not deeply tested and may contain some bugs, but should be good enough to explain the approach

    HTH
    Henry

    • Marked as answer by danishani Thursday, February 16, 2012 1:06 AM
    Friday, December 9, 2011 10:51 AM
  • Hi Edgar,

     

    Good to know, you have found your answer.

    Please post your answer and mark that as your answer, so the thread will be closed.

     

    Thanks again,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, January 9, 2012 8:42 PM
  • Note, the controls I want to access are in a Tab Control. Typically, you loop through like this:

    Hello Ed,

    I realize that you have a solution to your problem but I thought that it is worth pointing out what I believe to be the problem with the code you posted. You said that the controls are in a Tab control so you need to loop through the specific page of the tab control. Your posted code loops through all controls in the form.

    Private Sub Command12_Click()
      Dim ctl As Control
     
      'In the following line "TabCtl5" is the name of the tab control _
       and Page6 is obviously the specific page required.
      For Each ctl In Me.TabCtl5.Pages("Page6").Controls
        Select Case ctl.ControlType
          Case acTextBox
            MsgBox ctl.Name
        End Select
      Next ctl

    End Sub

    Following added with Edit as an after thought.

    Can also loop through the pages of a Tab control and can loop through all controls in all pages of the Tab control.

    Private Sub Command13_Click()
      'Iterate through pages of Tab control _
       and then interate through controls on the page.
      Dim pge As Page
      Dim ctl As Control
     
      For Each pge In Me.TabCtl5.Pages
        Select Case pge.Name
          Case "Page6"
            For Each ctl In pge.Controls
              MsgBox ctl.Name
            Next ctl
          Case "Page7"
            'code here
        End Select
      Next pge
    End Sub

    Private Sub Command14_Click()
      'Iterate through all controls in all pages of Tab control _
       Also returns Page tabs as separate controls.
      'Note .Controls is not tacked on end of Me.TabCtl5. Don't _
       know the reason why Controls on end does not work.
      Dim ctl As Control
     
      For Each ctl In Me.TabCtl5
        MsgBox ctl.Name
      Next ctl
    End Sub


    Regards, OssieMac
    • Edited by OssieMac Tuesday, January 10, 2012 12:46 AM
    • Marked as answer by danishani Thursday, February 16, 2012 1:06 AM
    Tuesday, January 10, 2012 12:12 AM