Help with BackColor

Topic

New Reply

I am using the following code in the Detail section for the “On Print” event of a report. I would like to be able to print the backcolor of a field in different colors dependent on the field value. It’s not working….what am I doing wrong?

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim lngWhite As Long
Dim lngYellow As Long
Dim lngPink As Long
Dim lngRed As Long
Dim lngGreen As Long
Dim lngBlue As Long
Dim lngLtGreen As Long

lngWhite = RGB(255, 255, 255)
lngYellow = RGB(255, 255, 128)
lngPink = RGB(255, 164, 209)
lngRed = RGB(255, 0, 0)
lngGreen = RGB(0, 128, 64)
lngLtGreen = RGB(179, 255, 217)
lngBlue = RGB(0, 128, 192)

If Me.DocStatus = Draft Then
Me.DocStatus.BackColor = lngYellow
ElseIf Me.DocStatus = “Out For Review” Then
Me.DocStatus.BackColor = lngPink
ElseIf Me.DocStatus = Obsolete Then
Me.DocStatus.BackColor = lngRed
ElseIf Me.DocStatus = Issued Then
Me.DocStatus.BackColor = lngGreen
ElseIf Me.DocStatus = “In For Format” Then
Me.DocStatus.BackColor = lngLtGreen
ElseIf Me.DocStatus = “Issued w/ALAC Approval” Then
Me.DocStatus.BackColor = lngBlue
End If

End Sub

Reply | Quote

Replies

One thing I notice is that you have quotes around some values in your if…then statements but not around others. is the value in docstatus text?

Reply | Quote

Yes, the values in DocStatus are text.

Reply | Quote

Tried this as well….still doesn’t work. Any other suggestions?

Reply | Quote

When you say “it doesn’t work” what results are you getting exactly?

Reply | Quote

BackColor appears as either white or transparent.

Reply | Quote

Only other thing I can think of is what Rory suggested – the back style….my experiments with your code are working.

Reply | Quote

Haven’t played with colours in reports and where to set them is not mentioned in the documentation, but since other formatting (e.g. setting visible/invisible) is supposed to be done in the format event code rather than the print event code, I’d try putting your code there.

Also, I suspect you are missing a bunch of double-quotes, unless draft, obsolete, etc. are variables.

Reply | Quote

Fixed the quote issue and also tried the On Format event without success. Thanks!

Reply | Quote

Hi,
In addition to Paul’s point about quotes, you also have to have the BackStyle of the control set to normal (value of 1)rather than transparent (value of 0)
For example,
Me!DocStatus.BackStyle = 1
Hope that helps.

Reply | Quote

Hi,
It might be worth trying this variation on your code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim lngWhite As Long
Dim lngYellow As Long
Dim lngPink As Long
Dim lngRed As Long
Dim lngGreen As Long
Dim lngBlue As Long
Dim lngLtGreen As Long
Dim lngColour as Long

lngWhite = RGB(255, 255, 255)
lngYellow = RGB(255, 255, 128)
lngPink = RGB(255, 164, 209)
lngRed = RGB(255, 0, 0)
lngGreen = RGB(0, 128, 64)
lngLtGreen = RGB(179, 255, 217)
lngBlue = RGB(0, 128, 192)

Select Case Me!DocStatus
Case “Draft”
lngColour = lngYellow
Case “Out For Review”
lngColour = lngPink
Case “Obsolete”
lngColour = lngRed
Case “Issued”
lngColour = lngGreen
Case “In For Format”
lngColour = lngLtGreen
Case “Issued w/ALAC Approval”
lngColour = lngBlue
Case Else
lngColour = lngWhite
End Select
With Me!DocStatus
.BackStyle = 1
.BackColor = lngColour
End With

End Sub
Hope that works.

Reply | Quote

Unfortunately this doesn’t work either. I am able to have the entire background of the Detail section change color based on the DocStatus value, however, cannot quite figure out how to change the DocStatus field’s backcolor property…this has been very frustrating.

Reply | Quote

Is the database small enough to post here? If not, would you like to email me a copy at rarchi5404@aol.com and I’ll take a look at it, since I’m running out of ideas here! I’ll post any answers here if I find them.

Reply | Quote

What is the name of the control on your report? If it is the same as the underlying field then try changing the name of the control to some thing different and then change your code accordingly.

Reply | Quote

Problem solved….thanks for everyone’s help. As it turns out my code was correct however, for some reason the problem was related to the SQL statement referenced as the data source….doesn’t make sense I know but I am referencing the table now as the datasource and it works great!

Reply | Quote

Glad to hear you got it all sorted out!

Reply | Quote