VB code to extract control properties from ALL forms

I have the following code to return select properties of all controls on my form.

However, this code is not giving me the controls of the current form I am on.  When the database opens, I open a hidden form (frmActiveUser) that stays active.  The code keeps referring to this hidden form.  

If I do not log into the database and open the hidden form. I get all controls I am requesting with values (if there are any).

Does the line    DoCmd.OpenForm frm, acDesign, , , , acHidden  need to specify the form name?  For example, I want to start with a form called frmProject.  
Should the line be changed to;
DoCmd.OpenForm frmProject instead of DoCmd.OpenForm frm

Is it possible to cycle through all forms en masse without having to put this (or any other code) on each form?  The code I posted earlier to document my tables does this.

Also, how can I get column titles at the top of the output?
Is there a way to get the name of the form on the output?
Private Sub cmdDoc_Click() 
Dim ctl As Control 
Dim prp As Property 
Dim strName As String 
Dim strControlType As String 
Dim strControlSource As String 
Dim strFormat As String 
Dim strDecimalPlaces As String 
Dim strVisible As String 
Dim strInputMask As String 
Dim strDevaultValue As String 
Dim strValidationRule As String 
Dim strValidationText As String 
 
Open CurrentProject.Path & "\" & frm & "_FormControls.txt" For Output As #1 
On Error Resume Next 
 
DoCmd.OpenForm frm, acDesign, , , , acHidden 
 
For Each ctl In Forms(frm).Controls 
strName = "" 
strControlType = "" 
strControlSource = "" 
strFormat = "" 
strDecimalPlaces = "" 
strVisible = "" 
strInputMask = "" 
strDevaultValue = "" 
strValidationRule = "" 
strValidationText = "" 
            On Error Resume Next  ' Ignore missing properties 
    strName = ctl.Properties("Name") 
    strControlType = ctl.Properties("ControlType") 
    strControlSource = ctl.Properties("ControlSource") 
    strFormat = ctl.Properties("Format") 
    strDecimalPlaces = ctl.Properties("DecimalPlaces") 
    strVisible = ctl.Properties("Visible") 
    strInputMask = ctl.Properties("InputMask") 
    strDevaultValue = ctl.Properties("DevaultValue") 
    strValidationRule = ctl.Properties("ValidationRule") 
    strValidationText = ctl.Properties("ValidationText") 
            On Error GoTo 0 
'    Print #1, strName; "^"; ctl.Properties("ControlType") 
    Print #1, ctl.Properties("Name"); "^"; strControlType; "^"; strControlSource; "^"; _ 
    strFormat; "^"; strDecimalPlaces; "^"; strVisible; "^"; _ 
    strInputMask; "^"; strDevaultValue; "^"; strValidationRule; "^"; strValidationText 
 
Next ctl 
'DoCmd.Close acForm, frm, acSaveNo 
Set ctl = Nothing 
Set prp = Nothing 
Close #1 
MsgBox "File has been created" 
End Sub

Open in new window

LVL 1
ScamquistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
lets start with this, tthis will list all forms in the current db that you run the codes

sub getFormsProp()
open "c:\myFormsList.txt" for output as #1
dim frm as object
for each frm in currentproject.allforms
     print #1, frm.name        

next
close #1
end sub
0
Rey Obrero (Capricorn1)Commented:
lets, get all the controls listed per form

Sub getFormsProp()
Open "c:\myFormsList.txt" For Output As #1
Dim frm As Object, ctl As Control
For Each frm In CurrentProject.AllForms
    Print #1, frm.Name
    DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
        For Each ctl In Forms(frm.Name)
           
            Print #1, vbTab & ctl.Name
        Next
    DoCmd.Close acForm, frm.Name, acSaveNo
   
Next
Close #1
End Sub


post your feed back if you want to limit the control types that you want to listed

0
Rey Obrero (Capricorn1)Commented:
getting the textbox, combo box and listbox controls



Sub getFormsProp()
Open "c:\myFormsList.txt" For Output As #1
Dim frm As Object, ctl As Control
For Each frm In CurrentProject.AllForms
    Print #1, frm.Name
    DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
        For Each ctl In Forms(frm.Name)
            If ctl.ControlType = acTextBox _
                Or ctl.ControlType = acComboBox _
                Or ctl.ControlType = acListBox Then
            Print #1, vbTab & ctl.Name
            End If
        Next
    DoCmd.Close acForm, frm.Name, acSaveNo
    
