Display a ColorCoded Field on a Continuous Form

[GLepage]

I wanted to post this in case anyone needs a similar solution.

I have a form with a sub-Form that is a "continuous form" type. On the
subform I wanted to display a Green, Yellow or Red warning depending
on the value entered for a "Severity" field. Changing the field
BackColor property does not work beacue it changes the BackColor for
all fields on the subForm not just the individual record.

My solution was to add a field to the table that the subForm used as
it's record source of the type OLEObject, then add this field to the
subForm. I created three Bit-Mapped Image files called
GreenSignal.bmp, YellowSignal.bmp, and RedSignal.bmp with MS Paint
that are filled with the desired color. In the AfterUpdate event of
the Severity field I used the following code:

Private Sub Severity_AfterUpdate()
On Error GoTo Severity_AfterUpdate_Err

Select Case Me.Severity
Case 1 To 5
Me.olePicture.SourceDoc = "D:\My Documents\My Pictures
\GreenSignal.bmp"
Me.olePicture.Action = acOLECreateLink
Me.olePicture.SizeMode = acOLESizeStrech
Case 6 To 8
Me.olePicture.SourceDoc = "D:\My Documents\My Pictures
\YellowSignal.bmp"
Me.olePicture.Action = acOLECreateLink
Me.olePicture.SizeMode = acOLESizeStrech
Case 9 To 10
Me.olePicture.SourceDoc = "D:\My Documents\My Pictures
\RedSignal.bmp"
Me.olePicture.Action = acOLECreateLink
Me.olePicture.SizeMode = acOLESizeStrech
Case Else
Exit Sub
End Select

Me.RPN = Me.Severity * Me.Occurence * Me.Detection

Exit_Severity_AfterUpdate:
Exit Sub

Severty_AfterUpdate_Err:
MsgBox Err.Number & " - " & Err.Description, vbExclamation, "ERROR
ASSIGNING OBJECT"
Resume Exit_Severity_AfterUpdate

End Sub

Setting the "SourceDoc" & "Action" properties based on the severity
number worked. And the value is retained after you close the form by
the table. The next time the form is opened the colors are displayed
correctly.

 

[Phil]

You may be over complicating the issue, could you not just use conditional
formatting to change the colour of the controls, this gives you the 3 options
(4 if you include the standard). I have tried it on a continous form and it
works

 

[Albert D. Kallal]

Any reason why you don't just use conditional formatting?

highlight one text box in design mode..and then go:

format->conditional formatting

You get a dialog box that lets you set the text, background etc for a given
expression to any fill color you want. It is designed for continues forms
and was a most requested feature that MS finally put in!!!

The feature was added in a2000....