Change color of record or field in a continuous form?

Hello TK421,

Here's one suggestion you could use (too bad there's not build in method to change the background color of particular records in datasheet view or continous forms):

Add one field into your subform's Query which will indicate with "*" or some other character that the record is old. Then add this field into subform (perhaps begin of each line) and set it's Locked property True and Enabled property False.

Query would be something like this:

SELECT *,
       IIf([StartDate]+30<Date(),"*","") AS Indicator
FROM TABLE1;

What do you thing about this approach?

Regards,
Paasky

.. of course you could use values True (= older than 30 days) and False is not , and have a checkbox showing if the record is more than 30 days old:

SELECT *,
       IIf([StartDate]+30<Date(),True , False) AS Indicator
FROM TABLE1;

Paasky

The best solution would be to use Access2000, which introduced conditional formatting on continuous forms.

Wes

TK421:

you could create a function that changes the background color of your controls based on the contents of your date field i.e

function bgcolor (strdate) as double

if datediff("dd",Me![start date],date()) >30 then

bgcolor='enter the value for the color you've choosen here'

else

bgcolor='enter value for the default color'

end if

end function


then create an on current event on your form or on format event on a report that calls the function


Me![start date].BackColor=bgcolor(Me![Start Date])

hope this will give you some options

good luck

I think mberumen's suggestion won't work because it will color every instance of [start date] the same.  That is the problem with Access continuous forms until access2000.  Te be fair, I haven't tested it, so I may very well be wrong.

There is a way to simulate colored backgrounds using the format property of a text box, but I don't have any examples.  Maybe someone will post one.

Wes

Wesleysteward,

If you try to change background color of continuous form's control, it will effect to all records, not just one (Access 97). I installed A2000 just a couple of days ago and haven't yet had time to examine it closely (all my customers still using A97).

Current record background change effect can be found from Trygve's knowledge base. But I'm afraid it won't help here, because it only affects to current record, not multiple records in continuous form.

Here's the link to Trygve's hive: http://www.netservice.no/trygve/wiz/FrameText.asp?ArticleID=15B84BFD-450C-11D3-A9BB-005004227874 

Paasky

like wesleystewart said, the format property can help.  In MSAccess Help dialog box,
1. Go to the Index tab and type "Format Property"
2. Double-click "Format Property" in the list.
3. See sub-topics:
   "Format Property — Date/Time Data Type"
   "Format Property — Number and Currency Data Types"
   "Format Property — Text and Memo Data Types"
   "Format Property — Yes/No Data Type"

Each format property has a different number of "sections" available depending on the datatype (Yes/no has three, text/memo has two, etc.)  Since date only has one section, maybe you could calculate "Number of days beyond 30" and aply a format to it.  Numeric data types have the most number of format sections available to them: 4 for positive, negative, zero, and null values.

Believer,

Interesting approach. I just tried your method implemented with my second suggestion (true/false field) and got a fancy effect with format property: first I added textbox in the subform which Control Source was "Indicator" field. Then I defined following format mask to it:

;"Warning"[Red];"OK"[Green]

However I think it might be impossible to format all fields of particular record like this.

Paasky

mberuman,

I tried your suggestion, but I am getting an invalid procedure call on this line:

Me![start date].BackColor=bgcolor(Me![Start Date])

Any ideas?

TK421,

I think paasky its right, my solution won't work on a continuos form.  i can't think of anything else right now, my apologies

mberumen

No prob. mb, I appreciate the attempt.

paasky & TK:
paasky said "However I think it might be impossible to format *all* fields of particular record like this." but TK's question said "...or a *particular* field in the record."  Therefore I believe the method I suggested would work.

In the good ole days of Access 2.0,

I achieved a flagging effect by using the numeric conditinal formatting.
It allowed me to display the foreground color or type color of only that
field based on its positive, negative, zero, or null status.

It worked for me.
Not a nice approach given Access' power in other places.

Hope all of these suggestions help towards the answer you were hoping for.

Ed

by the way,

I think you can also make that conditional formating make
access display a word from the formatting text, in color,

Make the type larger and bolder on that field to make it stand out.

Ed

Edice, Believer,

Would I have to add a field to my table in order for this conditional formatting to work? I'm not sure exactly how to implement your suggestion. Can you be a little more specific? Thank you.

TK

Well I know you can do it in A95 and A97 because here's the method I use(which works).

Jim.

1. Create a text box equal in width to the detail section.

2. Set the control source equal to an expression that returns one of the following four values:

    Positive number
    Negative number
    Zero
    Null

3. Set the format of the control to:

   <<padding>>[Blue];<<padding>>[Red];<<padding>>[Green];<<padding>>[Yellow]

  Where "padding" is a repeated sequence of solid block characters (Alt + 0219 in terminal font).  Use the Character Map in Accessories to get it. Copy the char to the clipboard, then paste it.

4.  Set the controls font to terminal.

5.  set the foreground and background color to white.

6.  Set the control behind all the other controls on the form.

7.  Set all other controls in the section to background transparent.

Okay, tested it... works great! (If I do say so myself)

Looks like you have your solution, TK421......

Ed

Believer,
Indeed it does work! Yipee! First, my apologies for taking a few days. This database (that I inherited) is not the only thing I work on, so thank you for your patience (to all). This will work perfectly. I can alert each user of records >30 days. This way, they can't say "...duh, I didn't know..." Very cool. Thanks again Believer.

you're very welcome!