Solved

Change color of record or field in a continuous form?

Posted on 2000-03-08
21
523 Views
Last Modified: 2008-02-26
What is the best way to achieve my goal:

I have a continuous form that displays several records. Ii would like to alert the user of records that are older than 30 days. This alert could be a font color change, background color change, etc. for the whole record or a particular field in the record.  Just some way to make it clear that the record is >30 days. Any ideas?

I have a "date field" [Start Date].

Thanks,
TK421
0
Comment
Question by:TK421
  • 5
  • 4
  • 4
  • +4
21 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2596817
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2596836
.. 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
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2596867
The best solution would be to use Access2000, which introduced conditional formatting on continuous forms.

Wes
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 4

Expert Comment

by:mberumen
ID: 2597087
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
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2597343
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2597470
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
0
 
LVL 7

Expert Comment

by:Believer
ID: 2597493
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.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2597548
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

0
 

Author Comment

by:TK421
ID: 2597607
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?

0
 
LVL 4

Expert Comment

by:mberumen
ID: 2597628
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
0
 

Author Comment

by:TK421
ID: 2597640
No prob. mb, I appreciate the attempt.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2597671
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.
0
 
LVL 1

Expert Comment

by:edice
ID: 2597765
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
0
 
LVL 1

Expert Comment

by:edice
ID: 2597773
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
0
 

Author Comment

by:TK421
ID: 2597801
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
0
 
LVL 57
ID: 2597805
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.

0
 
LVL 7

Accepted Solution

by:
Believer earned 40 total points
ID: 2597843
Write a query over the table and add the a calculated field with this expression: datediff("d",[datDate],date())-30
where [datDate] is the field from your table.  The calculated value will return zero or a positive number for records >=30 days old.  It will return a negative number for reords <30 days old.  In the subform, use something like this for the format property on the caluclated field: "Deadbeat!"[Red];"";"Deadbeat!"[Red];""
The red word "Deadbeat!" will appear for zero and negative values (the 2nd and 3rd positions in the format property) and nothing will appear for positive and null values (the 1st and 4th positions in the format property).  I haven't tested it, but think it should be fine (maybe with a little tweaking).
0
 
LVL 7

Expert Comment

by:Believer
ID: 2597957
Okay, tested it... works great! (If I do say so myself)
0
 
LVL 1

Expert Comment

by:edice
ID: 2600566
Looks like you have your solution, TK421......

Ed
0
 

Author Comment

by:TK421
ID: 2612020
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.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2612097
you're very welcome!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question