vba to format textbox in report?

[Mr Smin]

Hello AP forums,

I want the contents and format of a textbox to change depending on field values.

The following is working with respect to the text, but not the formatting (BackColour).

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
If [MoveOnDate] < Now Then
Me.Text93 = [NowAt] & ", Ended " & [move_on_date]
Me.Text93.BackColor = vbRed
Else
Me.Text93 = [NowAt] & " Since " & [start_date_latest]
End If
 
End Sub

(Code is in the report's Detail event)

Please could someone explain what I'm doing wrong?

Thanks

 

[vbaInet]

Use Conditional Formatting for the backcolor.

I'm sceptical you're using Now(). Is it not a date that MoveOnDate stores? If that's the case, use Date().

 

[Mr Smin]

I tried using Date() but I got an error, so I tried Now and it worked.
It's interesting that the difference is that the parentheses are not allowed - and when I typed Now() I triggered and auto-correction. I have changed it to Date without parentheses and it works and is more appropriate, as you said.

I can get the outcome I need using conditional formatting, but I was hoping someone could advise on controlling the same things via vba for situations requiring more than 3 possible formats, and for the sake of having everything in one place.

 

[vbaInet]

Have a look at this link that contains Sample Databases provided by DCrake (one of the forum's Admin and a VIP):

http://www.xcraftlimited.co.uk/Sample-Databases.html

Scroll to the bottom and it's called CondFormat