VBA conditional formatting not visible in Print Preview

Question

I am trying to apply conditional formatting to a report using VBA. This works if I open the report in Report View, but does not work if I open the report in Print Preview (unless I first open in Report View).

This issue does not apply to conditional formatting added via the wizard. That displays correctly in Print Preview.

I am using Office365 with Access Version 2208

My setup uses a form to allow a user to enter a date. This date is then passed to the report as an OpenArg:

Private Sub cmd_Ok_Click()
    'DoCmd.OpenReport "Component Value", acViewPreview, , , , Me.txt_Date <- Does not show conditional formatting
    DoCmd.OpenReport "Component Value", acViewReport, , , , Me.txt_Date
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Then the report adds a format condition in its OnLoad event

Dim strCondDate As String

Private Sub Report_Load()
    If Not IsNull(Me.OpenArgs) Then
        strCondDate = Me.OpenArgs
    End If
    
    If Not IsNull(strCondDate) Then
        With Me.txt_Updated.FormatConditions
            With .Add(acExpression, , "[Last Updated] < #" & strCondDate & "#")
                .BackColor = vbYellow
            End With
        End With
    End If
End Sub

I can breakpoint this code and see that it runs correctly when I open the report with acViewPreview, but the formatting is not visible.

Why does the format condition not appear in Print Preview (unless I first open the report in Report View)? How can I get the formatting to be displayed?

Answer 1

Conclusion is Conditional Formatting rule cannot be programmatically added to report opened directly to Print or PrintPreview but can to ReportView. Fortunately, there is no need to use VBA. CF rule can grab OpenArgs content: Field Value Is less than CDate([OpenArgs]). Otherwise, have rule reference control on form that has user input.

Another alternative is to directly set Backcolor property in OnFormat or OnPrint event with conditional VBA structure. Drawback is these events do not execute in ReportView.