We help IT Professionals succeed at work.

Looking for System Objects

SpaceCoastLife
SpaceCoastLife used Ask the Experts™
on
I'm attempting to create a Data Dictionary for our product and would like to start with identifying all of the form names as well as the control names on each form. I've determined how to locate the form names by looking in the MsObjects table using a Name criteria of -32768. I haven't been able to locate a list of the control names in any of the system tables, however, if indeed such a list exists.

Can anyone shed light on where I might be able to find all of the control names for each form?

I'm using Access 2002
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Have you tried using the Access Documenter?

Tools > Analyze > Documenter
Top Expert 2016

Commented:
In the documenter form, there is an Options button where you can select the properties of the form that you want to be included/excluded in the docmenter
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
If the documenter doesn't meet your needs, you will need to create a loop to loop through each of the forms.  Then within that loop, you will need to loop through the controls collection, something like:

dim ctrl as control
while not rs.eof

    docmd.OpenForm rs!Name, acDesign, ,,,acHidden
    set frm = forms(forms.count - 1)
    for each ctrl in frm.controls
        'you can list a wide variety of control properties here
        'or maybe even use an IF clause to handle different control types differently
        debug.print ctrl.name, ctrl.controltype
    next

    rs.movenext
Wend
MIS Liason
Most Valuable Expert 2012
Commented:
Or buy a product that provides all of this this info, and pretty much anything else you may need in the future....
http://www.fmsinc.com/products/analyzer/compare.htm

JeffCoachman

Author

Commented:
fyed: Tried your code (slightly modified as shown) but keep getting error "Object Variable Or With Block Not Set"


Dim ctrl As Control
Dim rs As Recordset
Dim frm As Form
While Not rs.EOF

    DoCmd.OpenForm rs!Name, acDesign, , , , acHidden
    Set frm = Forms(Forms.count - 1)
    For Each ctrl In frm.Controls
        'you can list a wide variety of control properties here
        'or maybe even use an IF clause to handle different control types differently
        Debug.Print ctrl.Name, ctrl.ControlType
    Next

    rs.MoveNext
Wend

Ideas?

Also boag2000: I looked at the link but it says for Xp only. I'm running W7. I emailed them to confirm.
Top Expert 2016

Commented:
SpaceCoastLife,

so, you don't like the Documenter ?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
I modified fyed's code,

Works OK for me, ...Points to fyed


Dim ctrl As Control
Dim rs As DAO.Recordset
Dim frm As Form

Set rs = CurrentDb.OpenRecordset("Select * FROM msysobjects where Type=-32768")

Do While Not rs.EOF

    DoCmd.OpenForm rs!Name, acDesign, , , , acHidden
    Set frm = Forms(Forms.Count - 1)
    Debug.Print "ctrlName", "ctrlType", "frmName"
    For Each ctrl In frm.Controls
        'you can list a wide variety of control properties here
        'or maybe even use an IF clause to handle different control types differently
        'MsgBox rs!Name
        Debug.Print ctrl.Name, ctrl.ControlType, rs!Name
        DoCmd.Close acForm, ctrl.Name
    Next

    rs.MoveNext
Loop
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
oh and add this to the end of the code:

rs.Close
set rs=Nothing

Author

Commented:
Capricorn1: I tried the Document-er but don't see how it gets me where I need to go

Author

Commented:
boaq2000: Get the following error when attempting to run:

Run-time error '7784'

This form or report is already being designed as a subform or subreport. To open this form in Design view, select the subform or subreport and click Subform in New Window on the View menu.

Ideas?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Then it seems that the code does not allow for subforms...
Perhaps fyed will be along to modify the code.

<I tried the Document-er but don't see how it gets me where I need to go>
The documenter lists all the objects in a form.
Remember, you never posted the exact output you are expecting
(aka: "where I need to go)
;-)
Please explain how you ran the Documentor, what options you selected, and post the output (Click the "Word" Icon in print preview)

JeffCoachman


Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Modifying Jeff's modification of my code.  This subroutine will pass a form variable to the EnumerateControls subroutine, which will check to see whether a control is a subform, and if so, it will recursively call itself.

Public Sub EnumerateFormControls()

Dim frm As Form
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * FROM msysobjects where Type=-32768")

Do While Not rs.EOF

    DoCmd.OpenForm rs!Name, acDesign, , , , acHidden
    Set frm = Forms(Forms.Count - 1)
    Call EnumerateControls(frm)
    DoCmd.Close acForm, frm.Name
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

Public Sub EnumerateControls(frm As Form)

    Dim ctrl As Control

    Debug.Print frm.Name
    For Each ctrl In frm.Controls
        Debug.Print "   -- "; ctrl.Name, ctrl.ControlType
        If ctrl.ControlType = acSubform Then EnumerateControls ctrl.Form
    Next

End Sub




Author

Commented:
fyed: code fails on "Then EnumerateControls ctrl.Form" with the following error:
The expression you entered refers to an object that is closed or doesn't exist"


Public Sub EnumerateControls(frm As Form)

    Dim ctrl As Control

    Debug.Print frm.Name
    For Each ctrl In frm.Controls
        Debug.Print "   -- "; ctrl.Name, ctrl.ControlType
        If ctrl.ControlType = acSubform Then EnumerateControls ctrl.Form
    Next

End Sub
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
That is strange, it worked great for me.  I just ran it against a form that I'm working on that has two subforms and it produced the following, note that as soon as it encounters sub_LD_Basics it jumps to that subform and enumerates those controls, and when it encounters sub_POCs it jumps to that subform and enumerate those.

