Solved

Change color of record or field in a continuous form?

Posted on 2000-03-08
21
507 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
Comment Utility
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
Comment Utility
.. 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
Comment Utility
The best solution would be to use Access2000, which introduced conditional formatting on continuous forms.

Wes
0
 
LVL 4

Expert Comment

by:mberumen
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:TK421
Comment Utility
No prob. mb, I appreciate the attempt.
0
 
LVL 7

Expert Comment

by:Believer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
Okay, tested it... works great! (If I do say so myself)
0
 
LVL 1

Expert Comment

by:edice
Comment Utility
Looks like you have your solution, TK421......

Ed
0
 

Author Comment

by:TK421
Comment Utility
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
Comment Utility
you're very welcome!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now