Next
Close #1
End Sub

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JezWaltersCommented:
If you put the property names into a collection, you can loop through the collection to print your colum headings, as well as to extract the values themselves.
0
ScamquistAuthor Commented:
JezWalters,
Are you referring to my original code post at line 21?

You were great help getting my table documentation to work.  I have been trying to do similar with forms.  
0
JezWaltersCommented:
This was the kind of thing I had in mind:
Public Sub cmdDoc_Click()

    Dim colProperties As Collection
    Dim ctlControl As Control
    Dim dbsCurrent As DAO.Database
    Dim docForm As DAO.Document
    Dim frmForm As Form
    Dim intFile As Integer
    Dim strFile As String
    Dim strProperty As String
    Dim strRecord As String
    Dim varProperty As Variant

    Set colProperties = New Collection
    colProperties.Add "Name"
    colProperties.Add "ControlType"
    colProperties.Add "ControlSource"
    colProperties.Add "Format"
    colProperties.Add "DecimalPlaces"
    colProperties.Add "Visible"
    colProperties.Add "InputMask"
    colProperties.Add "DefaultValue"
    colProperties.Add "ValidationRule"
    colProperties.Add "ValidationText"

    Set dbsCurrent = CurrentDb

    For Each docForm In dbsCurrent.Containers("Forms").Documents

        intFile = FreeFile
        strFile = docForm.Name & "_FormControls.txt"
        Open CurrentProject.Path & "\" & strFile For Output Access Write As #intFile
        strRecord = ""
        For Each varProperty In colProperties
            strRecord = strRecord & varProperty & "^"
        Next
        strRecord = Left(strRecord, Len(strRecord) - 1)
        Print #intFile, strRecord

        DoCmd.OpenForm docForm.Name, acDesign, , , , acHidden
        Set frmForm = Forms(docForm.Name)

        On Error Resume Next  ' Ignore missing property
        For Each ctlControl In frmForm.Controls
            strRecord = ""
            For Each varProperty In colProperties
                strProperty = ""
                If varProperty = "ControlType" Then
                    strProperty = TypeName(ctlControl)  ' Use meaningful name instead
                Else
                    strProperty = ctlControl.Properties(varProperty)
                End If
                strRecord = strRecord & strProperty & "^"
            Next
            strRecord = Left(strRecord, Len(strRecord) - 1)
            Print #intFile, strRecord
        Next
        On Error GoTo 0

        Set frmForm = Nothing
        DoCmd.Close acForm, docForm.Name

        Close intFile

    Next

    Set dbsCurrent = Nothing

    Set colProperties = New Collection

    MsgBox strFile & " has been created."
End Sub

Open in new window

0
ScamquistAuthor Commented:
Very cool.
I combined Capricorn1 with previous code from JezWalters and I am almost there.
Still confused as how to get the column headings.

Also, the ControlType is retuning a number, 100, 109, 103 etc.  Is there a command to return the name fo the ControlType as opposed to the number value?
Private Sub cmdDoc_Click()
Dim ctl As Control
Dim prp As Property
Dim strName As String
Dim strControlType As String
Dim strControlSource As String
Dim strFormat As String
Dim strDecimalPlaces As String
Dim strVisible As String
Dim strInputMask As String
Dim strDevaultValue As String
Dim strValidationRule As String
Dim strValidationText As String

Open CurrentProject.Path & "\" & frm & "_FormControls.txt" For Output As #1
On Error Resume Next

DoCmd.OpenForm frm, acDesign, , , , acHidden

For Each ctl In Forms(frm).Controls
strName = ""
strControlType = ""
strControlSource = ""
strFormat = ""
strDecimalPlaces = ""
strVisible = ""
strInputMask = ""
strDevaultValue = ""
strValidationRule = ""
strValidationText = ""
            On Error Resume Next  ' Ignore missing properties
    strName = ctl.Properties("Name")
    strControlType = ctl.Properties("ControlType")
    strControlSource = ctl.Properties("ControlSource")
    strFormat = ctl.Properties("Format")
    strDecimalPlaces = ctl.Properties("DecimalPlaces")
    strVisible = ctl.Properties("Visible")
    strInputMask = ctl.Properties("InputMask")
    strDevaultValue = ctl.Properties("DevaultValue")
    strValidationRule = ctl.Properties("ValidationRule")
    strValidationText = ctl.Properties("ValidationText")
            On Error GoTo 0
'    Print #1, strName; "^"; ctl.Properties("ControlType")
    Print #1, ctl.Properties("Name"); "^"; strControlType; "^"; strControlSource; "^"; _
    strFormat; "^"; strDecimalPlaces; "^"; strVisible; "^"; _
    strInputMask; "^"; strDevaultValue; "^"; strValidationRule; "^"; strValidationText

