PS.

DLookup could get fairly slow. It may be better to include the lookup table
in the query feeding the report. Link the lookup table to the main table on
the condition field and the field in the main table that has the value (i.e.
X1, X2, etc). You would then have the associated color field available for
each record. Place a textbox in the same report section as txtBox and set
this new textbox's Visible property to No. In the Format event code for the
section you could now use:

Me.txtBox.BackColor = Me.txtColorBox

To handle the Else condition as in the Case Else in the Select statement,
set the link between the tables to "include all fields from the main table
and only those fields from the lookup table where the fields match". This
will give you a value of Null for all records that didn't have a color
specified. You would then change the above statement to:

Me.txtBox.BackColor = Nz(Me.txtColorBox, 16777215)

This will return the color 16777215 if a color hasn't been specified.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
So I would not be able to do this by setting up a table with the condition
in
one column and the color# in the second column.
I would need to specify the condition in the line of code?

This list has the potentional of expanding; what I wrote was just a
quick-and-dirty example.

Sorry I didn't clarify that before.

"Wayne Morgan" wrote:

Select Case Me.txtBox
Case "X1", "X2", "X3"
Me.txtBox.BackColor = 255
Case "Y1", "Y2"
Me.txtBox.BackColor = 100
Case "Z1"
Me.txtBox.BackColor = 301
Case "Z2"
Me.txtBox.BackColor = 302
Case Else
Me.txtBox.BackColor = 16777215
End Select

In the first statement, you may be able to get by with

Case "X1" To "X3"

so, if you have a lot more of these (i.e. X4, X5, X6, etc), that may be
the
shorter way to write it. With just 3 elements though, it's not much
shorter.
Whether or not this could be simplified (to lessen the typing) will
depend
on how many you have and what sort of pattern they are (i.e. is there a
pattern to them that would be short and easy to code). The Like operator
may
come to mind, but it's not supported in a Select Case statement.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
Could this work:

Condition BackColor
X1 255
X2 255
X3 255
Y1 100
Y2 100
Z1 301
Z2 302



Would this be more efficient, espically if I have a long list, and more
conditions arise that would require highlighting?

How would I structure the VBA code?