Access窗体上的条件格式查找值 [英] Conditional formatting on Access form looking up a value

问题描述

我在Access中创建了一个使用交叉表查询作为其数据源的表单。

查询的列标题是1,2,3,4和5，表示周数。
这些值显示的项目包括 3/3 = 100.00％或 0/13 = 0.00％或 3/14 = 21.00％。

我在表单上的文本框中添加了条件格式。

表达式正确（[2]， 7）=100.00％可以工作，并在百分比为100时显示为粗体红色的数字。

表达式为Val（Right（[2] ，7））= 100 也起作用 - 将文本值转换为数字值。

我遇到的问题是我并不总是在寻找100％ - 这取决于表格中的值。我想要做的是
$ b $ ul

Val（Right（[2]，7））=（SELECT ParamValue * 100 FROM tbl_System WHERE Param ='SampleSize'） - 这不起作用。

两者都不是：

• Eval（Val（Right（[2]，7））=（SELECT ParamValue * 100 FROM tbl_System WHERE Param ='SampleSize'））
  
  或
• 
  
• Val（Right（[2]，7））= EVAL（SELECT ParamValue * 100 FROM tbl_System WHERE Param ='SampleSize'） <
  或
  
• Val（Right（[2]，7））= DLookUp（ParamValue，tbl_System，Param =' （右（[2]，7））=100或$ Eval（DLookUp（ParamValue，tbl_System，Param ='SampleSize'）* 100）

  TRANSFORM NZ（Sum（Abs（[Include]））， 0）& /& NZ（Count（*），0）& =& 
 FormatPercent（NZ（Round（Sum（Abs（Include））/ Count（*），2），0），2）
 SELECT tbl_TMP_PrimaryDataSelection.TeamMember 
 FROM tbl_TMP_PrimaryDataSelection 
 GROUP BY tbl_TMP_PrimaryDataSelection.TeamMember 
 PIVOT tbl_TMP_PrimaryDataSelection.WeekNum In（1,2,3,4,5）
  

解决方案

我不认为你可以在那里使用一个函数，不管它是系统的还是用户定义的。

<但是你可以在运行时动态地定义FormatCondition，如下所示：


$ b

  Dim txtFld As TextBox 
 Dim objFrc As FormatCondition 
 Dim strExpr As String 
 
 Set txtFld = Me！myTextBox 
'Remove existing FormatConditions 
 txtFld.FormatConditions.Delete 
'动态表达式
 strExpr =Val（Right（[2]，7））=& * 100 
'将新的FormatCondition赋给文本框
 Set objFrc = txtFld.FormatConditions.Add（acExpression，，strExpr） 
'设置格式
 objFrc.ForeColor =& HFF0000 
  

这个例子只需删除并重新创建所有的FormatConditions。如果您拥有固定数量的条件，则还可以使用 FormatCondition.Modify 方法（请参阅联机帮助）。

<编辑：
我使用的最终代码在 Form_Load 事件上执行，并为五个每周文本box：

  Private Sub Form_Load（）
 
 Dim aTxtBox（1 5）作为TextBox 
 Dim x As Long 
 Dim oFrc As FormatCondition 
 Dim sExpr As String 
 
 With Me 
 Set aTBoxBox（1）=。 Wk1 
设置aTxtBox（2）= .Wk2 
设置aTxtBox（3）= .Wk3 
设置aTxtBox（4）= .Wk4 
设置aTxtBox（5）= .Wk5 
 
对于x = 1到5 
 aTxtBox（x）.FormatConditions.Delete 
 sExpr =Val（Right（[& x&]，7））> ; =& DLookup（ParamValue，tbl_System，Param ='SampleSize'）* 100 
设置oFrc = aTxtBox（x）.FormatConditions.Add（acExpression，，sExpr）
 oFrc.ForeColor = RGB （255,0,0）
 Next x 
 End With 
 
 End Sub 
  

编辑2

是的，在处理循环中的多个控件时，通过VBA定义FormatConditions特别有用。您也可以在设计视图中执行此操作，并永久保存FormatConditions，只是为了避免逐个浏览FormatConditions对话框。或者，如果客户稍后决定他宁愿有不同的颜色。 ：）

