[AccessD] Iterating Controls

Arthur,

I suspect the problem is that you have the frm As Object and not frm As
Form.
I wrote this and it works using the frm As Form without error, you can
easily adapt to your needs:

Sub AllFormsControls()
Dim frm As Form
Dim ctl As Control
Dim objFrm As Object

For Each objFrm In CurrentProject.AllForms
    DoCmd.OpenForm objFrm.Name, acDesign
    Set frm = Forms(objFrm.Name)
    For Each ctl In frm.Controls
        Debug.Print ctl.Name
    Next
    Set frm = Nothing
    DoCmd.Close acForm, objFrm.Name, acSaveNo
Next

End Sub

Chris Mackin
www.denverdb.com
Denver Database Consulting, LLC


-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Michael R Mattys
Sent: Sunday, March 09, 2003 9:12 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Iterating Controls


But Arthur -

You're not using DAO or the properties collection
of controls. There are some things you can't do
with ADO.

Mike Mattys

----- Original Message -----
From: Arthur Fuller
To: accessd at databaseadvisors.com
Sent: Sunday, March 09, 2003 10:48 AM
Subject: RE: [AccessD] Iterating Controls


Swing and a miss! I thought you had a home run there but no. Here’s the
whole sub, and all it prints is the names of the forms. Here’s the entire
sub:
<code>
Public Sub ListRowSources()
    On Error Resume Next
    Dim frm As Object, db As CurrentProject, ctl As Control
    Dim strOutFile As String
    strOutFile = "RowSources.txt"        '"c:\testfile.txt"
    Set db = CurrentProject
    Dim fs As Object, a As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(strOutFile, True)

    Set db = CurrentProject

    Application.Echo False

    Debug.Print "Analyzing Row Sources"

    'Walk through forms
    For Each frm In db.AllForms
        DoCmd.OpenForm frm.name, acDesign
        a.writeline "Form: " & frm.name
        'Walk through controls
        For Each ctl In frm.Controls
            With ctl
                Debug.Print ctl.Properties("Name")
                Select Case .ControlType
                    Case acComboBox
                        Debug.Print ctl.name & ": Combo Box"
                        Debug.Print ctl.RowSource
                        a.writeline "Control: " & ctl.name
                        a.writeline "RowSource"
                        a.writeline ctl.RowSource

                    Case acListBox
                  Debug.Print ctl.name & ": List Box"
                        Debug.Print ctl.RowSource
                        a.writeline "Control: " & ctl.name
                        a.writeline "RowSource"
                        a.writeline ctl.RowSource
                Case Else
                  'do nothing
                End Select
            End With
        Next ctl
        DoCmd.Close acForm, frm.name
    Next frm

    Application.Echo True

    Set frm = Nothing
    Set db = Nothing
    Set fs = Nothing
    Set a = Nothing

 End Sub
</code?

All it does is print the form names. Not a single mention of any control on
any form. Clearly it’s more wrong than On Error. But I’m not sure what.
Arthur

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com] On Behalf Of Michael R Mattys
Sent: March 9, 2003 10:26 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Iterating Controls

Hi Arthur,

You're looking too far to see it up close :)
On Error Resume Next

Mike Mattys