[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

VB code to extract control properties from ALL forms

Posted on 2010-03-30
15
Medium Priority
?
340 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Scamquist
  • 6
  • 5
  • 4
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29103197
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 29103644
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29104003
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 17

Expert Comment

by:JezWalters
ID: 29104902
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
 
LVL 1

Author Comment

by:Scamquist
ID: 29107405
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 29108206
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
 
LVL 1

Author Comment

by:Scamquist
ID: 29108868
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
 
LVL 1

Author Comment

by:Scamquist
ID: 29111048
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 29111665
did you check the code i posted at
http:#a29104003
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 29176648
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
 
LVL 17

Expert Comment

by:JezWalters
ID: 29176765
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
 
LVL 17

Accepted Solution

by:
JezWalters earned 1600 total points
ID: 29178826
To give you the idea, the attached database yields the attached form controls file
Q25607675.mdb
FormControls.txt
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 29179283
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
 
LVL 1

Author Comment

by:Scamquist
ID: 29179938
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
 
LVL 1

Author Closing Comment

by:Scamquist
ID: 31709004
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question