Coloring fields if they meet a condition

[Carn]

Hi all.

I already tried most suggestions regarding coloring a textfield on a report if a certain condition is met. I`ll give a sample of the code that i am currently using. the field in question is in a subform on the form. I placed this code on the form's on open event.

If Me.PH = "2" Then
Me.PHtext.BackColor = vbRed
End If

This is just a single part. If the value of PH is 2, then the backgroundcolor of the PHtext field turns to red. I cant see a reason why it isnt working..I pretty much tried everything but i dont get it.

Thanks in advance!

 

[ColinEssex]

If the 2 is a number (not text) don't use quotes.

Col

 

[Carn]

If the 2 is a number (not text) don't use quotes.

Col

I tried that, but to no avail.

 

[ColinEssex]

Ok, reading through your thread again, you mention reports but then say you put the code in the form what field is it you want to change? the one on the form?

Col

 

[Carn]

sorry for the confusion.

I have a report, which consists of a few subforms. I dont know if this is the best way to make a report, but the report is based on a form on which I used to "convert to report" function on, and it seems to work pretty fine as a report. The field I want to change is on a subform on the report.

 

[ColinEssex]

If you want to change a SubForm field then you'll need to reference it

Forms!FormName!subFormName.form!ControlName

Col

 

[Carn]

I just tried this:

If Reports!SafetySheetReport![Query4 subform].Form!PH = 2 Then
Me.PHtext.BackColor = vbRed
End If


but again, i dont understand why it isnt working :S

 

[ColinEssex]

Post the Db and we'll have a look

Col

 

[Carn]

Im afraid I cant post it, its pretty big and company property etc.

I`ll try to set a context though:

the database is used to register different chemicals that are used in the company. For each of these chemicals, there is a need for a safetysheet. On that sheet there are pictures describing the actions that need to be taken if a worker gets in contact with the product.

They asked me to give certain fields a different color if, for example, the PH value of the chemical is 2, or any other value.

The sheet gets dynamically generated. The user can select the product out of a list, and a bunch of queries collect the neccesary date for the report.

Anyway, I placed the code on in the subform's on open event.

 

[ColinEssex]

To be honest, I'm a bit confused.

I'm not totally familiar with making a form a report then referencing a subform on said report.
Presumably you've tried the code I posted earlier in the real forms OnOpen event then seeing if a) it changes the colour and b) that is reflected in the report.

Col

 

[Carn]

Presumably you've tried the code I posted earlier in the real forms OnOpen event then seeing if a) it changes the colour and b) that is reflected in the report.

Yes, that is what I tried but it doesnt seem to work. I made a testfield on the report (not in a subform on the report), and tried some code behind that as well but it doesnt seem to work. Im lost with this

 

[trucktime]

Color

You may want to check this link:

http://www.microsoft-accesssolutions.co.uk/conditional_report_formatting.htm

 

[daveUK]

Carn,

I've done a similar thing on a report, I'll post the code and you should be able to apply it to your sub-report.

You need to set the Format property of each of the textboxes that you want to change color on the report to 'Fixed'. Then type in the following code:

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

Dim Txt As TextBox

For Each Txt In Detail.Controls

If Txt.Format = "Fixed" Then
If Txt.Value = 2 Then Txt.BackColor = VBRed
If Txt.Value = 3 Then Txt.BackColor = VBGreen
......etc.
End If

Next Txt

HTH