How To Change BackColor of a Box

[Robert Gillard]

(Using Access 2003) I would like to change the back colour of a box,
depending on the result of a calculation. In a previous db many years ago, I
was told I could achieve this by going into the "Details" - "On Format" with
the following Event Procedure

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Text36 = "Yes" Then
Box35.BackColor = vbGreen
ElseIf Text36 = "No" Then
Box35.BackColor = vbRed
ElseIf Text36 = "on" Then
Box35.BackColor = vbBlue
Else: Box35.Back Color = vbWhite
End If

End Sub

Whilst this worked perfectly for Acces 98, however this nolonger seems to
work with Access 2003, is there another way to achieve this please

Bob

 

[Duane Hookom]

This code should work as long as the box is not set to transparent. I
recommend a couple changes:
- rename you controls. Text36 and Box35 are not names that I would find
acceptable.
- With multiple conditions, consider using Select Case and "Me."

Me.boxStatus.BackColor = 1 'Normal
Select Case Me.txtStatus
Case "Yes"
Me.boxStatus.BackColor = vbGreen
Case "No"
Me.boxStatus.BackColor = vbRed
Case "on"
Me.boxStatus.BackColor = vbBlue
Case Else
Me.boxStatus.BackColor = vbWhite
End Select