CODEWITHSUNDEEP
vbams-access
riley3131
riley3131

Reputation: 280

Access Conditional Formatting for continuous form on individual records

I have a continuous form that list times that samples were taken and levels of different chemicals in those samples. I want to format the cells to show when a chemical level is out of range. My problem is, that they change for each sample time. .3 is way too high for a 10 hour sample, but is fine for 30 hours. Below is an example of what my continuous form looks like.

Sample Time      Lactics      Sugar
10 hour           .085        15.2
20 hour           .125        12.8
30 hour           .345        8.4
40 hour           .405        4.2
50 hour           .415        1.9

So I want to say "if Lactics > .2 at 20 hour then make the cell red" and so on for each time period and each component I am tracking. How can I set this up with VBA?

Upvotes: 3

Views: 8845

Answers (2)

HansUp
HansUp

Reputation: 97101

Create a table to hold your "out of range" rules.

Sample Time Lactics_limit
10 hour                .3
20 hour               .35
30 hour                .4
40 hour               .45
50 hour                 ?

Then base your form on a query which joins your original table to the value_limits table, with a calculated field, lactics_flag, which indicates when the value is out of range. And base your conditional formatting on lactics_flag.

SELECT
    y.[Sample Time],
    y.Lactics,
    y.Sugar,
    IIf(y.Lactics > v.Lactics_limit, True, False) AS lactics_flag
FROM
    YourTable AS y
    INNER JOIN value_limits AS v
    ON y.[Sample Time] = v.[Sample Time];

Compare the simplicity of that approach with the complexity of an Expression Is list you would need to express those same rules:

([Sample Time]="10 hour" And [Lactics]>0.3) Or ([Sample Time]="20 hour" And [Lactics]>0.35) Or ([Sample Time]="30 hour" And [Lactics]>0.4) Or ([Sample Time]="40 hour" And [Lactics]>0.45) Or ([Sample Time]="50 hour" And [Lactics]>?)

Another advantage of this approach is that it's easier to maintain your rules when they are stored in a table instead of as conditional formatting expressions in a form. And the rules could be easily re-used for other forms or reports.

If you don't have rules for every [Sample Time], you could leave them out of the value_limits table and use a LEFT JOIN in the query.

Upvotes: 4

Fionnuala
Fionnuala

Reputation: 91306

You can use Expression Is wih a list of values:

 ([sample]=10 And [Lactics]>=0.2) Or ([sample]=20 And [Lactics]>=0.35)

Conditional

Upvotes: 2

Related Questions