vba 访问单个记录上连续形式的条件格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13937493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access Conditional Formatting for continuous form on individual records
提问by riley3131
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.
我有一个连续的表格，列出了采样的时间以及这些样本中不同化学物质的含量。我想格式化单元格以显示化学水平何时超出范围。我的问题是，它们在每个采样时间都会改变。.3 对于 10 小时的样品来说太高了，但 30 小时就可以了。下面是我的连续形式的示例。
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?
所以我想说“如果 Lactics > 0.2 在 20 小时，那么将单元格设为红色”等等，对于每个时间段和我正在跟踪的每个组件。如何使用 VBA 进行设置？
回答by HansUp
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.
然后将您的表单基于一个查询，该查询将您的原始表连接到 value_limits 表，并带有一个计算字段，lactics_flag指示值何时超出范围。并将条件格式基于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 Islist you would need to express those same rules:
将该方法的简单性与Expression Is表达相同规则所需的列表的复杂性进行比较：
([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_limitstable and use a LEFT JOINin the query.
如果您没有为 each 设置规则[Sample Time]，则可以将它们从value_limits表中删除并LEFT JOIN在查询中使用 a 。
回答by Fionnuala
You can use Expression Is wih a list of values:
您可以将 Expression Is 与值列表一起使用：
([sample]=10 And [Lactics]>=0.2) Or ([sample]=20 And [Lactics]>=0.35)
