Conditional formatting on Access form looking up a

Question

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)

Answer 1

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.