All controls in all forms

[Pat]

I need to loop through all controls on all forms in a database looking for ActiveX controls.
Can someone provide me the code to do this?

[HansV]

Try this:

Code: Select all

Sub FindActiveX()
  Dim o As AccessObject
  Dim f As Form
  Dim c As Control

  On Error GoTo ErrHandler

  DoCmd.Echo False
  For Each o In CurrentProject.AllForms
    DoCmd.OpenForm o.Name, acDesign
    Set f = Forms(o.Name)
    For Each c In f.Controls
      If c.ControlType = acCustomControl Then
        Debug.Print f.Name, c.Name
      End If
    Next c
    DoCmd.Close acForm, f.Name, acSaveNo
  Next o

ExitHandler:
  Set o = Nothing
  Set f = Nothing
  Set c = Nothing
  DoCmd.Echo True
  Exit Sub

ErrHandler:
  MsgBox Err.Description
  Resume ExitHandler
End Sub

This version will report the names of ActiveX controls in the Immediate window, but you can modify it to suit your needs.

[Pat]

Thanks Hans.
I had developed the following but yours is more complete:

Code: Select all

Private Sub CommandSaveSource_Click()
    Dim obj As AccessObject
    Dim ctl As control, frm As Form
    Dim s As String
    ' Search for open AccessObject objects in AllForms collection.
    For Each obj In Application.CurrentProject.AllForms
        If obj.name = "Department Availability" Then
            DoCmd.openform obj.name, acDesign
            Dim ic As Integer, iNo As Integer
            Set frm = Forms(obj.name)
            iNo = frm.count  '   no of controls on form
            For ic = 0 To iNo - 1
                If frm.Controls(ic).Properties(5).Value = "MSComCtl2.MonthView.2" Then
                    ' Print name of obj.
                    Debug.Print frm.name
                    Debug.Print frm.Controls(ic).Properties(1).Value
                    Debug.Print frm.Controls(ic).Properties(3).name
                    Debug.Print frm.Controls(ic).Properties(3).Value
                    Debug.Print frm.Controls(ic).Properties(5).Value
                End If
            Next ic
            DoCmd.close acForm, frm.name, acSaveYes
        End If
    Next obj
End Sub

[Pat]

Is there a way to get the name of the DLL or OCX for these ActiveX controls in VBA code as well as the above?

[HansV]

Not directly. The Class property of the ActiveX control returns the name with which the control is registered in the Windows Registry.

For example, the calendar control has class MSCAL.Calendar.7.
If you look this up in HKEY_CLASSES_ROOT, you'll find the CLSID {8E27C92B-1264-101C-8A2F-040224009C02}.
If you search for this CLSID, you'll find its InProcServer32 property. This is the path and filename of the .dll or .ocx, e.g. C:\Program Files\Microsoft Office\Office12\MSCAL.OCX.
Unfortunately, the location of the CLSID depends on whether you're on a 32-bit or 64-bit version of Windows, so it's not entirely trivial to do this in VBA.