Next ctl
'DoCmd.Close acForm, frm, acSaveNo
Set ctl = Nothing
Set prp = Nothing
Close #1
MsgBox "File has been created"
End Sub

Open in new window

0
ScamquistAuthor Commented:
I have tired a number of changes to get JezWalters code to output all forms to a single document.  It keeps blowing up, creating a blank file or only giving contols for one form.

The ideal output is

FormName1
Column Headers
   Control1 Name^ControlType^ControlSource...
   Control2 Name^ControlType^ControlSource...
   Control3 Name^ControlType^ControlSource...
FormName2
Column Headers
   Control1 Name^ControlType^ControlSource...
   Control2 Name^ControlType^ControlSource...
   Control3 Name^ControlType^ControlSource...
.
.
.
0
Rey Obrero (Capricorn1)Commented:
did you check the code i posted at
http:#a29104003
0
JezWaltersCommented:
The code I posted in http://#a29108206 above gives you column headings as well as control type names, not numbers - run it and you'll see what I mean.
If you want all the output in just one file, this should give you the format you're after:

Public Sub cmdDoc_Click()

    Dim colProperties As Collection
    Dim ctlControl As Control
    Dim dbsCurrent As DAO.Database
    Dim docForm As DAO.Document
    Dim frmForm As Form
    Dim intFile As Integer
    Dim strProperty As String
    Dim strRecord As String
    Dim varProperty As Variant

    Set colProperties = New Collection
    colProperties.Add "Name"
    colProperties.Add "ControlType"
    colProperties.Add "ControlSource"
    colProperties.Add "Format"
    colProperties.Add "DecimalPlaces"
    colProperties.Add "Visible"
    colProperties.Add "InputMask"
    colProperties.Add "DefaultValue"
    colProperties.Add "ValidationRule"
    colProperties.Add "ValidationText"

    intFile = FreeFile
    Open CurrentProject.Path & "\FormControls.txt" For Output Access Write As #intFile

    Set dbsCurrent = CurrentDb
    For Each docForm In dbsCurrent.Containers("Forms").Documents

        Print #intFile, docForm.Name

        strRecord = ""
        For Each varProperty In colProperties
            strRecord = strRecord & varProperty & "^"
        Next
        strRecord = Left(strRecord, Len(strRecord) - 1)
        Print #intFile, strRecord

        DoCmd.OpenForm docForm.Name, acDesign, , , , acHidden
        Set frmForm = Forms(docForm.Name)

        On Error Resume Next  ' Ignore missing property
        For Each ctlControl In frmForm.Controls
            strRecord = ""
            For Each varProperty In colProperties
                strProperty = ""
                If varProperty = "ControlType" Then
                    strProperty = TypeName(ctlControl)  ' Use meaningful name instead
                Else
                    strProperty = ctlControl.Properties(varProperty)
                End If
                strRecord = strRecord & strProperty & "^"
            Next
            strRecord = Left(strRecord, Len(strRecord) - 1)
            Print #intFile, strRecord
        Next
        On Error GoTo 0

        Set frmForm = Nothing
        DoCmd.Close acForm, docForm.Name

    Next

    Set dbsCurrent = Nothing

    Close intFile

    Set colProperties = New Collection

    MsgBox "File has been created."

End Sub

Open in new window

0
JezWaltersCommented:
Please don't get me wrong, but you appear not to have actually tried the code I posted last time.  If you give what I've just posted a whirl, you should find it gives you everything you're after!
0
JezWaltersCommented:
To give you the idea, the attached database yields the attached form controls file
Q25607675.mdb
FormControls.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JezWaltersCommented:
I've added a few comments to my earlier code, to help explain what's going on.  I've also created a DELIMITER constant, so that you can change field delimiters by just changing the constant's value.
By the way, I have a feeling that the properties you're extracting aren't likely to be the most useful for documenting controls.  The beauty of my solution is that it's trivial to Add a different set of property names to the colProperties list and everything else will come out in the wash.
0
ScamquistAuthor Commented:
JezWalters,
Thank you for your patience.  I did try the code you posted, which gave extracted the controls and properites.  However, the code created a separate file for each form.  I played around with code recommendations from Capricorn1 and your solution but kept running into problems.  

Your final post did the trick.  

The hard part is how to reward both for your assistance.
0
ScamquistAuthor Commented:
Thank you both.  

I try to apply the lessons I learn in my posts and apply them in different ways.  Each time i learn a new concept.

I truly appreciate your assistance in this.

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.