Change color on textbox in a Continuous Forms

[CJ_London]

don't know about 2007 - suggest google something like 'access 2007 max number of conditional formats' or similar. but suspect you are right

[Micron]

Ajax, forgive and correct me if I'm way off base here, but why not just evaluate the conditions in a Select Case block and set one based on the result:

Select Case optGroupChoice
  Case "g"
   With Me![RUNRESERVED]
       .BackColor = lngGreen
       .FontBold = True
       .ForeColor = lngBlack
   End With
  Case "y"
   With Me![RUNRESERVED]
       .BackColor = lngYellow
       .FontBold = True
       .ForeColor = lngBlack
   End With
etc, etc.
End Select

[CJ_London]

OP wants conditional formatting with more than three conditions (6 I believe) - so if the form is displaying 'one of each' three will revert to no formatting or be displayed up with the wrong format, although you can retrieve one as 'standard' formatting.

[Micron]

Guessed I missed (and still don't see) where it was stated that all six conditions had to be possible at the same time but on different records.

ok but how can I tell if (runselected) start with "g/" then color the runselected green or if start with "y" color yellow?

The 'or' part says to me, ("on my continuous form" as OP said they had) and I'm making up my own combinations here:
if the textbox starts with y, make the background yellow;
if the textbox starts with r, make the background red;
if the textbox starts with g, make the background green; etc., not
if in one record it's y, make it yellow, but if in another record if it's r make it red.

If that's your take, are you sure about that? Maybe the OP's limited experience has prevented them from picking up on something that's not required (because I can see why the posted solution would never work anyway)? Regardless of how many conditional format statements an Access version allows, I do not think it is possible to do so in a continuous form but I'm not sure. I'll take a whack at something but I may not be real quick. Maybe if the control called a function, it would be record-specific.

[Micron]

OK, regardless of whose interpretation is correct, I did manage to fudge something. Here are six records with 6 different SystemID's and 6 different colors for textboxes. However, I was correct (and further research proved) that you cannot affect properties of one control without affecting all instances of it. Plus, any vba code to set more than 3 conditions in a version that does not allow it will fail. What I have here is 6 textboxes, each with their own single condition based on the value of SystemID.


Why there are six: I tried to set the data control bg color by referencing the conditional color result. As you mentioned, it either does it to all based on the first or last record value, or oddly enough, it changes as you scroll the records. If I try tricks like overlaying the main control (set as transparent) with these color boxes, whatever you do to their color or transparency property is again affected throughout all records. Same story if I called a function instead. Best I could arrive at is a way to flag the record with a colored box (at least more than 3 choices are possible). You can probably envision that if you remove the border, in this data example I'll have a set of colored blocks like a set of stairs. I guess that leaves some decision making to be done: forget about looking pretty and find some other way (message box?) to draw attention, use this method above, make do with 3 conditions or upgrade Access. This method can be shortened to 2 textboxes if one puts 3 conditions on each IF 6 conditions are needed.

[barby.pava]

This was what i wanted

i was able to do it in a different way;
in my runs table i add the field runcolor for each runs then in my form i created a bounded ole object with the dlookup i set it in the back of the run number and now every runs has the right color.