Enumerate (List) All Controls, All Forms (1 Viewer)

JTI

New member
Local time
Today, 07:09
Joined
Mar 28, 2007
Messages
4
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:09
Joined
Feb 28, 2001
Messages
27,001
Not all controls have locked/enabled, so in some cases you are doomed. For example, TECHNICALLY a label and a line are controls. (Access is kind of dumb about things like that...). But for those controls that have the ability to take input, the properties should be visible on the control.

I noted in your code that you DO open the form. This is good because if the form isn't open, you cannot traverse its controls. And you are right to open it in design mode since a form opened in Forms mode might have an OnLoad or OnCurrent routine that does something you probably don't want done.

I notice you were doing some concatenation with the object name in a couple of places. You have to watch out for doing this in any approach, but if you have the control as a variable you can just refer to {control-var}.Enabled or any other property. The right way to do this is before you attempt to evaluate this property you should put an OnError trap around the reference to that property so that if you trap, the odds are you picked a non-existent property.

You mentioned that you could not evaluate the .Enabled and .Locked properties. Either my eyes are bad (which they probably are anyway) or my head is whacked (also a distinct possibility), but I didn't see any reference to either of .Enabled or .Locked in the code snippet you presented. Not that it matters a whole lot.
 

JTI

New member
Local time
Today, 07:09
Joined
Mar 28, 2007
Messages
4
Doc Man Thanks!

I haven't been able to get the enabled or locked to work at all because of exactly the problem you describe. A lot of the controls don't have the properties I need.

The other problem with this code, is for some reason it's not actually writing to the table. (Should have mentioned that)

I would still like to list the control even if the don't have the enabled and locked properties, so I can't figure out how to basically create an if statment to only list the property if it exists for a particular control.

Like I say this is all to help me audit form security, I currently have about 100forms, so I am trying to save myself some manual work.

Thanks again
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:09
Joined
Feb 28, 2001
Messages
27,001
There are two approaches.

First, you could just put an OnError trap around a test for a given property and if the trap trips (OnError takes the error action), skip the property.

What I once did was basically make a subroutine that returned a True or a False based on the input of a control object variable and a named property. This routine looked at the control's Properties collection and compared each name to the named property from the call. If the property existed, it came back true and the {Controlobject}.{NamedProperty} item could be used in an expression. In my case, I was doing a cross-reference of other properties than locked or enabled, but I did something not too dissimilar to what you have described. I'll say that you are decidedly on the right path even if there is a minor glitch somewhere.

Speaking of which, I don't see offhand why the recordset wouldn't update, assuming you didn't spell anything wrong, but it has been a while since I played with recordsets. Just remember to close the recordset when you are done. You should be able get a status back from the .Update operation if something failed, so I have to wonder if you are even executing the code. Have you tried doing some judicious debugging by setting breakpoints and stepping through the code to see if you ever get to the .Update?
 

JTI

New member
Local time
Today, 07:09
Joined
Mar 28, 2007
Messages
4
Doc Man As you suspected it was something silly, I misspelled some of the field names so it didn't update the recordset.

I did manage to get it working yesterday, I post the code later.

One issue I do have is I would like to list the control type as a name not as number and can't seem to get a case select to work correctly. Any suggestions would be appreciated.
 

boblarson

Smeghead
Local time
Today, 04:09
Joined
Jan 12, 2001
Messages
32,059
Make sure when you use the Select Case with a string value that you encapsulate in quotes:

Select Case Me.Name

Case "dormant"

Case "active"

etc.
 

Users who are viewing this thread

Top Bottom