Solved

Highlight row in Access 2007

Posted on 2012-04-10
21
548 Views
Last Modified: 2012-04-10
I am trying to highlight a row in Access 2007. I have a checkbox in the row, if the checkbox is checked, then I want the row highlighted. I saw a post a few months back by Capricorn1 that clearly shows how to do this using conditional formatting. But for some reason it isn't working. Here is how my report and conditional formatting is setup.
Access Screenshot
I was reading Microsoft's help file on conditional formatting and found the following:

4.On the All tab of the property sheet, if the first two properties (Name and Control Source) are identical, or if the Name property matches another field name in the form's or report's underlying record source, edit the Name property so that it is unique. A common practice is to add a short prefix to the name. For example, if the control is a text box, you might add the prefix "txt" to its Name property, as in "txtQuantity."

This seems strange to me, by default the name and control are the same on all my fields. SO I added a space between words to make the name and control different. So the control for the checkbox field is OnHold and the name is On Hold. But it still doesn't work. And now one of my fields "TotalHours" now opens a "Enter Parameter Value" window, even though I didn't change the control only the name, the control is Total Hours and the name is TotalHours. I hit enter, leaving the parameter blank and the report runs and the field populates, but I obviously do not want this window opening every time.

I have the checkbox formatted yes/no, though I have also tried in the conditional formatting [OnHold]=1, [OnHold]=ON, [OnHold]="Yes", none of these have worked. If I only click on the OnHold control and change the condition to Field Value is equal to Yes, then it does highlight that one field. But I cannot get the entire row to highlight, or get anything to highlight by using "Expression is" as a condition.

Any help would be greatly appreciated.
0
Comment
Question by:CJSilver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 5
  • +2
21 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37828722
A common solution is that the "BackStyle" property of the control must be set to Normal, for the Condition formatting to work...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37828748
see this simple attached sample...

If you highlight (select) both the LastName and Title Controls, (in design view) you will see that they both have the same Conditional Formatting, yet the Backstyle is set to Normal for  Last Name and "Transparent for Title...

;-)

JeffCoachman
Database114.mdb
0
 
LVL 1

Author Comment

by:CJSilver
ID: 37828784
Jeff,

I checked all of the controls in the detail section and the back style is normal for all of them.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37828818
In a report, a method that I find is quite useful for doing this is to use the Detail sections Format event.  Something like:

Private Sub Detail_Format

    me.detail.backcolor = iif(me.chkFieldName, rgb(255,0,0), rgb(0, 255, 0)

End Sub
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37828878
Upload a working model showing the issue.
Tell what to do and expect what.
0
 
LVL 1

Author Comment

by:CJSilver
ID: 37829090
Dale,

I want to highlight the row when the OnHold field = Yes. How do I show this in your code?

hnasr,

I cannot upload my data and do not have time to create new tables that have bogus data.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37829119
The following example would highlight those records where the value of the [OnHold] field <> 0.  I use that syntax, because various databases use different values for Yes (some use 1, some use -1, some use any value other than zero).

Private Sub Detail_Format

    me.detail.backcolor = iif(me.[OnHold] <> 0, rgb(255,0,0), rgb(0, 0, 0)

End Sub
0
 
LVL 1

Author Comment

by:CJSilver
ID: 37829162
Dale,

I put in the code as you show except you are missing a parenthesis. I have:

Private Sub Detail_Format()

Me.Detail.BackColor = IIf(Me.[OnHold] <> 0, RGB(255, 0, 0), RGB(0, 0, 0))

End Sub


But it is still not working, I get no change in color.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829167
CJSilver ,

Again, the term "highlight the row" is a bit vague.
What you appear to be trying to do is highlight the *controls* in the row, ...not the row itself.

Enter fyed's post...
I do this too instead.
This way, there is only ONE object to format.
The draw back might be that this will not work if the report is opened in "Report View"
...but this is the way I would go, ...I am sure Dale can get you going on this technique...
;-)

Jeff
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 37829209
try Dales code like this perhaps:

If me.OnHold=True then
    me.section(0).backcolor=vbYellow
elseIf me.OnHold=False then
    me.section(0).backcolor=vbwhite
end if
0
 
LVL 1

Author Comment

by:CJSilver
ID: 37829218
Jeff,

You mentioned that it will not work in Report View? I need to be able to see the highlight on the screen. What view can I see it?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829225
Yeah I tried to CF multiple controls and I had trouble as well...
I saw this:
http://www.utteraccess.com/forum/Conditional-Formatting-t1546463.html

Funny but I could do two controls, but 3 gave me trouble...
Curios to see if other experts can confirm (Access 2007)
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37829227
Good catch Jeff.  I never use Report view, I always use PrintPreview.
0
 
LVL 1

Author Comment

by:CJSilver
ID: 37829249
Jeff,

I get the following error when I run your code:

Formatting Error
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37829258
You might find some of my Access Archon articles dealing with conditional formatting helpful here:

http://www.helenfeddema.com/Files/accarch118.zip
http://www.helenfeddema.com/Files/accarch173.zip
http://www.helenfeddema.com/Files/accarch201.zip
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 37829279
CJ,  If you compile the code, which line does it error on?

Try it as:

If me.OnHold=True then
    me.Detail.backcolor=vbYellow
elseIf me.OnHold=False then
    me.Detail.backcolor=vbwhite
end if
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829526
CJSilver,

Remember to put our code on the Detail Format event....
0
 
LVL 1

Author Closing Comment

by:CJSilver
ID: 37829611
I have it working now. It erred on

Private Sub Detail_Format()

I deleted it and let Access recreate it as:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

and now it works, I needed to change the controls to transparent also. The only issue is I can't see it in Report view, as Jeff mentioned above. But I can deal with that. Thank you both, I am going to split the points between you.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829618
<Good catch Jeff.  I never use Report view, I always use PrintPreview.>
;-)
Me too...

Don't get me started...

Why is Report View the default view?
Why can't you Globally change this to make Print Preview the default for all New Reports?

I fully understand the "Benefits" of Report View:
Interaction (Click Button, Follow Hyperlinks...)
Quick Viewing
Filtering
RefreshAll
Find
...etc

But, ...the drawbacks...
Poor graphics
No Page Breaks
No accurate Page Numbers
No Format Code will run
No quick Access to the Page setup Options...
...et al

I mean, you know how I love my report automation...
;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37829795
Thanks but my code was just a simple mod of Dale's code/suggestion.
(besides, it did not work anyway...)
:-(

Pretty simple, no real effort taken on my part...

If you want to request that all the points be given to Dale, I have no problem with that...

;-)

Jeff
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37829887
don't sweat it, CJ.  No need to reallocate points.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delete table fields 3 43
Conditional Format if Between Dates 9 38
Create a varying recordset 5 46
Using a combo box to search a form. 3 24
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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