注意：您可以使用 Set aTBoxBox（x）= Me（Wk& x）在循环。但实际上，您不需要多个TextBox变量，只需重新使用即可。

I've created a form within Access which uses a cross-tab query as its data source.
The column headings for the query are 1, 2, 3, 4 and 5 representing week numbers. The values display items such as 3/3 = 100.00% or 0/13 = 0.00% or 3/14 = 21.00%.

I've added conditional formatting to the text boxes on the form.
Expression Is Right([2],7)="100.00%" works and displays the figure in bold red when the percentage is 100.
Expression is Val(Right([2],7))=100 also works - converting the text value to a numeric value.

The problem I'm having is that I'm not always looking for 100% - it depends on the value within a table. What I'm trying to do is

• Val(Right([2],7))=(SELECT ParamValue*100 FROM tbl_System WHERE Param='SampleSize') - this doesn't work.

Neither does:

• Eval(Val(Right([2],7))=(SELECT ParamValue*100 FROM tbl_System WHERE Param='SampleSize'))
  or
• Val(Right([2],7))=EVAL(SELECT ParamValue*100 FROM tbl_System WHERE Param='SampleSize')
  or
• Val(Right([2],7))=DLookUp("ParamValue","tbl_System","Param= 'SampleSize'")*100
  or
• Val(Right([2],7))=Eval(DLookUp("ParamValue","tbl_System","Param= 'SampleSize'")*100)

The SQL for the cross-tab query is:

TRANSFORM   NZ(Sum(Abs([Include])),0) & "/" & NZ(Count(*),0) & " = " & 
                FormatPercent(NZ(Round(Sum(Abs(Include))/Count(*),2),0),2)
SELECT      tbl_TMP_PrimaryDataSelection.TeamMember
FROM        tbl_TMP_PrimaryDataSelection
GROUP BY    tbl_TMP_PrimaryDataSelection.TeamMember
PIVOT       tbl_TMP_PrimaryDataSelection.WeekNum In (1,2,3,4,5)

解决方案

I don't think you can use a function in there, be it system or user-defined.

But you can define the FormatCondition dynamically at runtime, like this:

Dim txtFld As TextBox
Dim objFrc As FormatCondition
Dim strExpr As String

Set txtFld = Me!myTextBox
' Remove existing FormatConditions
txtFld.FormatConditions.Delete
' The dynamic expression
strExpr = "Val(Right([2],7))=" & DLookUp("ParamValue","tbl_System","Param='SampleSize'")*100
' Assign a new FormatCondition to text box
Set objFrc = txtFld.FormatConditions.Add(acExpression, , strExpr)
' Set the format
objFrc.ForeColor = &HFF0000

This example simply removes and recreates all FormatConditions. If you have a fixed number of conditions, you can also use the FormatCondition.Modify method (see online help).

Edit: The final code I have used executes on the Form_Load event and adds a format to each of the five weekly text boxes:

Private Sub Form_Load()

    Dim aTxtBox(1 To 5) As TextBox
    Dim x As Long
    Dim oFrc As FormatCondition
    Dim sExpr As String

    With Me
        Set aTxtBox(1) = .Wk1
        Set aTxtBox(2) = .Wk2
        Set aTxtBox(3) = .Wk3
        Set aTxtBox(4) = .Wk4
        Set aTxtBox(5) = .Wk5

        For x = 1 To 5
            aTxtBox(x).FormatConditions.Delete
            sExpr = "Val(Right([" & x & "],7))>=" & DLookup("ParamValue", "tbl_System", "Param='SampleSize'") * 100
            Set oFrc = aTxtBox(x).FormatConditions.Add(acExpression, , sExpr)
            oFrc.ForeColor = RGB(255, 0, 0)
        Next x
    End With

End Sub

Edit 2

Yes, defining FormatConditions via VBA is especially useful when dealing with multiple controls in a loop. You can do this in Design View too and save the FormatConditions permanently, simply to avoid going through the FormatConditions dialogs one by one. Or if the customer later decides that he'd rather have a different color. :)

Note: You could use Set aTxtBox(x) = Me("Wk" & x) in the loop. But actually you don't need multiple TextBox variables, you can simply re-use it.

这篇关于Access窗体上的条件格式查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！