After searching and many attempts, I am going to ask for help, if missed a solution on the site I apologize.
What I would like to do is loop thru all the controls on all the forms in my DB, and then write the form name, control name, control type, and hopefully the locked and enabled properties to a table.
In searching the web I have attempted to modify what I found into a function to do this, it will give me the form name and control names, but I can't get the locked and enabled properties to show.
Also I can only open the forms in design view, since I would like to do this with out having to enter any parameters to open the forms.
The hope is to use this information to better document the database and audit the security of the fields on all the forms
Attempt 1
Function test2()
Dim accobj As AccessObject
Dim ctl As Control
Dim strDoc As String
For Each accobj In CurrentProject.AllForms
strDoc = accobj.Name
DoCmd.OpenForm strDoc, acDesign
For Each ctl In Forms(strDoc).Controls
Debug.Print strDoc & vbTab & ctl.Name
Next
DoCmd.Close acForm, strDoc
Next
End Function
Attempt 2 (Not Working)
Function DisplayFormInformation()
Dim frm As Form
Dim strObject As String, aob As AccessObject, obj As Object
Dim strTemp As String, strList As String
Dim strTempForm As Variant
Dim myObject As Object
Dim k As Long
Dim frmControl As Control
Dim frmControls As Controls
Dim controltype As String
Dim intcnt As Integer
Dim i As Integer
Dim ConnectToDB As New ADODB.Connection
Set ConnectToDB = CurrentProject.Connection
Dim rTarget As New ADODB.Recordset
rTarget.Open "ObjectDocumenter", ConnectToDB, adOpenKeyset, adLockOptimistic
Set obj = CurrentProject.AllForms
On Error Resume Next
For Each aob In obj
strTemp = aob.Name
DoCmd.OpenForm strTemp, acDesign
strTempForm = "forms!" & strTemp
Set frm = strTempForm
For Each frmControl In frm.Controls
ReDim astrCtlName(0 To intcnt - 1, 0 To 1)
For i = 0 To intcnt - 1
astrCtlName(i, 0) = frm(i).Name
'Use ControlType to determine the Type of Control
Select Case frm(i).controltype
Case acLabel: astrCtlName(i, 1) = "Label"
Case acRectangle: astrCtlName(i, 1) = "Rectangle"
Case acLine: astrCtlName(i, 1) = "Line"
Case acImage: astrCtlName(i, 1) = "Image"
Case acCommandButton: astrCtlName(i, 1) = "Command Button"
Case acOptionButton: astrCtlName(i, 1) = "Option button"
Case acCheckBox: astrCtlName(i, 1) = "Check box"
Case acOptionGroup: astrCtlName(i, 1) = "Option group"
Case acBoundObjectFrame: astrCtlName(i, 1) = "Bound object frame "
Case acTextBox: astrCtlName(i, 1) = "Text Box"
Case acListBox: astrCtlName(i, 1) = "List box"
Case acComboBox: astrCtlName(i, 1) = "Combo box"
Case acSubform: astrCtlName(i, 1) = "SubForm"
Case acObjectFrame: astrCtlName(i, 1) = "Unbound object frame or chart"
Case acPageBreak: astrCtlName(i, 1) = "Page break"
Case acPage: astrCtlName(i, 1) = "Page"
Case acCustomControl: astrCtlName(i, 1) = "ActiveX (custom) Cotrol"
Case acToggleButton: astrCtlName(i, 1) = "Toggle Button"
Case acTabCtl: astrCtlName(i, 1) = "Tab Control"
End Select
controltype = astrCtlName(i, 1)
rTarget.AddNew
rTarget("FormName") = strTemp
rTarget("FormCaption") = Eval("forms!" & strTemp & ".caption")
rTarget("ObjectName") = frmControl.Name
rTarget("ControlTipTextValue") = frmControl.ControlTipText
rTarget("Type") = controltype
rTarget.Update
'Next i
Next frmControl
DoCmd.Close acForm, strTemp
Next
End Function
Thanks for any help
What I would like to do is loop thru all the controls on all the forms in my DB, and then write the form name, control name, control type, and hopefully the locked and enabled properties to a table.
In searching the web I have attempted to modify what I found into a function to do this, it will give me the form name and control names, but I can't get the locked and enabled properties to show.
Also I can only open the forms in design view, since I would like to do this with out having to enter any parameters to open the forms.
The hope is to use this information to better document the database and audit the security of the fields on all the forms
Attempt 1
Function test2()
Dim accobj As AccessObject
Dim ctl As Control
Dim strDoc As String
For Each accobj In CurrentProject.AllForms
strDoc = accobj.Name
DoCmd.OpenForm strDoc, acDesign
For Each ctl In Forms(strDoc).Controls
Debug.Print strDoc & vbTab & ctl.Name
Next
DoCmd.Close acForm, strDoc
Next
End Function
Attempt 2 (Not Working)
Function DisplayFormInformation()
Dim frm As Form
Dim strObject As String, aob As AccessObject, obj As Object
Dim strTemp As String, strList As String
Dim strTempForm As Variant
Dim myObject As Object
Dim k As Long
Dim frmControl As Control
Dim frmControls As Controls
Dim controltype As String
Dim intcnt As Integer
Dim i As Integer
Dim ConnectToDB As New ADODB.Connection
Set ConnectToDB = CurrentProject.Connection
Dim rTarget As New ADODB.Recordset
rTarget.Open "ObjectDocumenter", ConnectToDB, adOpenKeyset, adLockOptimistic
Set obj = CurrentProject.AllForms
On Error Resume Next
For Each aob In obj
strTemp = aob.Name
DoCmd.OpenForm strTemp, acDesign
strTempForm = "forms!" & strTemp
Set frm = strTempForm
For Each frmControl In frm.Controls
ReDim astrCtlName(0 To intcnt - 1, 0 To 1)
For i = 0 To intcnt - 1
astrCtlName(i, 0) = frm(i).Name
'Use ControlType to determine the Type of Control
Select Case frm(i).controltype
Case acLabel: astrCtlName(i, 1) = "Label"
Case acRectangle: astrCtlName(i, 1) = "Rectangle"
Case acLine: astrCtlName(i, 1) = "Line"
Case acImage: astrCtlName(i, 1) = "Image"
Case acCommandButton: astrCtlName(i, 1) = "Command Button"
Case acOptionButton: astrCtlName(i, 1) = "Option button"
Case acCheckBox: astrCtlName(i, 1) = "Check box"
Case acOptionGroup: astrCtlName(i, 1) = "Option group"
Case acBoundObjectFrame: astrCtlName(i, 1) = "Bound object frame "
Case acTextBox: astrCtlName(i, 1) = "Text Box"
Case acListBox: astrCtlName(i, 1) = "List box"
Case acComboBox: astrCtlName(i, 1) = "Combo box"
Case acSubform: astrCtlName(i, 1) = "SubForm"
Case acObjectFrame: astrCtlName(i, 1) = "Unbound object frame or chart"
Case acPageBreak: astrCtlName(i, 1) = "Page break"
Case acPage: astrCtlName(i, 1) = "Page"
Case acCustomControl: astrCtlName(i, 1) = "ActiveX (custom) Cotrol"
Case acToggleButton: astrCtlName(i, 1) = "Toggle Button"
Case acTabCtl: astrCtlName(i, 1) = "Tab Control"
End Select
controltype = astrCtlName(i, 1)
rTarget.AddNew
rTarget("FormName") = strTemp
rTarget("FormCaption") = Eval("forms!" & strTemp & ".caption")
rTarget("ObjectName") = frmControl.Name
rTarget("ControlTipTextValue") = frmControl.ControlTipText
rTarget("Type") = controltype
rTarget.Update
'Next i
Next frmControl
DoCmd.Close acForm, strTemp
Next
End Function
Thanks for any help