I am trying to format a text box control on a report to display in a specific font color depending on the value (e.g. Failed, Passed, Scheduled). However, regardless of the code I write in VBA, the report always returns the error "[u]You entered an expression that has no value.</u>" The code never makes it to the Select...Case. It fails when trying to assign a value to the variable.

The code I'm trying to use is:

Dim strFollowUp As String
strFollowUp = Me!txtFollowUp!value

Select Case strFollowUp
    Case "Failed"
        Reports.Item("Me.txtFollowUp").ForeColor = vbRed
    Case "Passed"
        Reports.Item("Me.txtFollowUp").ForeColor = vbGreen
    Case "Scheduled"
        Reports.Item("Me.txtFollowUp").ForeColor = vbBlue
    Case Else
        Reports.Item("Me.txtFollowUp").ForeColor = vbBlack
End Select

What line of code can I use to capture the value associated with the text box control on my report so I may associate a forecolor with it?

Any insight would be greatly appreciated.

When the code fails and halts on the assignation, highlight Me!txtFollowUp!value, right-click on it, and add it to to the Watch List. This will allow you to see what type of thing it is. It might not be convertible to type String.

If you just highlight Me!txtFollowUp you can examine its properties to see which one you can use.

Perhaps the field that goes into the control, and the control itself have the same, and Access is trying to find the .Value property of the field...

Thank you for the tip. However, I am fairly new to VBA and have been somewhat unsuccessful at using the Immediate and Watch windows. I can't seem to get them to tell me much of anything. I always get the error "Compile Error: Sub or function not defined" when I try to run the sub from the immediate window. Also, the properties from Me!txtFollowUp aren't telling me anything either.

The reason you get that error is that the sub or function you are trying to run is not public in scope.

Subs in a Form or Report are part of the internal machinery of them, and so cannot be run directly.

If you create a module, and add a public sub to it, you will be able to run it from the immediate window.

Now the error returned is "Object Required." Apparently, it's not capturing the text box object and, therefore, can't capture its value.

Based on my code above, where am I going wrong to capture the text box object and its associated value?

Is the error still on the line strFollowUp = Me!txtFollowUp!value?

Did you make a change that changed the message to "object required?"

After writing the code in a separate module in order to utilize the watch window, the new code is:

Public Sub Test()
On Error GoTo Err_Test

    Dim strReport, strField, strFollowUp As String
    strReport = "rptCorrectiveActionFollowUpReport"
    strField = "txtFollowUp"

    strFollowUp = strReport.strField.value
    MsgBox "Follow Up: " & strFollowUp

Exit_Test:
    Exit Sub

Err_Test:
    MsgBox Err.Description
    Resume Exit_Test

End Sub

Yes, the code is still failing at the point of capturing / assigning the value of strFollowUp. (Pardon the excessive code. I was just trying to break it down to its simplest elements for debugging.)

I found this in Help:

Change the format of a text box or other control on a report when it meets certain criteria

1. Open the report in Design view.

2. Make sure that the control is selected, and then, on the F[u]o</u>rmat [u]menu</u> (not the Format tab of properties), click Con[u]d</u>itional Formatting.

3. Do one of the following:

• To use values in the selected control as the formatting criteria, click Field Value Is in the first box, click the comparison phrase in the second box, and then type a value in the third (and fourth) box. You can enter a constant value or an expression.
• To use an expression as the formatting criteria (to evaluate data or a condition other than the values in selected controls), click Expression Is in the first box, and then enter the expression in the second box. The expression must return a logical value of TRUE or FALSE.

4. Select the font style, color, or other formatting that you want to apply.
Access applies the selected formatting only if the control value meets the condition, or the expression returns a value of TRUE.

5. To add another condition, click Add, then repeat steps 3 & 4.
You can specify up to 3 conditions. If none of the specified conditions are true, the control keeps its existing formatting.

[u]Note</u> If more than 1 specified condition is true, Access applies only the formatting of the 1st true condition.

I believe that is EXACTLY what I was looking for. I just couldn't find it so I was trying to compensate in VB.

Thanks for the help.

Have a Merry Christmas!

I hope you, too, have a merry Christmas. Thanks for that.