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)?

Which version of ms-access is this? Because the versions I know use a slightly different model then the one in VBE — rene, May 06 '16 at 19:17
@rene my code needs to work *regardless of the VBE host application*, so ...all of 'em! — Mathieu Guindon, May 06 '16 at 19:18
It’s not clear what you mean by “lack” of a designer. Be it vb.net forms, FoxPro forms, Paradox forms, or in this case Access, they all work much the same and all have form designers and all have their own formats. So for the last 30 years, I don’t see how Access forms, or vb.net forms are any different? As pointed out it only about 4-5 lines of VBA code to iterate the forms.Controls collection, but you have to open the form to achieve this result. So do expand on what you mean by form designer? — Albert D. Kallal, May 08 '16 at 17:47
@AlbertD.Kallal I mean exactly what's in the code above: `VBComponent.Designer` returns a `UserForm` instance when the host is Excel, but returns `null` when the host is Access. The question is about whether there's a way to iterate the controls on an Access form without having to have the designer open - it works with MSForms in Excel, so what's the deal with Access not giving me a `Designer` object? — Mathieu Guindon, May 08 '16 at 17:52
The answer is simple. User forms in Office are not the same as Access forms. The .net interop tools are in fact creating an instance of those user forms since the office inter-op in .net provides that form object type for you. You need an instance of that object, be it a user form or in the case of Access a Access forms object. You don’t have to “open” the Access form but you most certainly have to create an instance of the Access form object to use the access form object and iterate the controls. User forms in office simple compared to Access forms and likely have not changed in 15+ years. — Albert D. Kallal, May 14 '16 at 02:50
So yes, User forms or Access forms both have a designer like all software for 30+ years, it just that .net includes a user form type, but not a Access one. So again in both cases, to use that form object you need to create an instance of that object. And despite contrary comments here, you don’t have to open the Access form in design mode and in fact you don’t even have to execute an open command, but you WILL most certainly have to create an instance of that object just like you are doing for user forms. — Albert D. Kallal, May 14 '16 at 02:52
Also, it may well be possible to set a reference to the Access object model and thus not have to create a instance of Access, but only create an instance of the Access forms object - I not tested if this is possible, but tomorrow I will time permitting attempt exactly this idea. — Albert D. Kallal, May 14 '16 at 03:12
@AlbertD.Kallal I'm writing an addin for the VBE; I do reference the PIA's but so far I only needed it to call `Application.Run` to launch VBA unit tests and report results in a docked toolwindow. Given the VBIDE API exposes a `Designer` *interface* via `VBComponent` I was naively hoping Access forms would implement the same interface and then the actual type wouldn't matter and my code would work regardless of the host. I'm trying to avoid special-casing Access and I want as little host-specific code as possible, since I extend the IDE, not Access. So, is there any way through the VBIDE API? — Mathieu Guindon, May 14 '16 at 05:15
But the designer interface you are using **IS** tied to one specific type and that is a user form type (that all of office has, including Access). So the designer var type you are using is the same for all of office and is ONE specific object type. The type does NOT change when going from power-point to Access or Excel. That designer type will no more get you text boxes and objects on a power-point slide then grabbing text boxes off of a Access form. So user forms are ONE specific object type for ALL OF office. That interface is not magic and that type is the same for all of office. — Albert D. Kallal, May 15 '16 at 13:07
You can no more use PIA’s TYPES to grab power-point objects from a slide or text boxes on an access form. The .desinger type is not relevant to power-point objects on a slide anymore then Access objects on a form. So to answer your question? You can no more use some static data type to grab power-point objects off a slide then grabbing objects off of a Access form. (the user form object will not give you this information). In all of these cases, you have to create a instance of that application, or by-pass the application and read the source object directly which as we know not practical. — Albert D. Kallal, May 15 '16 at 13:07
User forms are possible in Access but never used because they don’t edit data and are same in Word, Excel or Access. So user forms in power-point or Excel or Access are the same, but that object does not give you use of controls used in any of the office applications UNLESS you talking about a user form. So the .desinger interface does not change with each office application because you talking about an object that that is the same and common to all of office. If you add-in is to be running while Access is launched, then you can grab the active "access" form object with screen.activeForm. — Albert D. Kallal, May 15 '16 at 13:13

score 2 · Answer 1 · answered May 07 '16 at 03:05

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

No, the last resort would be reading the form's binary from the MSys tables :-/ — ThunderFrame, May 07 '16 at 03:10
On the other hand if the add-in embraces the fact that different hosts mean different things; if it's built with a plug-in architecture, you could have a host-specific library to implement various host-specific behavior, which would include the way `DeclarationType.Control` declarations are acquired, which would solve the problem. — Mathieu Guindon, May 07 '16 at 04:24
I should point out that you don't have to execute a form open command, nor do you need to open the form in design mode. You can create a instance of the forms object - the openform command in fact is a roundabout way to create an instance of the forms object. However Access applications do allow multiple instances of a given form to be in use at the same time - so you not limited to openform. — Albert D. Kallal, May 14 '16 at 03:18
I was aware that I could create multiple instances of a form, but creating an instance will trigger the form Load/Open/Close/Unload events, which I don't want to do. — ThunderFrame, May 14 '16 at 03:30

Mathieu Guindon · Answer 2 · 2016-06-01T05:07:41.153

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.

See [this](http://stackoverflow.com/a/29369770/5757159) post for details on NameMap — ThunderFrame, Jun 01 '16 at 05:12
Hey @Mat'sMug, just don't forget that the existing "Excel" code **is** valid in Access too. I've used UserForms in Access to achieve "write once, plug in anywhere" UIs. — RubberDuck, Jun 01 '16 at 13:44

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

2 Answers2