How to Set Dynamic Conditional Formating on Reports

[Mr Key]

Hi all!
I have a Report from Cross-Tab Query in which I would like to Highlight the Whole Columns conditionally (Details Sections) according to the certain prio-conditions.
The fields are: Field1, Field2, Field3, Field4, Field5...Field30.

I have tried to set the Conditional Formatting for Each Fields as on the Details Section of the ReportDesinView. On the Property Sheet of a Report, I have selected the DetailsSection and then Set the On Paint Property the following Code!

Private Sub Detail_Paint()
If Me!Field1.Value<175 Then
Me!Field1.BackColor = vbBlack
End If
If Me!Field2.Value < 175 Then
Me!Field2.BackColor = vbBlack
End If
...
End Sub
 

Entering all fieldNames and Conditions, all values <175 would be highlighted as expected!
QUESTION:
How to set this Conditional Formatting Dynamic, wont depends on FieldNames.
ie. I want to use the same code to apply to different fieldnames say FieldN0,FieldN1,FieldN2,FieldN3 ... Without explicity Entering fieldNames

[Rabbit]

You could use a for each construct to iterate through all the fields. Also, please don't double post your questions.

[Mr Key]

Ok thanks! But How can it be done?
Rabbit?

[Rabbit]

I already said how it can be done. By using "a for each construct to iterate through all the fields."

You could iterate through your controls if you wanted, but that could pull in controls you don't want. By iterating through the fields, assuming the control has the same name, then you can rest assured that it will only format the fields in the recordset.

[Mr Key]

Give me the code`s example please!
I have suffered a lot to write the code by explicitly writing down the field names.
Please!

[Rabbit]

We don't give out code here unless an effort has been shown by the poster that they attempted to code it themselves.

When you have done so, you can post the code you tried and we can work through the errors.

[Mr Key]

OK!!
I have tried this!

Private Sub Detail_Paint()
Dim MyDB As DAO.Database
Dim rstSource As DAO.Recordset
Dim inFldCtr As Integer
Set MyDB = CurrentDb()
Set rstSource = MyDB.OpenRecordset(strSource)
rstSource.MoveLast: rstSource.MoveFirst
For inFldCtr = 0 To .Fields.Count - 1
If .Fields(inFldCtr).Name.Value > 175 Then
.Fields(inFldCtr).Name.BackColor = vbBlack
End If
Next inFldCtr
End Sub
 
 

But this code couldnt work!
Please help!!!!

[Mr Key]

For Each ctlo In Me.Controls
ctlo.BackColor=VbBlack

[NeoPa]

Not only is your posted solution not even correct, it is only the result of following some already clear instructions. Awarding that a Best Answer is an abuse of the forum and is not acceptable. See below how it should look, and award the Best Answer only to post #2 where the answer you tried to implement was given.

For Each ctlO In Me.Controls
    With ctlO
        If Left(.Name) = "Field" Then _
            .BackColor = IIf(.Value < 175, vbBlack, {Original colour})
    End With
Next ctlO

I don't know what your original background colour is, but it needs to be reset any time the value isn't less than 175, otherwise they will all tend to end up as black over time and multiple records.

[Rabbit]

Sorry, I dropped the ball on this one. I didn't see the post from 2 weeks ago.

[Mr Key]

Yap! you are Correct Neopa!
What I have writen is just a brief answer of what was supposed to be.
Thanks all for your contributions