Access subform VBA

Forms behave differently when they are used as subforms, and this can be a challenge when using VBA. Normally, when you load a form, Access adds it to the Forms collection. So if you open a form MyForm then

Forms.Count = 1

and you can refer to a control on that form using:

Forms!MyForm!MyControl

However, if that form contains a subform, MySubform, then

Forms.Count = 1

and this will fail:

Forms!MySubform!MySubformControl

So if you want to refer to a control on the subform, you need to use:

Forms!MyForm!TheSubformControl.Form!MySubformControl

Note that TheSubformControl is the name of the subform control on MyForm that contains the form MySubform, not the name of the embedded form. Nowhere do you see the actual name of the form that is being used as a subform.

Confusing? You bet. If you use the Expression Builder it can allow you build the incorrect version, so you need to make sure to drill down through the main form to the subform.

Let's say that you want to invoke code on the subform when an event occurs on the main form - for example, when you navigate to a different record on the main form, you want to set the value of a combo box on the subform & then run its AfterUpdate code. Due to the fact that the subform properties & methods are not exposed, in the Form_Current event on the main form you might use this:

Forms!MyForm!TheSubformControl.Form!MySubformCombo = DCount("*","MyTable")

Forms!MyForm!TheSubformControl.Form.MySubformCombo_AfterUpdate

But wait - that doesn't work (yet). Why? The answer is quite simple: Scope.

By default, code in standard modules are declared as Public, so they can be accessed from anywhere in the application. But code-behind in forms is declared as Private. So in the subform code, all you have to do is change this

Private Sub MySubformCombo_AfterUpdate()
'
End Sub

to this:

Public Sub MySubformCombo_AfterUpdate()
'
End Sub

...and it will work as expected.