Microsoft Access Tips and Tricks
Transferring Focus Between a Main Form and its Subform
When using VBA code to set the focus from a main form to a control on a subform, one calls the SetFocus method. But there's a catch. You have to call SetFocus twice: once to set the focus to the subform (on the main form), and once to set the focus to the control on the subform. For example, one might write:
Interestingly, you can perform those two SetFocuses in either order: subform then control, or control then subform. It makes no difference, but you need both of them.
This seems strange, at first look. After all, you can set the focus from one control to another on the main form with only one statement. And you can set the focus from a subform to a control on its parent form with only one statement. Why does it take two to go from the main form to the subform?
The reason is that each form object has an active control (returned by the .ActiveControl property), and calling the SetFocus method changes which control is the active control *on that form object*. It's perfectly possible for a control to be the active control on a subform, but that subform doesn't have the focus on the main form, so the control that actually has the application's focus (as returned by Screen.ActiveControl) is something else entirely. In order for the control on the subform to have the application's focus, it must be the active control on the subform *and* the subform control itself must be the active control on the main form -- *and* the main form must be the active object in the Access application.
That last condition, the main form being the active object, isn't usually an issue when these questions come up. It's usually the first two conditions that matter: both the subform control (on the main form) and the control on the subform must be the active controls on their respective form objects. This can be achieved by calling the SetFocus method for both of them, but it doesn't matter in what order.
When you go the other way, and set the focus from the subform to a control on the main form using code like this:
... you don't have to call SetFocus twice, because you aren't actually changing the active control on the subform. You're just changing the active control on the parent form, which is enough to make the application's focus change from the subform control to that other control.
Versions: Access 95 to 2007
Categories: VBA, How To, Forms, Subforms
Date: 6 October, 2009