Create a table Colours with columns ColourID and Colour. The Colour ID
column can be an autonumber, the Colour column should be an OLE Object. In
the Colour column insert bitmap images of a single colour each, using
whatever colours you want to distinguish the rows in your table.
If you want to be able to 'colour code' each row manually, in your current
table add a ColourID column of long integer number data type and insert
values corresponding to the ColourID of whatever colour you want for each
row. Join the two tables in a query on the ColourID columns. For manual
colour coding its as well to add a ColourName column to the colours table as
well so that when entering data you can select from a combo box listing each
colour by name, but bound to the ColourID column.
If you want the colour to be dynamically related to a particular value
another column then, instead of putting a ColourID column in your current
table, create a third table with two columns, ColourID (a long integer number
again) and a column with values corresponding to the values in your current
table, then join all three tables in a query. Say for instance the Colours
table had ColourID values of 1 and 2 for red and blue, and you want to colour
code rows with 'cat', 'dog' or 'horse' as blue, and rows with 'pig' or ''cow'
as red then this third table would have rows like so:
pig 1
cow 1
cat 2
dog 2
horse 2
As you can see the third table is modelling a many-to-many relationship
between your current table and the Colours table.
Create a form based on the query in which you've joined the tables and add a
text box control bound to the OLE column containing the bitmaps. You can
position this control however you wish, as a colour patch at one end of a
row, or as a large control behind the other controls to give a background
colour.
You could of course achieve something similar by means of conditional
formatting, eliminating the need for the Colours table completely, but the
advantage of using bitmap images is that as well as having a single uniform
colour, you can have patterns of more than one colour per row, or you can
have each colour as a different symbol, though the last only really works for
a colour 'patch' rather than a background.
Ken Sheridan
Stafford, England
