Conditional Formatting via VBA MS Access Report not working

Question

I am having a trouble while trying to conditionally format the exhibition of records in a report inside a MS Access 2007 form.

I have search the Internet and I have seen lots os fellows stating that it is possible to perform visual changes in a single record via code implementing the method Detail_Paint() for the event Paint of the Detail section in a Report. Those people say that something like this is going to work:

Private Sub Detail_Paint()
    val = CStr(Me.someTextBox.Value)
    If val = "constraint" Then
        Me.lineStrikethrough.BorderStyle = 0
    End If
End Sub

The problem is that although the reading statement Me.someTextBox.Value returns the value of each record when the Paint event is thrown, the writing statement Me.lineStrikethrough.BorderStyle = 0 writes the value of the property BorderStyle for every single line in my report, not only for the one respecting the single record whose value I read from someTextBox field.

Can anyone tell me why such is happening? If this is the correct behaviour (although it does not seem right to me), how can I achieve my goal?

Note: lineStrikethrough is used to perform a strikethrough effect over the record in a Report. If there is another way to do that I would be happy to know.

Answer 1

Two things:

1 - Use the Detail's On Print event and not On Paint event.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    val = CStr(Me.someTextBox.Value)
    If val = "constraint" Then
        Me.lineStrikethrough.BorderStyle = 0
    End If
End Sub

2 - To see the conditional formatting, always open your report in Print Preview and not Report View.

Answer 2

You are really close!! :-) In my testing, it appears that the Detail_Paint() event allows you to change the formatting of controls, but changes will continue in subsequent records until you change/reset them to something else.

In your code sample, you just need to add another line to turn the strikethrough BorderStyle back on when the condition is no longer met.

Private Sub Detail_Paint()
    Val = CStr(Me.someTextBox.Value)
    If Val = "constraint" Then
        Me.lineStrikethrough.BorderStyle = 0
    Else
        Me.lineStrikethrough.BorderStyle = 1
    End If
End Sub

I found this clue on a code sample on MSDN which demonstrates conditional formatting using the Detail_Paint() method.

Answer 3

I found that Select Case to work best in the detail paint over if statements for a continuous form.

'Private Sub Detail_Paint()

Select Case CStr(Me.someTextBox.Value)

    Case "constraint"
        Me.lineStrikethrough.BorderStyle = 0

    Case Else
        Me.lineStrikethrough.BorderStyle = 1 'or what the default value is

End Select