[AccessD] Walk the controls on a given form

MartyConnelly martyconnelly at shaw.ca
Mon Jul 11 11:43:53 CDT 2005


This works just pass form name and set form in subroutine

Sub ListRowDataSources(fname As String)
Dim ctl As Control
Dim f As Form
   'assume the form has already been opened by the calling process
   'I want to walk the controls
   'the only controls of interest are combos or listboxes so I can skip
   'over all others
   Set f = Forms(fname)
   Debug.Print f.Name
   For Each ctl In f.Controls
      'If the control is either a listbox or combo-box
      Select Case ctl.ControlType
      Case acComboBox, acListBox
         Debug.Print ctl.Name & ":" & ctl.RowSource
      Case Else
      End Select
   Next
set f=nothing
set ctl=nothing
End Sub

Jim Lawrence wrote:

>...but this would work.
>
>Dim frm as Form
>
>Jim
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
>Sent: Sunday, July 10, 2005 12:31 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Walk the controls on a given form
>
>Yes, you cannot pass an object in as a form
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
>Sent: Sunday, July 10, 2005 10:08 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Walk the controls on a given form
>
>
>I'm still doing something wrong, clearly. It doesn't seem to like the
>declaration (perhaps because in the outer function I'm dimming frm as an
>object not a form?)
>
>'---------------------------------------------------------------------------
>------------
>' Procedure : ListFormDataSources
>' DateTime  : 31/05/2005 09:37
>' Author    : Arthur Fuller
>' Purpose   : list all the data sources from the forms in the current
>database
>'---------------------------------------------------------------------------
>------------
>'
>Sub ListFormDataSources()
>
> Dim frm As AccessObject   ' changing this to object or form doesn't work
> Dim db As CurrentProject
>    Dim i As Integer
>    On Error GoTo ListFormDataSources_Error
>
>    Set db = CurrentProject
>
>    Application.Echo False
>
>    'Check form recordsource
>    For Each frm In db.AllForms
>        DoCmd.OpenForm frm.Name, acDesign
>        If Forms(frm.Name).RecordSource <> "" Then
>            Debug.Print i, frm.Name & ": "
>            Debug.Print Forms(frm.Name).RecordSource
>        End If
>        ListRowDataSources (frm)
>        DoCmd.Close acForm, frm.Name
>        i = i + 1
>    Next frm
>
>    Application.Echo True
>
>    Set frm = Nothing
>    Set db = Nothing
>
>    On Error GoTo 0
>    Exit Sub
>
>ListFormDataSources_Error:
>
>    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
>ListFormDataSources of Module aa_Listers"
>
> End Sub
>
>
>'and then your code...
>
>Sub ListRowDataSources(f As Form)
>Dim ctl As Control
>   'assume the form has already been opened by the calling process
>   'I want to walk the controls
>   'the only controls of interest are combos or listboxes so I can skip
>   'over all others
>   Debug.Print f.Name
>   For Each ctl In f.Controls    
>      'If the control is either a listbox or combo-box
>      Select Case ctl.ControlType
>      Case acComboBox, acListBox
>         Debug.Print ctl.Name & ":" & ctl.RowSource
>      Case Else
>      End Select
>   Next
>End Sub
>
>Arthur
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
>Sent: July 9, 2005 5:18 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Walk the controls on a given form
>
>BTW, this is exactly how my framework's form class instantiates a class for
>each control found on the form.  Using a big case statement I instantiate a
>class specific to the control type, then pass in the control to the class
>instance, and save a pointer to each control class in a collection in the
>form.
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
>Sent: Saturday, July 09, 2005 4:51 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Walk the controls on a given form
>
>
>Sorry, I missed the part about wanting only specific types of controls.
>
>Function TestCtlType()
>Dim ctl As Control
>    ctl.ControlType
>End Function
>
>Place your click on the .ControlType and hit F1.  Help on ControlType will
>come up showing a list fo constants for all control types.  You code will
>now look something like:
>
>Sub DumpRowSources(f As Form)
>Dim ctl As Control
>   'assume the form has already been opened by the calling process
>   'I want to walk the controls
>   'the only controls of interest are combos or listboxes so I can skip
>   'over all others
>   Debug.Print f.Name
>   For Each ctl In f.Controls     '<--- this is the important part
>      'If the control is either a listbox or combo-box
>      Select Case ctl.ControlType
>      Case acComboBox, acListBox
>         Debug.Print ctl.Name & ":" & ctl.RowSource
>      Case Else
>      End Select
>   Next
>End Sub
>
>
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
>Sent: Saturday, July 09, 2005 4:41 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Walk the controls on a given form
>
>
>
>Sub DumpRowSources ( f as Form )
>Dim ctl as control
>   'assume the form has already been opened by the calling process
>   'I want to walk the controls
>   'the only controls of interest are combos or listboxes so I can skip 
>   'over all others
>   Debug.print f.name
>   For Each ctl in f.Controls     '<--- this is the important part
>      If the control is either a listbox or combo-box
>         Debug.print ctl.Name & ":" & ctl.RowSource
>      End If
>   Next
>End Sub
>
>
>John W. Colby
>www.ColbyConsulting.com 
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
>Sent: Saturday, July 09, 2005 3:46 PM
>To: 'Access Developers discussion and problem solving'
>Subject: [AccessD] Walk the controls on a given form
>
>
>I think I have asked this previously, but if I received an answer then I
>misplaced it. Here is exactly what I need.... This is pseudo-code. Don't
>expect it to compile!
>
>Sub DumpRowSources ( f as Form )
>   'assume the form has already been opened by the calling process
>   'I want to walk the controls
>   'the only controls of interest are combos or listboxes so I can skip 
>   'over all others
>   Debug.print f.name
>   For Each ctl in f.ControlsCollection     '<--- this is the important part
>      If the control is either a listbox or combo-box
>         Debug.print ctl.Name & ":"
>         Debug.print ctl.RowSource
>      End If
>   Next
>End Sub
>
>TIA!
>Arthur
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada






More information about the AccessD mailing list