访问条件格式以对单个记录进行连续表单 [英] Access Conditional Formatting for continuous form on individual records

问题描述

我有一个连续的表格，列出了采样时间以及这些样品中不同化学物质的含量.我想格式化单元格以显示化学水平超出范围时.我的问题是，每个采样时间它们都会改变. .3对于10小时的样品来说太高了，但对于30小时的样品来说太好了.下面是我的连续表格的示例.

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

所以我想说如果20时Lactics> .2，则将单元格设为红色"，依此类推，在每个时间段和我要跟踪的每个组件上.如何使用VBA进行设置?

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?

推荐答案

创建一个表来保存您的超出范围"规则.

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                 ?

然后将表单基于查询，该查询将原始表连接到value_limits表，并带有计算字段lactics_flag，该字段指示值何时超出范围.并将条件格式基于lactics_flag.

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];

将这种方法的简单性与Expression Is列表的复杂性进行比较，您需要表达相同的规则:

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.

如果没有每个[Sample Time]的规则，则可以将其保留在value_limits表之外，并在查询中使用LEFT JOIN.

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.

这篇关于访问条件格式以对单个记录进行连续表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！