Microsoft Access Highlight current control back color to yellow

I have the code below that is to highlight the current control background to yellow.  It works like a charm, except when it moves between the current form and the current form subform.  If someone can figure out how to make it work when it switches from current form to current form subform I would be much appreciated.  Thank you
Public Function RowHighlight(frm As Form)
Dim lngYellow As Long, lngWhite As Long
Dim CtlName As String, CurrentForm As Form, MyForm As Form
Dim Ctl As Control
On Error GoTo Errhandler
    lngYellow = RGB(255, 255, 0)     'Set lngYellow variable for
                                     'yellow color.
    lngWhite = RGB(255, 255, 255)    'Set lngWhite variable for white
                                     'color.
If Set_Screen_ActiveSubformControl() = False Then
           CtlName = Screen.ActiveControl.Name
         Set CurrentForm = Screen.ActiveForm
         Else
            CtlName = Screen_ActiveSubformControl.Name
         Set CurrentForm = Screen_ActiveSubformControl.Form
End If
    If CtlName <> myctrname Then  ' If active
                                                    
    For Each Ctl In CurrentForm.Controls            ' control not
                                                   ' equal to
                                                    ' myctrname do
    If Ctl.Name = myctrname Then                    ' next line.
    CurrentForm(myctrname).BackColor = lngWhite
    Parent.CurrentForm(myctrname).BackColor = lngWhite     ' Set myctrname
    End If
    Next                                            ' variable to white
                                                  ' BackColor.
    Screen.ActiveControl.BackColor = lngYellow    ' Set active color
                                                  ' BackColor to
                                                  ' yellow.
    myctrname = Screen.ActiveControl.Name         ' Set myctrname
   Set MyForm = CurrentForm                          ' variable to
                                                  ' active control
                                                  ' name.
    End If
 
Exit Function
 
Errhandler:
If err = 2465 Then        ' If error is 2465 which is "Object-defined
                          ' error."
Resume Next               ' Resume running on next line after error.
ElseIf err = 2474 Then    ' If error is 2474 which is "No Control is
                          ' active."
Resume Next
ElseIf err = 438 Then    ' If error is 2474 which is "No Control is
                          ' active."
Resume Next
ElseIf err = 91 Then    ' If error is 2474 which is "No Control is
                          ' active."
Resume Next
Else
   MsgBox err & " " & Error   ' Show Error number and string of error
                              ' value.
Exit Function
End If
End Function

Open in new window

dkintaudiAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
See attached GIF
CF.GIF
0
 
dkintaudiAuthor Commented:
I forgot to add that the above code also seems to get hung up on continuous forms.  If there is a better way to highlight the current control background to yellow, please let me know.  

Thank you
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Why not use Conditional Formatting on each control ...and use the Got Focus condition of CF to change the background. No code required ... no issues moving around.

mx
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
techhealthCommented:
How does RowHighlight() get called?  By _GotFocus()?
0
 
klilleyCommented:
If your are using a version of Access that supports Conditional Fomatting then you could set all fields on a main form and subform to highlight the background to yellow while the control has the focus.
0
 
dkintaudiAuthor Commented:
I got the answer from the genius Allen Browne.  He has a great module he set up at http://allenbrowne.com/highlight.html.  It must hurt to be that bright lol.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No need to AB's solution here.  Conditional Formatting is the way to go.  No code required.

mx
0
 
techhealthCommented:
Conditional formatting is supported since ACC2000.  Using code may give you more flexibility though.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Why are we closing this Q ?

CF is the elegant why to handle this situation.

mx
0
 
dkintaudiAuthor Commented:
Seems I jumped the gun.  Allen's code only works for single forms and not continuous forms.  His code doesn't work better than the one I created after all lol.  It does look prettier though.
0
 
dkintaudiAuthor Commented:
What do you mean by CF?
0
 
dkintaudiAuthor Commented:
Nevermind, once again typed to quickly.  However, how does this work with continuous forms?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"how does this work with continuous forms? "

Same way it does on a non-continuous form ... no problem.  I do it ALL the time.

Did you see my post here http:#a23604191   and here http:#a23604310  ?

mx
0
 
dkintaudiAuthor Commented:
Okay figured it out, for continuous forms I have to use conditional formatting current row method.  
0
 
dkintaudiAuthor Commented:
Thank you, you were right Conditional Formatting is the way to handle this.  
0
 
dkintaudiAuthor Commented:
One more question.  I have a million controls here.  How do I do this quickly?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
A million is a lot.  BUT ... in form design, *Usually* ... you can select all the controls at once ... then

Format>>Conditional Formatting ...

However, sometimes ... dues to some anomalies, you may have to select a small group of controls or one at a time.

mx
0
 
dkintaudiAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.