Code problem with On Format event (Changing Font Colors) (1 Viewer)

[RHubbard]

I am having problems with the following code that is in the On Format event in the detail section of a report.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
‘***************************
‘ Change font color when translator name is “L” for Lamdin
‘ ”PM” for Patterson-Meyer
‘ ”PR” for Patterson-Robinson
‘***************************

If Me.[txtTrans] = "L" Then
Me.[txtContext].ForeColor = 0 'black
ElseIf Me.[txtTrans] = "PM" Then
Me.[txtContext].ForeColor = 255 'red
ElseIf Me.txtTrans = "PR" Then
Me.txtContext.ForeColor = 16711680 'blue
Else: Me.[txtContext].ForeColor = 0
End If
End Sub

As you may surmise, the objective is to have the text in the [txtContext] field change font color (ForeColor) based on the value that is in [txtTrans].

The code works partially. When [txtTrans] = “PR” or “PM” the [txtContext] field appears correctly on the report, however the contents of [txtContext] does not appear AT ALL on the report. Moreover, when I filter the underlying query for the report to include ONLY “L” in [txtTrans] a VB error is generated.

What am I doing wrong?


Thanks,

Rick

 

[CraigDolphin]

Any chance you're encountering a null in txtTrans?

Dim trans as string

trans = Nz(Me.txtTrans,"0")
Select case trans
case "L"
Me.[txtContext].ForeColor = 0 'black
case "PM"
Me.[txtContext].ForeColor = 255 'red
case "PR"
Me.txtContext.ForeColor = 16711680 'blue
case else
Me.[txtContext].ForeColor = 0
end select