How can I iterate the controls on a MS-Access form?

Question

I have a method where, given a VBComponent, I can access the .Designer and from there, the .Controls collection:

 private void DeclareControlsAsMembers(VBComponent form)
 {
     var designer = form.Designer;
     if (designer == null)
     {
         return;
     }

      // using dynamic typing here, because not only MSForms could have a Controls collection (e.g. MS-Access forms are 'document' modules).
      foreach (var control in ((dynamic)designer).Controls)
      {
          var declaration = new Declaration(_qualifiedName.QualifyMemberName(control.Name), ...);
          OnNewDeclaration(declaration);
      }
  }

The problem with this method is that, when the host is MS-Access, form.Designer is null, so the method returns early.

The dynamic cast here isn't particularly useful, it seems I could be casting to a UserForm interface and it would "just work" - at least in an Excel host.

But since MS-Access' forms don't have a designer (???), how do I go about iterating controls on a MS-Access form, given C# code that's a VBE add-in (i.e. which can only easily access whatever the VBIDE API makes available)?

Answer 1

You can't iterate the controls on an Access form unless the form is open. Opening forms, even to design mode, is expensive, as controls need to be rendered, and being Access, the bound properties are resolved to database objects. There's also the issue of sub-forms and sub-reports.

But, this VBA code will take your vbComponent, open the form (if it isn't already open, in design mode), and then return the controls collection from the vbComponent's Properties collection. If the form wasn't open at the outset, then it is closed.

This code is fairly easy to replicate for Access Reports.

Function GetControls() As Access.Controls
  Dim comp As VBIDE.VBComponent
  Dim proj As VBIDE.VBProject
  Dim props As VBIDE.Properties
  Dim bCloseFormWhenDone As Boolean
  Dim formName As String

  Set proj = Application.VBE.ActiveVBProject
  Set comp = proj.VBComponents("Form_Form1")
  On Error Resume Next
  Set props = comp.Properties
  On Error GoTo 0

  If props Is Nothing Then
    bCloseFormWhenDone = True
    'The form is not open, so open it in design mode
    formName = Mid(comp.Name, 6)
    Application.DoCmd.OpenForm formName, acDesign
  End If

  'Get the controls collection
  Set GetControls = comp.Properties("Controls").Object

  'Close the form if it wasn't already open
  If bCloseFormWhenDone Then
    Application.DoCmd.Close acForm, formName
  End If

End Function

Answer 2

Export the modules (using the lovely Application.SaveAsText undocumented functionality), and parse the content. A CommandButton would look like this:

Begin CommandButton
    OverlapFlags =85
    Left =907
    Top =793
    Width =3118
    Height =1304
    ForeColor =4210752
    Name ="Command0"
    Caption ="Command0"
    OnClick ="[Event Procedure]"
    GUID = Begin
        0x925ed6d615e7594c83313637a6d582f4
    End
    GridlineColor =10921638

    LayoutCachedLeft =907
    LayoutCachedTop =793
    LayoutCachedWidth =4025
    LayoutCachedHeight =2097
    BackColor =15123357
    BorderColor =15123357
    HoverColor =15652797
    PressedColor =11957550
    HoverForeColor =4210752
    PressedForeColor =4210752
    WebImagePaddingLeft =2
    WebImagePaddingTop =2
    WebImagePaddingRight =1
    WebImagePaddingBottom =1
End

You can retrieve the value of the Name property (and any other property you like) for all controls, and even be able to tell if OnClick is attached to an event handler procedure.