SpaceCoastLife
asked on
Looking for System Objects
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SpaceCoastLife,
so, you don't like the Documenter ?
so, you don't like the Documenter ?
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("S elect * 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
Works OK for me, ...Points to fyed
Dim ctrl As Control
Dim rs As DAO.Recordset
Dim frm As Form
Set rs = CurrentDb.OpenRecordset("S
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
oh and add this to the end of the code:
rs.Close
set rs=Nothing
rs.Close
set rs=Nothing
ASKER
Capricorn1: I tried the Document-er but don't see how it gets me where I need to go
ASKER
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?
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?
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
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
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("S elect * 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
Public Sub EnumerateFormControls()
Dim frm As Form
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("S
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
ASKER
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
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
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
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
ASKER
I'm getting confused. would you please republish exactly what code in total I should be using?
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.
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
ASKER
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?
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?
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"?
...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"?
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.
ASKER
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.
Nonetheless, thanks to everyone for responding.
Tools > Analyze > Documenter