Highlighting only Rows that meet Conditions

[deeda67]

OK, I know how to make the shading highlight every other row. I know how to make just the field that the condition has to be met highlight. But how in the world do I make the whole row highlight when the condition is met.

For example, Access highlights all the PENDING (STATUS FIELD) proposals in light blue when PENDING is chosen for that record's STATUS FIELD (so, WON or LOST proposals would not be highlighted) in my report. So that the Pending proposals stand out to those who view them.

I can get it to highlight just the Status fields that are Pending but I need it to highlight the whole row.

 

[RoyVidar]

In the detail sections on format event, compare what you need, and alter the colour of the section.

if me!txtMyControl = "somevalue" then
me.section(acdetail).backcolor = vbyellow
else
me.section(acdetail).backcolor = vbwhite
endif

here's one with more conditions http://www.access-programmers.co.uk/forums/showthread.php?t=122843

 

[deeda67]

Thank you! That worked. When I did my search on this subject, that information didn't come up in my results. Thank you for the extra info, I'm sure I will need it.

 

[Jakboi]

Hello,

I need this to highlight a row in a report or a field in a report if a condition is met, is this the same thing.

For example if a field equals the current month then highlight yellow, if not then do nothing.

Does the code above do that or work for what I am trying to complete...

Thanks.

 

[RoyVidar]

Yes, it should.

If you have specific questions, feel free to post them.

 

[Jakboi]

Sorry havent messed with my database lately and everything seems foreign.

Ok. This is what I have in my Event Procedure in my details format section on my report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!txtMyControl = "somevalue" Then
Me.Section().BackColor = vbYellow
Else
Me.Section(acDetail).BackColor = vbWhite
End If
End Sub

As you have posted earlier. Now I have tried to find a condition for the "somevaule" and keeping getting invalid syntax. What would a condition be if say, If [text 35] equals current month then highlight yellow.

Also should (acDetail) be changed to something else? I didnt know if that was standard for the details section or just something that applied to the previous post.

Basically if [text 35] (which is a date field) equals the current month then highlight the row yellow.

Thanks for any insight.

 

[RoyVidar]

Replace

Me!txtMyControl = "somevalue"

with

Me![text 35] = Month(Date)

The report (and form) sections can be named by the developer, and are also subject of locale variations. Where I reside, the Detail section, is usually called Detalj. Using Me.Section(acDetail)... which uses a constant should be a safe way of referring to the specified section, regardless of locale (note, you've removed one!).

 

[Jakboi]

Thank you for the help. I have this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me![LAST UPDATE] = month(Date) Then
Me.Section(acDetail).BackColor = vbYellow
Else
Me.Section(acDetail).BackColor = vbWhite
End If
End Sub

I tried using [text 35] but it cannot find it. That box is named text 35 but the control source is LAST UPDATE. So I have changed it and when I preview the form nothing highlights...mmm.

I have attached a screen shot of my form if taht helps at all to understand my situation. Thank you for the assitance.

 

[RoyVidar]

Well, does the field you question contain the month numbers (1-12) and how many records do you have for June?

Or, was it a date field?

Month(Me![LAST UPDATE]) = month(Date)

If so, you'll probably need something to exclude previous years too?

If Month(Me![LAST UPDATE]) = month(Date) AND _
year(Me![LAST UPDATE]) = year(Date) then

 

[Jakboi]

That LAST UPDATE format is "SHORT DATE" in that fields format. There is like 12 entries for June.

I tried this:

Month(Me![LAST UPDATE]) = month(Date)

But gave me null error.

 

[RoyVidar]

So, it may also be Null, for instance nz?

If Month(nz(Me![LAST UPDATE])) = month(Date) AND _
year(nz(Me![LAST UPDATE])) = year(Date) then

 

[Jakboi]

That worked, amazing. Thank you for your assitance. Rep+ (It wont let me just yet, but I will, thanks.)

 

[Okibi07]

Hi just jumping in here to also solve my problem.
I am trying to do the same essential thing - I have a "Priority" box on my report that either says Yes/No. What I want to do is the "Detail" background to change yellow if Priority says "Yes".

What I have in my code is not working, so would appreciate some help.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Priority = "Yes" Then
Me.Detail.BackColor = vbYellow
Else
Me.Detail.BackColor = vbWhite
End If

End Sub