Using ControlType Property - sorting and meaning

[bentheimmigrant]

I'm writing a sub to cycle through all the controls on all the forms in a project, and export their properties to csv.

This has gone smoother than expected (hat-tip: http://vbcity.com/forums/p/142594/609960.aspx#609960)

Now, I am going through and cycling all the properties, but each ControlType (which is an integer) has a different number of properties, so I have to write a separate line of headers for each type.

Unfortunately, the ControlType's are accessed in a seemingly random order. Now, if I could sort by ControlType, I could then only put one header line in for each type of control.

Questions:
1. Is there a way to do this that won't make my head explode?

2. A simpler question: Does anyone have a link converting ControlType to the Actual control type (e.g. 100 = label)?

Code below. (Note: The "Write #1, "End"" bits are because I need to close out a line that has a comma, and I don't want an extra line return... not sure if that's logical, but it works).

Sub Export_Control_Details()

    Dim objForm As AccessObject
    Dim objActiveForm As Form
    Dim objControl As Control
    Dim intCounter As Integer
    Dim prp As Property
    Dim ExPath As String, ExFile As String
    Dim prpHeaders As Boolean
    Dim prpVal As String
    Dim ControlType As Long
    
    ExPath = CurrentProject.Path
    If Not Right(ExPath, 0) = "\" Then
        ExPath = ExPath & "\"
    End If
    ExPath = ExPath & "Control Export\"
    If Dir(ExPath, vbDirectory) = "" Then
         MkDir (ExPath)
    End If
    
    For intCounter = 0 To Application.CodeProject.AllForms.Count - 1
        Set objForm = Application.CodeProject.AllForms.Item(intCounter)
        DoCmd.OpenForm FormName:=objForm.Name, View:=acDesign
        ExFile = ExPath & objForm.Name & ".csv"
        Open ExFile For Output As #1
        ' Reset the control type
        ControlType = 0
        Set objActiveForm = Application.Screen.ActiveForm
        For Each objControl In objActiveForm.Controls
            If ControlType <> objControl.Properties("ControlType") Then
                ControlType = objControl.Properties("ControlType")
                Write #1, vbCrLf
                Write #1, vbCrLf
                Write #1, "Control Type: " & ControlType
                Write #1, vbCrLf
                ' Reset the headers flag to get new line of headers
                prpHeaders = False
            End If
            If Not prpHeaders Then
                Write #1, "Control Name",
                For Each prp In objControl.Properties
                    Write #1, prp.Name,
                Next prp
                Write #1, "End"
            End If
            prpHeaders = True
            Write #1, objControl.Name,
            For Each prp In objControl.Properties
                On Error Resume Next
                prpVal = prp.Value
                If Err.Number <> 0 Then
                    prpVal = ""
                    Err.Clear
                End If
                Write #1, prpVal,
                
            Next prp
            Write #1, "End"
        Next
        DoCmd.Close ObjectType:=acForm, ObjectName:=objActiveForm.Name
        Close #1
    Next
    
    Set objForm = Nothing
    Set objActiveForm = Nothing
    Set objControl = Nothing

End Sub

 

[CJ_London]

Is there a way to do this that won't make my head explode?

loop through form multiple times, looking for each controltype

A simpler question: Does anyone have a link converting ControlType to the Actual control type (e.g. 100 = label)?

just google it

https://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx

 

[bentheimmigrant]

loop through form multiple times, looking for each controltype



just google it

https://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx

I had found that article already, but it's not helpful. The ControlTypes in that article have names. Access returns integers.

Seems to be part of MS's constant effort to pretend that VBA = VB.net.

I could build a case select for ControlType, but I first need to know all the integers.

 

[CJ_London]

they are names for numbers (called enums) which you can use instead (in VBA, not relevant for SQL)

in the VBA window, immediate pane type

?acCheckBox

and you will see the number

or go to the object browser, type in 'AcControlType' and they will all be listed


or just use

select case controltype
acCheckBox
...
...

which makes for much clearer code

 

[arnelgp]

or you can use the TypeName() function, ie:

dim controlType as String
controlType = "Integer"
...
...
if TypeName(objControl) <> controlType
...
...

 

[bentheimmigrant]

they are names for numbers (called enums) which you can use instead (in VBA, not relevant for SQL)

in the VBA window, immediate pane type

?acCheckBox

and you will see the number

or go to the object browser, type in 'AcControlType' and they will all be listed


or just use

select case controltype
acCheckBox
...
...

which makes for much clearer code

Thanks for this. I'll have a go and see how badly I can mangle it...

 

[The_Doc_Man]

If ever in doubt:

Open a code window.

In the icon bar find the Object Browser or use the F2 function key. Select the Access library. Now look in the "Classes" list for AcControlType. Click that and the "Members of {classobject}" will display the names. If you then select a name, you would see the value of it at the bottom of that display as "Enum {objectname}"

The same is true for all sorts of other useful class constants like AcObjectType (useful for DoCmd.Open actions), AcModuleType, AcView (useful when opening forms and reports through VBA), etc. etc.

Depending on what references you have selected from (code window) >> Tools >> References, you can browse the constants, find the names of methods and properties, and see all sorts of useful information about naming.

In the case you mentioned (control types), I don't think you can force the names to be sorted a priori but you might export them to a temporary table and sort THAT before you do your output. After all, what good is having Access's ability to create temp tables and sort things if you don't use the features?

As to WHY they are not sorted? I can take a "slightly-more-than-uneducated" guess. They are defined based on some common structures that are built up via inheritance. For instance, a text box and a label have the properties of a rectangle plus more parts - so if you look at the properties of the text box or label, you would see the rectangle's properties as a group - because they were inherited - plus the things that make the label different - and then on top of that, things that make the text box different.

We have to remember something that is incredibly important here. We are not looking at the objects with VBA. We are looking at how VBA presents the objects we are trying to manipulate. There is a difference between the two statements. That difference is called the Common Object Model interface. So if that COM interface doesn't sort for us, we are pretty much on our own.

 

[bentheimmigrant]

Temporary tables sounds like an excellent solution... I've not used them before, so it should be a good time to practice!

 

[jdraw]

I did something similar. You might find some of this useful.
http://www.accessforums.net/showthread.php?t=31409&p=154208#post154208

 

[bentheimmigrant]

Thanks jdraw. That should give me a significant head start.

Just a sidenote to any that come after... a blank entry in a property.Value will throw an error, so beware if you're calling up all properties.

 

[bentheimmigrant]

Thanks all for the help. It dawned on me that it was much easier to choose the properties I wanted (in this case, largely the OnEvents), than to make a different case for each type. Then, exporting to csv is simple, and sorting by type will be trivial. Updated code below. If you use it, just add/remove from the prps array as required.

Sub Export_Control_Details()
    Dim objForm As AccessObject
    Dim objActiveForm As Form
    Dim objControl As Control
    Dim intCounter As Integer
    Dim prp As Variant
    Dim ExPath As String, ExFile As String
    Dim prpHeaders As Boolean
    Dim prpVal As String
    Dim ControlType As Long
    Dim NumPrps As Integer
    Dim prps() As Variant
    
    ExPath = CurrentProject.Path
    If Not Right(ExPath, 0) = "\" Then
        ExPath = ExPath & "\"
    End If
    ExPath = ExPath & "Control Export\"
    If Dir(ExPath, vbDirectory) = "" Then
         MkDir (ExPath)
    End If
    
    ' List out the properties to collect
    prps = Array("Name", "ControlType", "ControlSource", "Caption", "Visible", "OnClick", "OnClickEmMacro", "BeforeUpdate", "BeforeUpdateEmMacro", "AfterUpdate", "AfterUpdateEmMacro", _
    "OnDirty", "OnDirtyEmMacro", "OnChange", "OnChangeEmMacro", "OnNotInList", "OnNotInListEmMacro", "OnGotFocus", "OnGotFocusEmMacro", "OnLostFocus", "OnLostFocusEmMacro", "OnDblClick", _
    "OnDblClickEmMacro", "OnMouseDown", "OnMouseDownEmMacro", "OnMouseUp", "OnMouseUpEmMacro", "OnMouseMove", "OnMouseMoveEmMacro", "OnKeyDown", "OnKeyDownEmMacro", "OnKeyUp", "OnKeyUpEmMacro", _
    "OnKeyPress", "OnKeyPressEmMacro", "OnEnter", "OnEnterEmMacro", "OnExit", "OnExitEmMacro", "OnUndo", "OnUndoEmMacro")
    
    For intCounter = 0 To Application.CodeProject.AllForms.Count - 1
        ' Reset the headers flag to get new line of headers
        prpHeaders = False
        
        Set objForm = Application.CodeProject.AllForms.Item(intCounter)
        DoCmd.OpenForm FormName:=objForm.Name, View:=acDesign
        ExFile = ExPath & objForm.Name & ".csv"
        Open ExFile For Output As #1
        ' Reset the control type
        ControlType = 0
        Set objActiveForm = Application.Screen.ActiveForm
        For Each objControl In objActiveForm.Controls
            If Not prpHeaders Then
                For Each prp In prps
                    Write #1, prp,
                Next prp
                Write #1, ""
            End If
            prpHeaders = True
            For Each prp In prps
                On Error Resume Next
                prpVal = objControl.Properties(prp)
                If Err.Number <> 0 Then
                    prpVal = ""
                    Err.Clear
                ElseIf prp = "ControlType" Then
                    Select Case objControl.ControlType
                        Case 119 ' acWebBrowser, Treeview, Calendar
                            prpVal = "Custom control"
                        Case acTabCtl
                            prpVal = "TabCtl"
                        Case acLabel
                            prpVal = "Label"
                        Case acTextBox
                            prpVal = "TextBox"
                        Case acComboBox
                            prpVal = "ComboBox"
                        Case acCheckBox
                            prpVal = "CheckBox"
                        Case acListBox
                            prpVal = "ListBox"
                        Case acOptionButton
                            prpVal = "OptionButton"
                        Case acToggleButton
                            prpVal = "ToggleButton"
                        Case acSubform
                            prpVal = "SubForm"
                        Case acCommandButton
                            prpVal = "CommandButton"
                        Case acObjectFrame
                            prpVal = "ObjectFrame"
                        Case acBoundObjectFrame
                            prpVal = "BoundObjectFrame"
                        Case acRectangle
                            prpVal = "Rectangle"
                        Case acLine
                            prpVal = "Line"
                        Case acImage
                            prpVal = "Image"
                        Case acPage
                            prpVal = "Page"
                        Case acPageBreak
                            prpVal = "PageBreak"
                        Case acOptionGroup
                            prpVal = "Option Group"
                    End Select
                End If
                Write #1, prpVal,
            Next prp
            Write #1, ""
        Next
        DoCmd.Close ObjectType:=acForm, ObjectName:=objActiveForm.Name
        Close #1
    Next
    
    Set objForm = Nothing
    Set objActiveForm = Nothing
    Set objControl = Nothing

End Sub