List all Control names in all Forms in Access 2010

[pcplayer]

What I am trying to do is write a routine that will not only list all the forms that in access but then open the form and list all the controls in them.
This code works to a point but it doesn't list all the forms nor all the controls. ultimately I would like to write this out to tables as I have a customer that wants to lock and maintain controls per from. A pain I know but it pays

Sub GetAllFormsAndControls()
On Error Resume Next


Dim objAccObj As AccessObject
Dim objFormAs Object
Dim strForm As String
Dim ctl As Control
Dim objActiveForm As Form

Set objForm = Application.CurrentProject
For Each objAccObj In objForm.AllForms
strForm= objAccObj.Name
Debug.Print strForm
DoCmd.OpenForm strForm, acDesign
Set objActiveForm = Application.Screen.ActiveForm
For Each ctl In objActiveForm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox, acOptionButton, acToggleButton, acSubform, acCommandButton
Debug.Print " " & ctl.Name
End Select
Next ctl
DoCmd.close acForm, strForm
Next objAccObj
End Sub

Thanks for any help you can shed on this.

[alansidman]

Why not just use the built in analyzer in Access. Click on the Database tools on the ribbon. Then click on the Data base Documentor. It will give you all the details on every object and control in your db.

Alan

[pcplayer]

Thanks for the reply, I really never used the wizards but that is cool. I was looking to use the code to write the information to tables. this looks like a lot of copy and paste and way to much information to dig through. Thank though.

[orange]

I looked at your code and made some revisions. This routine will create a table and identify all Forms,
Controls, Control Names and Control types to the table. It will delete and recreate the table on each execution.

I have included the acConstant for the control type in the Control_Type field. I found that type 119 was really a "custom control".
That is could be Calendar, WebBrowser or TreeView (and possibly more --that's what I traced it down to).

'---------------------------------------------------------------------------------------
' Procedure : GetAllFormsAndControls
' Author    : Jack
' Date      : 12/01/2013
' Purpose   : To iterate all forms and report all controls by form, control name and control type.
'
' posted question:
'https://www.accessforums.net/showthread.php?t=31409&p=154208#post154208

'---------------------------------------------------------------------------------------
'
Sub GetAllFormsAndControls()
10       On Error GoTo GetAllFormsAndControls_Error

20    On Error Resume Next
      Dim objAccObj As AccessObject
      Dim objForm As Object
      Dim strForm As String
      Dim ctl As Control
      Dim objActiveForm As Form
      '
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
      Dim strSQL_DROP As String
      Dim strSQL_Create As String
       'Delete existing copy of this table
30           strSQL_DROP = "DROP TABLE tblControlsOnForms;"

40           DoCmd.RunSQL strSQL_DROP

50           strSQL_Create = "CREATE TABLE tblControlsOnForms" & _
                                "(form_name varchar(250), control_name varchar(40),control_type varchar(25));"

60           DoCmd.RunSQL strSQL_Create
70    Set db = CurrentDb
80    Set rs = db.OpenRecordset("tblControlsOnForms")
90    With rs
100   Set objForm = Application.CurrentProject
110   For Each objAccObj In objForm.AllForms


120       strForm = objAccObj.name
130       Debug.Print strForm

          
140       DoCmd.OpenForm strForm, acDesign
150       Set objActiveForm = Application.Screen.ActiveForm
160       For Each ctl In objActiveForm.Controls
170   .AddNew
180   !form_name = strForm
190           Select Case ctl.ControlType
              Case 119 ' acWebBrowser, Treeview, Calendar
200           !control_Type = "Custom control"
210           Case acTabCtl
220           !control_Type = "TabCtl"
230           Case acLabel
240           !control_Type = "Label"
250           Case acTextBox
260           !control_Type = "TextBox"
270           Case acComboBox
280           !control_Type = "ComboBox"
290           Case acCheckBox
300           !control_Type = "CheckBox"
310           Case acListBox
320           !control_Type = "ListBox"
330           Case acOptionButton
340           !control_Type = "OptionButton"
350           Case acToggleButton
360           !control_Type = "ToggleButton"
370           Case acSubform
380           !control_Type = "SubForm"
390           Case acCommandButton
400           !control_Type = "CommandButton"
410           Case acObjectFrame
420           !control_Type = "ObjectFrame"
430           Case acBoundObjectFrame
440            !control_Type = "BoundObjectFrame"
450           Case acRectangle
460           !control_Type = "Rectangle"
470           Case acLine
480           !control_Type = "Line"
490           Case acImage
500           !control_Type = "Image"
510           Case acPage
520           !control_Type = "Page"
530           Case acPageBreak
540           !control_Type = "PageBreak"
550           Case acOptionGroup 
560           !control_Type = "Option Group"
570           End Select
580           Debug.Print " " & ctl.name & "  " & ctl.ControlType
590   !control_name = ctl.name
600   !control_Type = !control_Type & "  " & ctl.ControlType  '  used to associate the control with the acControl constant
610   .Update
              
620       Next ctl
630       DoCmd.Close acForm, strForm
XXX:
640   Next objAccObj

650      On Error GoTo 0
660      Exit Sub
670   End With
GetAllFormsAndControls_Error:

680       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure GetAllFormsAndControls of Module AWF_Related"
End Sub

Good luck with your project.

[pcplayer]

Thank you so much. You didn't have to go to all that trouble to write to the table. I could have done that after I got a hold of the controls, but you did and I thank you. It was very kind of you. I worked perfectly.

[orange]

You are welcome. Happy to help.

It could be useful to others. I haven't looked into the "customized controls", but my guess is there is a way to distinguish one from another -- if that's important to you.

[Nod_Swerdna]

Hi,
How the heck did you make your animation, it's hilarious!
Don.