How to force conditional formatting of a subform's fields to be refreshed?

[Alc]

I have a sub form on one of my forms. This sub form has its default view set to Continuous Forms and uses conditional formatting to change the background colour of each cell, depending on the value of one cell in each row. This is all working well and looks exactly how I want.

My problem is that, when the user makes a selection on the main form and the sub form data changes accordingly, the conditional formatting isn't automatically displayed. All fields remain white until the user passes his mouse over them, at which point the conditional formatting 'kicks in' and the desired colours appear.

Is there a command I can add in VBA to force all cells to change colour without any user interaction being required?

I'm using requery to load the data and that doesn't work.
I've also tried refresh, without any luck.

 

[sneuberg]

You should have to do anything to update the conditional formatting as the simple attached database demonstrates. Something else is going on. I suggest looking at the Mouse Move events of the subform and its fields. If you upload your database I'll see if I can find your problem.

 

[Alc]

Thanks very much for the response and the kind offer. Unfortunately, the security settings here don't allow to up or download anything.

In case it provides any clues, this is the code behind the field on the main form that changes what's displayed in the subform.

 Private Sub cboCampaignCode_AfterUpdate()
    Campaign_Code_Change
End Sub
  
 Sub Campaign_Code_Change()
    Dim Rst As Recordset
    Dim strCampaignCode As String
    Dim llWorstDiff As Long
    Dim Qdf As QueryDef
    Dim strColour As String
    
    strCampaignCode = Nz(Me![cboCampaignCode], "")
    Me!txtCampaignDescription = DLookup("English_Description", "IWH_CAMPAIGN_HEADER_DIM", "([Division_Code] & [Campaign_Number]) = '" & strCampaignCode & "'")
    If strCampaignCode <> "" Then
        Forms!frmCampaignDates!subfrmCampaignStageDates.SourceObject = "frmCampaignStageDates"
        Set Rst = Forms![frmCampaignDates].RecordsetClone
        Rst.FindFirst "tblCampaign.Camp = '" & strCampaignCode & "'"
        If Rst.NoMatch = False Then
            Forms![frmCampaignDates].Bookmark = Rst.Bookmark
        End If
        [COLOR=red]Forms!frmCampaignDates!subfrmCampaignStageDates.Requery
[/COLOR]        Me![cboCampaignCode] = ""
        
        If Me!btnTargetVis.Caption = "Hide Targets" Then
            Show_Targets
        Else
            Hide_Targets
        End If
        
        'Set 'master' colour on main form
        strColour = Nz(DLookup("Overall_Colour", "tblCampaign", "[Camp] = '" & strCampaignCode & "'"), "WHITE")
        Select Case strColour
            Case "GREEN"
                Me!boxOverallStage.BackColor = RGB(153, 204, 0)
            Case "ORANGE"
                Me!boxOverallStage.BackColor = RGB(255, 255, 0)
            Case "RED"
                Me!boxOverallStage.BackColor = RGB(255, 0, 0)
            Case "WHITE"
                Me!boxOverallStage.BackColor = RGB(255, 255, 255)
        End Select
    End If
End Sub
  
 Sub Show_Targets()
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.lbl1_Diff.Visible = True
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.lbl1_Tgt.Visible = True
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.lbl2_Tgt.Visible = True
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.BlankTarget.Visible = True
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.PopulatedTarget.Visible = True
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.DaysDifference.Visible = True
    
    Me!btnTargetVis.Caption = "Hide Targets"
End Sub
  
 Sub Hide_Targets()
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.lbl1_Diff.Visible = False
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.lbl1_Tgt.Visible = False
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.lbl2_Tgt.Visible = False
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.BlankTarget.Visible = False
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.PopulatedTarget.Visible = False
    Forms!frmCampaignDates!subfrmCampaignStageDates.Form.DaysDifference.Visible = False
     Me!btnTargetVis.Caption = "Show Targets"
End Sub

There is no code behind the 'On Open' event of the subform.

Bearing in mind your comment that "Something else is going on", I tried exiting the sub immediately after the line of code in red and it didn't help at all.

 

[sneuberg]

This code appear just to change the record of the main form by going to whatever is in Me![cboCampaignCode] so the records in the subform should be changed via the link fields and the conditional format should be applied. I can't see from this code why that's not happening.

Please try this. Enable record navigation in the main form (if not enabled) and scroll through the records. Do to the records change in the subform accordingly and is the conditional formatting applied when you do it this way?

Also please check the Mouse Move events of the subform and its fields for any code.

Also a screen shot of the conditional formatting rules might help.