[AccessD] Walk the controls on a given form

Arthur Fuller artful at rogers.com
Sun Jul 10 09:07:46 CDT 2005


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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list