[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Microsoft Access Highlight current control back color to yellow

Posted on 2009-02-10
19
Medium Priority
?
1,118 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

0
Comment
Question by:dkintaudi
  • 9
  • 6
  • 2
  • +1
18 Comments
 

Author Comment

by:dkintaudi
ID: 23604191
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
 
LVL 75
ID: 23604227
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
 
LVL 11

Expert Comment

by:techhealth
ID: 23604231
How does RowHighlight() get called?  By _GotFocus()?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 4

Expert Comment

by:klilley
ID: 23604259
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 23604310
See attached GIF
CF.GIF
0
 

Author Comment

by:dkintaudi
ID: 23604314
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
 
LVL 75
ID: 23604326
No need to AB's solution here.  Conditional Formatting is the way to go.  No code required.

mx
0
 
LVL 11

Expert Comment

by:techhealth
ID: 23604333
Conditional formatting is supported since ACC2000.  Using code may give you more flexibility though.
0
 
LVL 75
ID: 23604376
Why are we closing this Q ?

CF is the elegant why to handle this situation.

mx
0
 

Author Comment

by:dkintaudi
ID: 23604383
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
 

Author Comment

by:dkintaudi
ID: 23604396
What do you mean by CF?
0
 

Author Comment

by:dkintaudi
ID: 23604416
Nevermind, once again typed to quickly.  However, how does this work with continuous forms?
0
 
LVL 75
ID: 23604439
"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
 

Author Comment

by:dkintaudi
ID: 23604523
Okay figured it out, for continuous forms I have to use conditional formatting current row method.  
0
 

Author Closing Comment

by:dkintaudi
ID: 31545219
Thank you, you were right Conditional Formatting is the way to handle this.  
0
 

Author Comment

by:dkintaudi
ID: 23604617
One more question.  I have a million controls here.  How do I do this quickly?
0
 
LVL 75
ID: 23604664
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
 

Author Comment

by:dkintaudi
ID: 23605036
Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question