I added a couple of lines to show the end of each forms controls.

Public Sub EnumerateControls(frm As Form)

    Dim ctrl As Control

    Debug.Print frm.Name
    For Each ctrl In frm.Controls
        Debug.Print "   -- "; ctrl.Name, ctrl.ControlType
        If ctrl.ControlType = acSubform Then EnumerateControls ctrl.Form
    Next
    Debug.Print "END FORM " & frm.Name
    Debug.Print

End Sub

enumeratecontrols Forms!frm_col

frm_CoL
   -- cmd_Close              104
   -- lbl_Options            100
   -- lbl_Form_Title         100
   -- ImageList              119
   -- txt_Dummy              109
   -- cmd_Add  104
   -- lbl_Permissions        100
   -- lbl_My_Info            100
   -- tab_LDs  123
   -- tab_page_Tree          124
   -- tab_page_Basic_Info    124
   -- sub_LD_Basics          112
frm_CoL_subform_Basic_Info
   -- txt_LD_NUM             109
   -- lbl_LD_Num             100
   -- txt_LD_Name            109
   -- lbl_LD_Name            100
   -- txt_LD_Desc            109
   -- lbl_Description        100
   -- txt_LD_Narrative       109
   -- lbl_LD_Narrative       100
   -- cbo_Status             111
   -- lbl_Status             100
   -- cbo_WFFFA_ID           111
   -- lbl_WFFFA_ID           100
   -- cbo_Org_ID             111
   -- lbl_Lead_Org_ID        100
   -- txt_LD_Strat           109
   -- lbl_Strat_Docs         100
   -- txt_LD_Concept         109
   -- lbl_LD_Concept         100
   -- txt_LD_SptDoc          109
   -- lbl_LD_SptDoc          100
   -- chk_LD_Joint           106
   -- lbl_LD_Joint           100
   -- chk_LD_Interagency     106
   -- lbl_LD_Interagency     100
   -- chk_LD_Intergov        106
   -- lbl_LD_Intergov        100
   -- chk_LD_MultiNat        106
   -- lbl_LD_MultiNat        100
   -- txt_LD_ID              109
   -- sub_POCs               112
frm_CoL_subform_POCs
   -- Label6   100
   -- lbl_Role               100
   -- Label5   100
   -- POC_ID   100
   -- Label3   100
   -- Line10   102
   -- cmd_Add  104
   -- cmd_Remove             104
   -- lbl_PointsOfContact    100
   -- lbl_POC_ID             100
   -- txt_LD_ID              109
   -- User_ID  109
   -- txt_POC_Name           109
   -- txt_LD_POC_ID          109
   -- txt_Role               109
   -- txt_Dummy              109
   -- txt_POC_ID             109
END FORM frm_CoL_subform_POCs

   -- cbo_LD_TimeFrame       111
   -- lbl_LD_TimeFrame       100
END FORM frm_CoL_subform_Basic_Info

   -- tab_page_JCAs          124
   -- sub_JCAs               112
frm_CoL_subform_JCAs
   -- tree_JCAs              119
   -- TreeNodeImages         119
   -- txt_JCA_Description    109
   -- lbl_Description        100
END FORM frm_CoL_subform_JCAs

Author

Commented:
I'm getting confused. would you please republish exactly what code in total I should be using?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
The code I posted this morning will enumerate all of the controls on a single form (including all subforms).  You can call it one form at a time, just by doing as I did above by typing in the immediate window.  The syntax would be:

EnumerateControls Forms!FormName

If you want to do that for all of the forms in your databases Forms collection, then you need to run EnumerateFormControls.  It will loop through each of the forms in your database and run the EnumerateControls subroutine for each.  The syntax (from the immediate window) would be:

EnumerateFormControls

Keep in mind, all the subroutine does in write the names of the form and controls to the debug window(which I believe is limited to 250 lines of text).  If you want to do anything else with it, you will need to either write those values to a table or to a variable or some other object.

Public Sub EnumerateFormControls()

Dim frm As Form
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * FROM msysobjects where Type=-32768")

Do While Not rs.EOF

    DoCmd.OpenForm rs!Name, acDesign, , , , acHidden
    Set frm = Forms(Forms.Count - 1)
    Call EnumerateControls(frm)
    DoCmd.Close acForm, frm.Name
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

Public Sub EnumerateControls(frm As Form)

    Dim ctrl As Control

    Debug.Print frm.Name
    For Each ctrl In frm.Controls
        Debug.Print "   -- "; ctrl.Name, ctrl.ControlType
        If ctrl.ControlType = acSubform Then EnumerateControls ctrl.Form
    Next
    Debug.Print "END FORM " & frm.Name
    Debug.Print

End Sub

Open in new window

Author

Commented:
I added a command button on a form and added to the On_Click command:

    Call EnumerateFormControls

It seems to run for a spell but eventually errors on the "Then EnumerateControls ctrl.Form" as before.

Am I doing something wrong?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Just curious...
...I am sure that fyed can gett you sorted.

You still have not stated why the documentor in Access will not "Get you where you want to go"?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I took a look and found the problem, but will have to take a closer look this evening to figure out exactly what is going on.  Will post back sometime this weekend.

Author

Commented:
After reviewing the various suggestions, I've decided Jeff is right. The most straightforward solution is to simply purchase it - which I did.

Nonetheless, thanks to everyone for responding.