Highlighting focused controls in subform

[Zie]

Hello,

I'm using ChrisO's module (https://access-programmers.co.uk/forums/showpost.php?p=915277&postcount=10) to highlight controls on focus event in forms, however I can't make it work with subforms. I get a Run-time error "2450" : Application cannot find form.

Option Explicit
Option Compare Text

Public Sub InitialiseEvents(ByRef frmThisForm As Form)
    Dim ctl As Control
    
    On Error Resume Next

    For Each ctl In frmThisForm
        ctl.OnGotFocus = "=HandleFocus('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
        ctl.OnLostFocus = "=HandleFocus('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Lost')"
    Next ctl
    
    Err.Clear

End Sub


Public Function HandleFocus(ByVal strFormName As String, _
                            ByVal strControlName As String, _
                            ByVal strChange As String)

    Static lngForeColour   As Long
    Static lngFontWeight   As Long
    Static lngBorderStyle  As Long
    Static lngBorderColour As Long
    Static lngBackStyle    As Long
    Static lngBackColour   As Long
    
    On Error Resume Next

    With Forms(strFormName)(strControlName)
        Select Case strChange
            Case "Got"
                ' Save current configuration.
                lngForeColour = .ForeColor
                lngFontWeight = .FontWeight
                lngBorderStyle = .BorderStyle
                lngBorderColour = .BorderColor
                lngBackStyle = .BackStyle
                lngBackColour = .BackColor
                
                ' Set required configuration.
                .ForeColor = vbBlue
                .FontWeight = 700
                .BorderStyle = 1
                .BorderColor = vbRed
                .BackStyle = 1
                .BackColor = vbYellow
            
            Case "Lost"
                ' Restore saved configuration.
                .ForeColor = lngForeColour
                .FontWeight = lngFontWeight
                .BorderStyle = lngBorderStyle
                .BorderColor = lngBorderColour
                .BackStyle = lngBackStyle
                .BackColor = lngBackColour
                
        End Select
    End With
    
    Err.Clear

End Function

I tried several changes, like :

=HandleFocus('" & frmThisForm.Parent.Name & "', '" & frmThisForm.Name & "', '" & ctl.Name & "', '" & "', 'Got')"

With :

Public Function HandleFocus(ByVal strParentFormName As String, _
                            ByVal strFormName As String, _
                            ByVal strControlName As String, _
                            ByVal strChange As String)

And :

With Forms(strParentFormName)(strFormName)(strControlName)

I tried several expressions in the With statement, using bangs or ".Form" after the strFormName, but nothing seems to work and I'm running out of ideas. I think I'm failing to get the right combination of changes, or maybe I'm just missing the obvious.

It was suggested here (https://access-programmers.co.uk/forums/showthread.php?t=275143) to call the function in the subform but that's already the case, unless I'm doing it wrong.
Solutions have been found here (https://access-programmers.co.uk/forums/showthread.php?t=275003) but the link is dead, and here (https://experts-exchange.com/questions/29069325/Change-backcolor-on-control-that-has-focus.html) but they weren't shared.

I attached ChrisO's database demo where I simply put frmTest2 as a subform inside frmTest1 without touching the code.

Can someone help me please ? Many thanks !

 

[RuralGuy]

I have not done any testing but SubForms are not listed in the Forms collection so you may need to add the .FORM! qualifier.
http://theaccessweb.com/forms/frm0031.htm

 

[The_Doc_Man]

What I did for sub-forms was to remember that they are forms, too. So rather than have the parent form stew over this problem, I just made the routines generic and let the sub-form's local event code call the general routine as well as letting the parent form's event code calling the same general routine. To the sub-form, the controls are local. However, what I did was passed the control as an Access.Control object by Reference. So no matter what called the routine, it would pass the control reference (and I also passed in the "Me" reference as an object of type Access.Form).

Trying to gyrate from form to sub-form and trying to figure out who has focus can be a big headache. Letting Access fire whatever it needs to file as a local even? No problemns.

 

[MarkK]

I would do that using conditional formatting.

 

[MarkK]

Here's code that sets the conditional formatting for all the textboxes and combo boxes on a form.

Sub SetHasFocus(frm As Access.Form, Optional ForeColor As Long = 0, Optional BackColor As Long = 62207)
[COLOR="Green"]'   Sets all textboxes and combos on the given form to show yellow backcolor
'   when field has focus[/COLOR]
    Dim ctrl As Access.Control
    
    For Each ctrl In frm.Controls
        Select Case ctrl.ControlType
            Case acTextBox, acComboBox
                ctrl.FormatConditions.Delete
                With ctrl.FormatConditions.Add(acFieldHasFocus)
                    .ForeColor = ForeColor
                    .BackColor = BackColor
                    .FontBold = ctrl.FontBold[COLOR="green"]   'this is just propagates the existing state[/COLOR]
[COLOR="Green"]                    'so controls with bold font are still bold when focussed[/COLOR]
                End With
            Case Else
[COLOR="green"]                'ignore
[/COLOR]        End Select
    Next
End Sub

WARNING: this code deletes existing ConditionalFormatting for said controls. You can just call this code from any form in it's Open or Load event handlers, and you can modify it to suit, but I think it's simpler to do it this way.
hth
Mark

 

[Zie]

I have not done any testing but SubForms are not listed in the Forms collection so you may need to add the .FORM! qualifier.
theaccessweb.com/forms/frm0031.htm

Yes ! That's the page I've been using through my tests. But I only got every errors under the sun, I just can't get it right ? It's probably very easy, I just can't ='(.

However, what I did was passed the control as an Access.Control object by Reference. So no matter what called the routine, it would pass the control reference (and I also passed in the "Me" reference as an object of type Access.Form).

That's very interesting, could you tell me more about it, in details, please ? I'd rather you explain it to me like I'm 5, that'd be great, thanks !

I would do that using conditional formatting.

I wish I could ! I posted ChrisO's code to keep it simple, but I actually changed it to highlight labels as well as textboxes' borders. And conditional formatting is useless in both cases, which is terribly frustrating. Such a great feature, so few options ! Thanks for your code anyway, it could be useful !

 

[JHB]

It can be done with some modification, one of the pitfall in it is, that when the focus leave the subform, the On Lost Focus event for a control (in the subform), isn't trigged before the focus return to the subform.

 

[The_Doc_Man]

That's very interesting, could you tell me more about it, in details, please ? I'd rather you explain it to me like I'm 5, that'd be great, thanks !

Okey-dokey.

Look at this example. See if it makes sense to you. Remember this is just ONE example.

The theory is that you are preparing to call this to make your controls always look similar if similar things have happened to them. You might use this routine in several ways.

<somewhere in your setup code in a general module>

Enum MyCtrlState
   csNormal
   csVanished
   csDisabled
   csDirty
   <as many other distinct states as one might wish>
End Enum

<later on in another part of the module>

Public Sub SetCtrlState( TheCtl as Access.Control, TheState as MyCtrlState )

'    TheState is encoded to define what state I want the control to show, see the Enum
'    My comments will define the states I wanted

Dim lSectClr as Long
Dim objPrnt as Object

    On Error Resume Next

    objPrnt = TheCtl.Parent          'look for the object holding this control (section, usually)
    lSectClr = objPrnt.BackColor     'the color of that which is in the background

    Select Case TheState
        Case csNormal                                'normal and not selected and not dirty
             TheCtrl.ForeColor = vbBlack
             TheCtrl.BackColor = vbWhite
             TheCtrl.BorderColor = vbBlack
             TheCtrl.Visible = True
             TheCtrl.Enabled = True
             TheCtrl.TabStop = True
        Case csVanished                                'control not operational, not visible
             TheCtrl.ForeColor = lSectClr
             TheCtrl.BackColor = lSectClr
             TheCtrl.BorderColor = lSectClr
             TheCtrl.Visible = False
             TheCtrl.Enabled = False
             TheCtrl.TabStop = False
        Case csDisabled                            'control visible but not usable
             TheCtrl.ForeColor = vbBlack
             TheCtrl.BackColor = vbWhite
             TheCtrl.BorderColor = vbBlack
             TheCtrl.Visible = True
             TheCtrl.Enabled = False
             TheCtrl.TabStop = False
        Case csDirty                            'control enabled and dirty
             TheCtrl.ForeColor = vbRed
             TheCtrl.BackColor = vbYellow
             TheCtrl.BorderColor = vbRed
             TheCtrl.Visible = True
             TheCtrl.Enabled = True
             TheCtrl.TabStop = True
          <etc. etc. etc.>
    End Select

End Sub

You can pick your own colors, there are several articles on this forum that can be found via the Search feature to tell you about custom colors. There are a couple of cases of "gotcha" here, though. You CANNOT call this routine on control X from the X.GotFocus routine because it will alter the .Enabled flag if you try to vanish or lock down X. So when using the lockdown or vanish options, you would call this from the control is causing OTHER controls to change states. But you can certainly call this on X from X.GotFocus if you are only using the "Normal" and "Dirty" cases (or other changes that don't dink around with .Enabled.)

Call it like this:

    SetCtrlState( Me.controlname, cs<statename> )

This is NOT the only way to do this, but it is very close to what I used. That bit about the .Parent is because a control never occurs in isolation. It has a context, usually a form's .Section (usually the "Detail" section). Finding the section as an object lets you see the properties of a section.

You might also then realize that I am making the control visible two different ways at once. Once with the .Visible property, once with the trick of making the control's parts take on the same color as the background. When you use that feature it is overkill, but I wanted to show you the trick so you can if you wish define ANOTHER control that is invisible due to fading into the background, but is still enabled and thus acts like an invisible "hot spot" that you can use when debugging.

The bit about the .TabStop is because if a control is going to be disabled, you should also make it more difficult to reach. So using the TAB key to move from control to control will skip any potential stopping points if their .TabStop property is False.