We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Microsoft Access Highlight current control back color to yellow

dkintaudi
dkintaudi asked
on
Medium Priority
1,221 Views
Last Modified: 2013-11-28
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

Comment
Watch Question

Author

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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
techhealthDirector
CERTIFIED EXPERT

Commented:
How does RowHighlight() get called?  By _GotFocus()?

Commented:
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.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
See attached GIF
CF.GIF

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
No need to AB's solution here.  Conditional Formatting is the way to go.  No code required.

mx
techhealthDirector
CERTIFIED EXPERT

Commented:
Conditional formatting is supported since ACC2000.  Using code may give you more flexibility though.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Why are we closing this Q ?

CF is the elegant why to handle this situation.

mx

Author

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.

Author

Commented:
What do you mean by CF?

Author

Commented:
Nevermind, once again typed to quickly.  However, how does this work with continuous forms?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"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

Author

Commented:
Okay figured it out, for continuous forms I have to use conditional formatting current row method.  

Author

Commented:
Thank you, you were right Conditional Formatting is the way to handle this.  

Author

Commented:
One more question.  I have a million controls here.  How do I do this quickly?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
Thank you.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.