troubleshooting Question

Change backcolor on control that has focus

Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America asked on
Microsoft AccessVBA
17 Comments5 Solutions153 ViewsLast Modified:
I currently have form that I use to enter new records that use some vba code that will highlight the field that the user is entering data into. This is similar to using the conditional formatting where the backcolor changes when a field has focus. The only reason why I use vba code instead of the conditional formatting is because I want the backcolor to only change when the user is updating data and not whenever someone just clicks on a control on the form. I have the controls locked down so the only way a user can update the data is by clicking the cmdModify button which unlocks the controls and allows dataentry. It also uses the vba code below to also highlight the field that currently has focus. Once they save their changes the backcolor highlighting will stop. Here's the problem I am running into. I now have a new form with a subform and I cannot get this code to work on the subform. It will only work on the main form. I need to figure out how to modify the vba code to also allow the controls on the subform to be highlighted when a control has focus. Below is the vba code that I use. I fire the code in the cmdModify button to turn it on and then I fire the code again in the cmdSave button to shut it off.

This code is stored in a module called "basChangeFocusColor". I call it like this in the two command buttons:

cmdModify

InitialiseEvents Me

cmdSave

InitialiseEventsOff Me

Is there an easy way to modify the code to also allow it to work on subforms? If so, how and how would I call it on my subform to turn it on and off when the cmdModify & cmdSave buttons are clicked on my main form?

Public Sub InitialiseEvents(ByRef frmThisForm As Form)
  ' Comments: Creates Events for form controls
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    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 Sub InitialiseEventsOff(ByRef frmThisForm As Form)
  ' Comments: Turns off events for form controls
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    Dim ctl As Control
    
    On Error Resume Next

    For Each ctl In frmThisForm
        ctl.OnGotFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
        ctl.OnLostFocus = "=HandleFocusOff('" & 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)
  ' Comments: Creates backcolor and font color for Got Focus Events
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    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 = vbBlack
                lngFontWeight = .FontWeight
                lngBorderStyle = .BorderStyle
                lngBorderColour = .BorderColor
                lngBackStyle = 1
                lngBackColour = vbWhite
                
                ' Set required configuration.
                .ForeColor = vbBlue
                .FontWeight = 700
                .BorderStyle = 1
                .BorderColor = vbRed
                .BackStyle = 1
                .BackColor = vbYellow
            
            Case "Lost"
                ' Restore saved configuration.
                .ForeColor = vbBlack
                .FontWeight = lngFontWeight
                .BorderStyle = lngBorderStyle
                .BorderColor = lngBorderColour
                .BackStyle = 1
                .BackColor = vbWhite
        End Select
    End With
    
    Err.Clear
End Function

Public Function HandleFocusOff(ByVal strFormName As String, _
                            ByVal strControlName As String, _
                            ByVal strChange As String)
  ' Comments: Creates backcolor and font color for Lost Focus Events
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    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 = vbBlack
                lngFontWeight = .FontWeight
                lngBorderStyle = .BorderStyle
                lngBorderColour = .BorderColor
                lngBackStyle = 1
                lngBackColour = vbWhite
            
            Case "Lost"
                ' Restore saved configuration.
                .ForeColor = vbBlack
                .FontWeight = lngFontWeight
                .BorderStyle = lngBorderStyle
                .BorderColor = lngBorderColour
                .BackStyle = 1
                .BackColor = vbWhite
        End Select
    End With
    
    Err.Clear
End Function
ASKER CERTIFIED SOLUTION
Lawrence Salvucci
Director of Information Technology

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 5 Answers and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros