Conditional Formatting a form? 1

[HJessen]

I have a form that produces a datasheet output and covers different types of training that each employee has taken. Each of the training topics has a different grade spread.

i.e.;
Topic Grade Range
Shop Safety 50-41 (Exceptional)
40-31 (Passing)
30-21 (Very Low)
20-0 (Fails)
Vehicle Care 100-90 (Exceptional)
89-70 (Passing)
69-50 (Very Low)
49-0 (Fails)

What I need is a conditional format that looks at the topic and based on the grade from that particular topic changes the background and text color in the field. (The grade field is numeric.)

Basicly I need:
If topic = "Shop Safety" then do
if grade is GT 41 then
background = green
text = black bold
else if grade is LT 19 then
background = red
text = yellow/bold
Else If topic = "Vehicle Care" then do
if grade is GT 89 then
background = green
text = black bold
else if grade is LT 48 then
background = red
text = yellow/bold
END IF

Can anyone help me? I am NOT VBA literate and cannot seem to locate anything that will help me in the texts I have for reference.

THANKS.
H. Jessen
"Now I know more, and I feel dummer"

 

[chrissie1]

depends on what sort of form you have.

1) continous: look at the conditianal forrmat in the tools menu. more info if needed

2) single form
put this code in the on current event
If topic = "Shop Safety" then
if grade = "GT 41" then
topic.backcolor = vbgreen
topic.fontweight = "bold"
else
topic.backcolor = vbred
topic.forecolor = vbyellow
topic.fontweight = "bold"
Else topic = "Vehicle Care" then
if grade = "GT 89" then
topic.backcolor = vbgreen
topic.forecolor = vbblack
topic.fontweight = "bold"
else
topic.backcolor = vbred
topic.forecolor = vbyellow
topic.fontweight = "bold"
END IF
"What a wonderfull world" - Louis armstrong

 

[HJessen]

chrissie1:
I am using a continuious form; however, the conditional formatting on the tool bar will not work for me. The colors are dependent on the subject and the score. Unfortunately, each subject has a different range of acceptable, and not aceptable - it would be a LOT easier if every class was the same!

Let me try what you gave me and see what happens.

H. Jessen
"Now I know more, and I feel dummer"

 

[chrissie1]

that wont work (normally ) on a continious form only the condittional formatting will work "What a wonderfull world" - Louis armstrong

 

[HJessen]

This is not good! Guess I will have to find another way to display the information for the managers.

Thanks Anyway. H. Jessen
"Now I know more, and I feel dummer"

 

[uscitizen]

my objective is somewhat like hjessen's except that in my case i have gender coded 'M' or 'F' one one form and want to condition the value of a labtest on a different form using gender-specific normal ranges. the default display of the labtest control in bold black on a white background.

so for males, the value of labtest not between 12 to 18 inclusive would appear in red text and the for females, the value of labtest not between 14 to 19 inclusive would appear in red text. i am using a2k and my form is not of the 'continuous' variety. these are not the real values i am using as my list of lab ranges is not available to me as i write this, but i don't think that's all that important to cracking this.

 

[BillPower]

Hi again uscitizen,

While in your Form's design view select the "LabTest" field.

From the menu select Format>> Conditional Formatting.

Select "Expression Is" from Condition 1. In the Text Box immediately to the right put:

[Forms]![FormNameWithGenderOnIt]![Gender]=&quot;M&quot; And [LabTest]<12 Or [LabTest]>18 Or [Forms]![FormNameWithGenderOnIt]![Gender]=&quot;F&quot; And [LabTest]<14 Or [LabTest]>19

Just above the Cancel button right, bottom of the window you should see an Icon with a big A, click on that and select Red.

Click OK

Replace, if necessary Gender and LabTest with your actual Field names and of course FormNameWithGenderOnIt with the actual Form name.

If this doesn't work, please get back to me supplying EXACT Form/Sub Form and Control names.

Bill

 

[uscitizen]

Bill Power:

The form with Gender on it is 'Registration' and you guessed the values correctly: &quot;M&quot; and &quot;F&quot; (from a combo box) the user pulls down in the 'Gender' field on it. The clinical form's called &quot;Laboratory Data: Pre-Study&quot; and the labtest's name's: &quot;HGB (gm/dl)&quot;. The normal ranges for &quot;M&quot; and &quot;F&quot; are 14 to 18 and 12 to 16, respectively. Prior to your reply, I hadn't noticed the dropdown availability of 'Expression' is the Conditional Format window but still haven't managed to quite get this to do its thing. I'm thinking perhaps it's a question of 1) working out the parentesization for the Expression or 2) possibly A2K's requiring that we declare the form having the labtest value in the expression in additon to Registration or 3) both '1' and '2'.

 

[BillPower]

uscitizen:

This should work ok so long as the name of the form with Gender is called Registration and the name of the Control is called Gender. Also the control that you are formatting is assumed to be called HGB (gm/dl).

[Forms]![Registration]![Gender]=&quot;M&quot; And [HGB (gm/dl)]<12 Or [HGB (gm/dl)]>18 Or [Forms]![Registration]![Gender]=&quot;F&quot; And [HGB (gm/dl)]<14 Or [HGB (gm/dl)]>19

Bill

 

[uscitizen]

hi bill power,

the normal ranges for &quot;M&quot; and &quot;F&quot; are 14 to 18 and 12 to 16, respectively, which i inserted into my code per your last email but still no cigar!

 

[BillPower]

uscitizen:

I have posted a working example of this called &quot; USCitizen&quot; at

http://www.wpower.fsbusiness.co.uk/

You need to look at the Conditional Formatting in the Form named &quot;USCitizen&quot;

Will leave the example posted for the next 24 hours.

Bill

 

[uscitizen]

i have downloaded it and wanted to let you know after a bit of 'testing' found that when gender is 'm' and labtest's value 17 or higher, the result's displayed in red; according to the normal ranges, the upper limit of normal (black) is 18 -- making 17's appearance in red a little strange. the lower limits of 'm's labtest values were true to the norms (which i'll repeat here, i.e. m: 14 to 18 and f: 12 to 16), so values of 11 and under turned (correctly) red. regarding the behavior of the female specific formatting, below 11 and above 16 were displayed (correctly) in red. so i guess the question(s) is/are a) what's up with 'm' and labtest of 17 and b) why does this code not seem to work in my application at all?

 

[BillPower]

Hi, very strange, have changed the Conditional Formatting to:

[Forms]![Registration]![Gender]=&quot;M&quot; And [HGB (gm/dl)] Between 14 And 18 Or [Forms]![Registration]![Gender]=&quot;F&quot; And [HGB (gm/dl)] Between 12 And 16

Also have changed the Default ForeColor to Red and if the Condition above is met ForeColor = Black
========================================================
Are you using Access 2000 or greater. If this latest suggestion doesn't work send me a copy of your DB, removing any sensitive records first. billpower@cwcom.net

Bill

 

[dcorrea]

Using MS Access XP, I unchecked 1) Allow Full Menus, 2) Allow Default Shortcut Menus & 3) Use Access Special Keys under Tools, Startup. Is there a way to get access back to file formatting (tables, queries, forms, etc.)? Your assistance is greatly appreciated.

DC

 

[BillPower]

Hi dcorrea,

You could try pressing the Shift key while opening your DB.

Bill

 

[uscitizen]

Bill Power: I'm on my way to the office as I write where I'll have a chance to try out your modification on my Windows 2000 machine running A2K. Just to cover some more bases as it were, I notice that your miniaturization of the problem comes with no underlying tables that correspond with the registration and labtest forms. I never mentioned that I am using tables (after which the forms were named), don't see why that would make a difference, but to be superstitious figure it couldn't hurt to bring it up just in case the failure to get any range checking is due to the use of bound (vs. your unbound) controls on my part.

ps: there is an opportunity to define three conditions when creating conditional formats.....are they chained to the first one using boolean 'and' or the boolean 'or' operation?

 

[BillPower]

Hi uscitizen,

I'm not building an application, trying with very little success in demonstrating what should be a simple technique to understand. How you use, implement or expand on that technique is up to you.

As for the ps. Basic example of 3 Conditions for 1 control.

Field Vaue is Between 1 And 2 - Bold, ForeColor Red
Field Vaue is Between 3 And 4 - Italic, Forecolor Blue
Field Vaue is Between 5 And 6 - Disabled

Bill

 

[uscitizen]

Bill:

I think there's more work to be done with this, maybe using VBA.

Thanks.

 

[BillPower]

Hi uscitizen,

Thanks for the star!

Unless you are very lucky, there will always be more VBA coding and programming to be done after a suggestion is made. I've hopefully answered your question, it's up to you now to make it work for your app. But you are welcome if you get stuck to get back to me, giving as much info as possible explaining what's not working..

I have gone one step further, posting a demo of how to synchronize the records on two forms. The file can be downloaded from

http://www.wpower.fsbusiness.co.uk/

and the file to look at is: Synchronize Records On Two Forms.zip

I've shown that the criteria that you gave &quot;m: 14 to 18 and f: 12 to 16&quot; does work. Maybe take a little time to see what we are doing differently and get back to me.

Cheers

Bill

 

[uscitizen]

Thanks this is interesting.....I am going ahead with a VBA procedure in lieu of the conditional formatting. The procedure requires that both (&quot;Registration&quot; and &quot;Laboratory Data: Pre-Study&quot forms be open and synchronized. I wonder if your latest example has potential. Hmmmmm...