Conditional Formatting - limitation

[mlai08]

I noted that the maximum number of conditions you can put in on a report contol is 3. Does any one know how to include more than 3 conditions to format a control in report?

I tried to search the forum and google it without avail. Is there an alternative way to change the format of a field on a report according to its value? I tried to use VBA codes but I don't see a proper report event to trigger off the codes.

Thanks

Mike

 

[pbaldy]

The appropriate event is the format event of the section containing the control to be formatted (typically detail).

 

[mlai08]

Paul, I see but there is no proper properties to set the format of the report control, e.g. change font color.

Mike

 

[pbaldy]

Sorry; you implied you had the code but not the event. The code would be:

Me.ControlName.ForeColor = vbRed

 

[gemma-the-husky]

i was able to "fudge" this in a report a report by having (unbound) boxes behind transparent actual control textboxes, and setting them to transparent, or coloured depending on the value of the control. As Paul says, explicitly in the ofrmat event, but it gives you more than 3 alternatives.

having said that it changes the background, not the font colour, but its still useful.

its the same with forms - the trouble is in continuous forms/reports there is only 1 detail row, so effectively there is only one format applied to the whole column/field

 

[pbaldy]

You can affect more than the background in a report, and it's fairly simple. You can probably change anything, but certainly fore color, back color, bold, underline, visible, etc. There's no need for the unbound box behind; you can change the actual control directly.

 

[mlai08]

Thank you guys. I got it running.

There is no default selection on the control properties for ".ForeColor", but I just hard coded it Me!ControlName.Forecolor and it works.

 

[pbaldy]

Yeah, unfortunately the options don't show up in the intellisense(sp?) dropdown, but they are available:

.BackColor
.ForeColor
.FontBold
.FontUnderline
etc

Glad you got it working.

 

[gemma-the-husky]

can you confirm this, Paul,

i thought I was unsuccesful trying to format a textbox's fg/bg in a report directly, hence i resorted to using the rectangle

do you use the onformat or onprint event?

--------
you cant set these things in a form can you, for selected rows

ie
.BackColor
.ForeColor
.FontBold
.FontUnderline

 

[pbaldy]

I've done them in the format event of the section containing the control. To make sure I wasn't speaking out-of-turn, I tested before posting earlier, and those all worked. I agree that you can't do it in a form, because it doesn't have that event. I should clarify a continuous form as you could do it on a single view form (current event).

 

[mlai08]

Hi guys,

I have another issue. Since there are numerous controls on the report which require the same conditional formatting attribute, I want to create a Sub Routine for each control to call it. However, it does not seem to work for me. I got "type mismatch" error on ForeColor.

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Call FormatRptLevel(Me!ControlName, ForeColor)
End Sub

Private Sub FormatRptLevel(ByVal CrtlName As String, CrtlPrpty As Property)

If CrtlName = 1 Then
CrtlName.CrtlPrpty = vbRed
ElseIf CrtlName = 2 Then
CrtlName.CrtlPrpty = vbBlue
ElseIf CrtlName = 3 Then
CrtlName.CrtlPrpty = vbGreen
ElseIf CrtlName = 4 Then
CrtlName.CrtlPrpty = vbBrown
Else
'do nothing
End If
End Sub

Can anyone enlighten me?

Thanks

Mike

 

[pbaldy]

I've never really tried to pass a property like that, but I'm pretty sure it would work as a string:

CrtlName.Properties(CrtlPrpty)

 

[gemma-the-husky]

i suspect both the parameters are not working right

what is me!controlname?
how does this become 1,2,3 etc in the called sub

try this instead as a basis (although ive not tried to use it)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
{still don;t know what control name is, though}
{forecolor needs to be text}
Call FormatRptLevel(Me!ControlName, "ForeColor")
End Sub

Private Sub FormatRptLevel(CrtlName As String, CrtlPrpty As string)

with screen.activeform.controls(crtlname)
.properties(crtlproperty) = vbred
end with

end sub

 

[mlai08]

i suspect both the parameters are not working right

what is me!controlname?
how does this become 1,2,3 etc in the called sub

try this instead as a basis (although ive not tried to use it)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
{still don;t know what control name is, though}
{forecolor needs to be text}
Call FormatRptLevel(Me!ControlName, "ForeColor")
End Sub

Private Sub FormatRptLevel(CrtlName As String, CrtlPrpty As string)

with screen.activeform.controls(crtlname)
.properties(crtlproperty) = vbred
end with

end sub

Hi gemma-the-husky

1,2,3,4 are the number in the combo box control in the report.

I tried your codes but got an "invalid qualifier" error.

I enclose a test file for you to check.

Thanks

Mike

 

[mlai08]

I've never really tried to pass a property like that, but I'm pretty sure it would work as a string:

CrtlName.Properties(CrtlPrpty)

I received a "invalid qualifier" error on this code.

Mike

 

[pbaldy]

You're getting an error because of the control. Try this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Call FormatRptLevel("Test", "ForeColor")
End Sub

Private Sub FormatRptLevel(ByVal CrtlName As String, CrtlPrpty As String)
  With Me(CrtlName)
    If .Value = 1 Then
      .Properties(CrtlPrpty) = vbRed
    ElseIf .Value = 2 Then
      .Properties(CrtlPrpty) = vbBlue
    ElseIf .Value = 3 Then
      .Properties(CrtlPrpty) = vbGreen
    ElseIf .Value = 4 Then
      .Properties(CrtlPrpty) = vbBrown
    Else
      'do nothing
    End If
  End With
End Sub

 

[mlai08]

Thanks Paul. It works like a charm.

You are the best!