Fix for the error: "You can't hide a control that has the focus" in Microsoft Access

You cannot hide or disable an active control.  

If you try, you will get run-time error 2165, "You can't hide a control that has the focus."

The workaround is simple enough: set the focus to a different control before setting the Enabled or Visible property to False.

However, if you have to specify which other control to make active, then you can't create a general solution to the problem.

I wrote the following procedure to automatically set focus to some other control that can take the focus.  While you have the option of specifying a fallback control, the convenience of this function is that you don't have to.

The Code: DefocusIfActive()

Dependencies

The procedure uses my Throw procedure to generate a custom error.  It also relies on my TrySetFocus procedure.

' ----------------------------------------------------------------
' Purpose   : Shifts the focus away from a control so that it may be hidden or disabled.
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/defocusifactive/
' Date      : 7/6/2021 - 4/7/2023
' ----------------------------------------------------------------
Sub DefocusIfActive(Ctl As Control, Optional FallbackCtl As Control = Nothing)
    'We only want to change focus if the passed control is active
    If Not Ctl Is Screen.ActiveControl Then Exit Sub
    
    'Try to set focus to the fallback control if one is provided
    If Not FallbackCtl Is Nothing Then
        If TrySetFocus(FallbackCtl) Then
            'We were able to set focus to the fallback control, our work here is done
            Exit Sub
        End If
    End If
        
    'No fallback control specified or it couldn't take the focus,
    '   so we have to find another control that can take the focus

    'Loop through all the controls
    Dim OtherCtl As Object
    For Each OtherCtl In Ctl.Parent.Controls
        
        'Skip over the control we're trying to defocus
        If Not Ctl Is OtherCtl Then
            
            'If we can set focus to the other control, great...
            If TrySetFocus(OtherCtl) Then Exit Sub
            
            '...otherwise we keep trying
        End If
    Next OtherCtl
    
    'Consider adding a transparent command button to receive the focus:
    '   https://nolongerset.com/transparent-command-buttons/
    Throw "No suitable control to pass focus to from {0}", Ctl.Name
    
End Sub

How it works

Guard Clause

The procedure starts by checking to see if the passed control is currently active.  We don't want to switch the user's active control for no good reason.  If the passed control is not active, we exit immediately.

Explicit Fallback Control

If an explicit fallback control is passed to the function, it tries to set focus to that control.  If that works, the procedure exits.

However, setting focus to this other control might fail for any number of reasons.  If it does, the procedure continues on as if the fallback control was never passed.

Looping Through Other Controls

The function loops through all the other controls at the same level as the passed control.  It does not consider controls on a hypothetical parent form or subform (this functionality is left as an exercise for the reader, if you are so inclined).

Raising the Alarm

If there are no suitable controls to take the focus, then the procedure throws a custom error.  

If the design of the form does not lend itself to having an enabled control to accept the focus, you could always add a transparent command button and set its Tab Stop property to False.  This gives you a place to "park" the focus without adding any clutter to the user interface.


Referenced articles

The TrySetFocus Convenience Function
The TrySetFocus function attempts to set focus to a control. If the operation succeeds, it returns True; otherwise, it returns False.
4 Uses for Transparent Command Buttons in Microsoft Access
When you set a command button’s Transparent property to True, you won’t see it but you can still interact with it. This opens up a world of possibilities.
Throwing Errors in VBA
Introducing a frictionless alternative to Err.Raise.

UPDATE [2023-04-07]: Bug fix: changed ActiveControl to Screen.ActiveControl to fix compile error in DefocusIfActive code.