Conditional Formatting the Field based on the Value of Another Field

[Jopetes]

I have a report in which one of the field value (Text) to be highlighted based on the value of the another filed.

My Fields are [Ordered Supplier], [Invoiced Supplier]. If they are not equal should be highlighted with yellow and blue respectively.

I tried with user wizard in access but not working

If someone can help by VBA code, would be most beneficial.

Thank you

 

[Galaxiom]

No VBA required. Use ConditionalFormatting. Right click on the textbox to find it.

In the leftmost dropdown select [Expression Is].

 

[Jopetes]

I have done that but in report no desired result appears. The report appears in the normal colour only.

I have tried with VBA code and the font colour changes in first two fields. I have another similar fields such as "Ordered Currency" and "Invoiced Currency" , "Ordered Qty" and "Invoiced Qty" etc. The code result favourably comes only for the first two fields only.

 

[Galaxiom]

Changing the properties with VBA doesn't work as formatting on Reports and ContinuousForms.

You have a problem with your ConditionalFormatting expression. What expressions do you have?

 

[Jopetes]

I have the following codes

Option Explicit
Option Compare Text

Private Const ConRed As Long = vbRed
Private Const ConYellow As Long = vbYellow


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Ord_Supp <> Me.Inv_Supp Then
Me.Ord_Supp.BackColor = ConRed
Me.Ord_Supp.FontBold = True
Me.Inv_Supp.BackColor = ConYellow
Me.Inv_Supp.FontBold = True
End If
If Me.Ord_Curr <> Me.Inv_Curr Then
Me.Ord_Curr.BackColor = ConRed
Me.Ord_Curr.FontBold = True
Me.Inv_Curr.BackColor = ConYellow
Me.Inv_Curr.FontBold = True
End If
If Me.Ord_Qty <> Me.Inv_Qty Then
Me.Ord_Qty.BackColor = ConRed
Me.Ord_Qty.FontBold = True
Me.Inv_Qty.BackColor = ConYellow
Me.Inv_Qty.FontBold = True
End If
If Me.Ord_Price <> Me.Inv_Price Then
Me.Ord_Price.BackColor = ConRed
Me.Ord_Price.FontBold = True
Me.Inv_Price.BackColor = ConYellow
Me.Inv_Price.FontBold = True
End If

End Sub

 

[isladogs]

Changing the properties with VBA doesn't work as formatting on Reports and ContinuousForms.

Why do you say that?
I have many examples of both reports and continuous forms where format conditions are set using code. For example:

  'CR 18/10/2008 - 'set formatting conditions for totals column
        With Me("Total").FormatConditions
            .Delete
                If ResidualsFlag Then   'format to traffic lights if residuals are used
                        Set Fc = .Add(acFieldValue, acLessThan, 0)
                        Fc.ForeColor = vbBlack
                        Fc.BackColor = vbRed
                        Fc.FontBold = True
                        
                        Set Fc = .Add(acFieldValue, acGreaterThan, 0)
                        Fc.ForeColor = vbBlack
                        Fc.BackColor = vbGreen        '
                        Fc.FontBold = True
                            
                ElseIf ResidualGradesFlag = True Or GradesFlag = True Or PointsFlag = True Then
                        'format to traffic lights if grades are used
                         Set Fc = .Add(acFieldValue, acLessThan, 4)
                         Fc.ForeColor = vbBlack
                         Fc.BackColor = vbRed
                         Fc.FontBold = True
                         
                         Set Fc = .Add(acFieldValue, acBetween, 4, 4.9)
                         Fc.ForeColor = vbBlack
                         Fc.BackColor = 39423 'Amber
                         Fc.FontBold = True
                         
                         Set Fc = .Add(acFieldValue, acGreaterThanOrEqual, 5)
                         Fc.ForeColor = vbBlack
                         Fc.BackColor = vbGreen        '
                         Fc.FontBold = True
                End If
                
        End With

Having said that, in many cases conditional formatting often works just as well if not better (as you can have more format conditions using CF)

 

[Galaxiom]

Why do you say that?
I have many examples of both reports and continuous forms where format conditions are set using code.

Controls on ContinuousForms are all instances of the same control so changing the ForeColor and BackColor with VBA affects all records with whatever the expressions calculate on the current record.

 

[isladogs]

Controls on ContinuousForms are all instances of the same control so changing the ForeColor and BackColor with VBA affects all records with whatever the expressions calculate on the current record.

That's what I thought you might say.
However, this continuous form is one of many using VBA. It does not use conditional formatting via the wizard

Code used for formatting

Sub GetFormatting()

On Error GoTo Err_Handler

    Dim Fc As FormatCondition
    
    With txtCountRecords
        With .FormatConditions
        .Delete
        
        Set Fc = .Add(acFieldValue, acGreaterThan, "19") 'CR - Changed to 19 from 20 - 8/10/2008
        Fc.ForeColor = 0
        Fc.BackColor = 6605290 'Gold
        Fc.FontBold = True
        
        Set Fc = .Add(acFieldValue, acBetween, "10 ", "19")
        Fc.ForeColor = 0
        Fc.BackColor = 12959930 'Silver
        Fc.FontBold = True
        
        Set Fc = .Add(acFieldValue, acBetween, "5 ", "9")
        Fc.ForeColor = 64
        Fc.BackColor = 9692925  'Bronze 27865
        Fc.FontBold = True
        
        End With
    End With
    
    
    With txtAcYearTotal
        With .FormatConditions
        .Delete
        
        Set Fc = .Add(acFieldValue, acGreaterThan, "19") 'CR - Changed to 19 from 20 8/10/2008
        Fc.ForeColor = 0
        Fc.BackColor = 6605290 'Gold
        Fc.FontBold = True
        
        Set Fc = .Add(acFieldValue, acBetween, "10 ", "19")
        Fc.ForeColor = 0
        Fc.BackColor = 12959930 'Silver
        Fc.FontBold = True
        
        Set Fc = .Add(acFieldValue, acBetween, "5 ", "9")
        Fc.ForeColor = 64
        Fc.BackColor = 9692925  'Bronze 27865
        Fc.FontBold = True
        
        End With
    End With

Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog
    Resume Exit_Handler

End Sub

I have MANY other similar examples

 

[Minty]

@Colin - Do you find this method makes the forms less slow to update the display when there are a lot of records on screen?

I had a form where I was only setting the colour of 1 background box per record and you could visibly see it painting the individual records. It was especially bad when scrolling through the records.

We left it as the visual highlight made the form much easier to use, but it didn't look very professional.

 

[isladogs]

Hi Minty
Depends on the form/report.
For 'simple' formatting such as the form I showed, its fast enough not to be an issue. Less than a second for the form in the last reply which has approx 1500 student records

I haven't noticed any difference in speed whether using code for formatting or CF.

I do have a horrendously complex report where student exam grades are formatted using code based on a traffic light system.
The student grades are displayed but the formatting is based on the residual grade (difference in points between the actual grade and the target grade).
That has to be done using code as CF wouldn't hack it!
However it is slow - approx 1 minute to load as there may be around 20000 'slots' to format (300 students x 65 subjects). Apologies for the quality of the low res image

 

[jleach]

I've generally found Conditional Formatting to be slow, especially based on heavily calculated fields (usually some flicker/delay in rendering the format, especially noticeable when scrolling).

That said, I've not noticed it to be any worse when applying them via VBA instead of through the UI.

I have a function I use for highlighting the selected row, which I use rather liberally (when I'm on an Access project), and haven't had any particular issues.

Private Sub Form_Current()
  
  Dim fc As FormatCondition
  
  With Me.ctlTargetControl
    While .FormatConditions.count > 0
      .FormatConditions.Delete
    Wend
    Set fc = .FormatConditions.Add(acExpression, acEqual, "[ID] = " & Me.ID)
    fc.FontBold = True
  End With
  
End Sub

(sometimes that code has to be updated for more complex forms, to take into account additional conditions, but it's my boilerplate start for most any continuous form)

 

[Galaxiom]

That's what I thought you might say.
However, this continuous form is one of many using VBA. It has no conditional formatting

You are just applying ConditionalFormatting using code. I would have thought the similarity of the terms FormatConditions and ConditionalFormatting should have made that connection fairly obvious.

If you doubt that they are one and the same, read the items from the FormatConditions Collection of a control that has manually applied ConditionalFormatting.

Either way, ConditionalFormatting can be quite slow.

 

[isladogs]

You are just applying ConditionalFormatting using code. I would have thought the similarity of the terms FormatConditions and ConditionalFormatting should have made that connection fairly obvious.

If you doubt that they are one and the same, read the items from the FormatConditions Collection of a control that has manually applied ConditionalFormatting.

Either way, ConditionalFormatting can be quite slow.

As I'm sure you were aware from the accompanying screenshots, I was referring to the fact that the formatting was done via code and not the CF wizard,
Of course I'm aware that it's the same whether done via VBA or CF. I have been using both for 20 years
Whilst CF has improved significantly over that time, there are still cases where the conditions are too complex for CF so VBA has to be used

In case you've forgotten I was responding to your inaccurate statements in posts 4 & 7.

I agree it CAN be slow as in the complex report I mentioned earlier.
But in many cases, even with many records, the formatting takes less than a second.
In cases where I know it will be slow, I warn users and give the option of having no formatting

 

[isladogs]

I've generally found Conditional Formatting to be slow, especially based on heavily calculated fields (usually some flicker/delay in rendering the format, especially noticeable when scrolling).

That said, I've not noticed it to be any worse when applying them via VBA instead of through the UI.

I have a function I use for highlighting the selected row, which I use rather liberally (when I'm on an Access project), and haven't had any particular issues.

I occasionally see flickering but turning off screen updating whilst the format code is running usually fixes that.

I also have code for highlighting the current record though in my case I use a hidden control filling the width of the form and make it visible when a record is selected.

Either way, MS should really have provided built in code to highlight the selected record by default as is the case with listbox selection

 

[Galaxiom]

In case you've forgotten I was responding to your inaccurate statements in posts 4 & 7.

I stand by what I said. You are still using ConditionalFormatting even though you applied it using code.The result is identical whether applied manually or with code.

It is your claim that is patently false:

However, this continuous form is one of many using VBA. It has no conditional formatting

Would you also claim that your project doesn't use forms if you only open them with code instead of the Navigation Pane?

Whilst CF has improved significantly over that time, there are still cases where the conditions are too complex for CF so VBA has to be used

Care to demonstrate an example applied to a ContinousForm?

 

[isladogs]

Sigh ..... I also stand by my comments

Post #4

Changing the properties with VBA doesn't work as formatting on Reports and ContinuousForms.

Post #7

Controls on ContinuousForms are all instances of the same control so changing the ForeColor and BackColor with VBA affects all records with whatever the expressions calculate on the current record.

Both of the above statements you made are incorrect

I stand by what I said. You are still using ConditionalFormatting even though you applied it using code.The result is identical whether applied manually or with code.

It is your claim that is patently false:

Would you also claim that your project doesn't use forms if you only open them with code instead of the Navigation Pane?

Care to demonstrate an example applied to a ContinousForm?

I've already answered the first two points
The third isn't worth responding to

I could look for a suitable example using a continuous form later.
In the meantime here is an example from the report in post #10.
As already stated, the reason it is done using VBA is that the formatting is applied to the student grades (visible) but is based on the residual point (not shown).
So I start with two fields - residual & grade e.g. -10 & B ; 5 & E ; 0 & C
These are combined for the report crosstab -10:B ; 5:E; 0:C
The conditional formatting is based on the part before the colon.
The displayed value is the part after the colon

The actual formatting is in itself not complex - its the application of it

This is part of the relevant code and covers adjusting column widths and other things as well

========================================================

Private Sub Report_Open(Cancel As Integer)

On Error GoTo Err_Handler
    
    'set information for error log
    strItemName = Me.Name
    SetNewObjectLogItems
    
    'CR v4633W - modified code to update column width dynamically depending on number of subjects
    AllSubjectGridWidth = 19.95 'width of all columns used for subject grades
    
    'Get list of subjects so that the labels and controls can be populated.
    'CR - modified 15/07/2009 to limit to subjects examined in selected year
    'CR v5113 - excluded dummy subject 'ZZ'
    strSubjectList = "SELECT DISTINCT LeaversGCSESubjectSummary.SubjectID, LeaversGCSESubjectSummary.Description," & _
        " LeaversGCSESubjectSummary.Year, LeaversGCSESubjectSummary.Weighting" & _
        " FROM LeaversGCSESubjectSummary" & _
        " WHERE (((LeaversGCSESubjectSummary.SubjectID)<>'ZZ') AND ((LeaversGCSESubjectSummary.Year) = GetExamYear()))" & _
        " ORDER BY LeaversGCSESubjectSummary.Description;"
        
    'get fields for crosstab and 'fix' these in the SQL for the record source
    Dim Flds As String
    Flds = " In ("
     
    Set db = CurrentDb
     
    Set MySet = db.OpenRecordset(strSubjectList)
    With MySet
        If .RecordCount > 0 Then
        .MoveLast
        .MoveFirst
        NumberOfSubjects = .RecordCount
        
        'determine column width 'CR v4633W
        'reduced min size v5191
        SGW = AllSubjectGridWidth / NumberOfSubjects 'CR v4633W - width of each subject column
        'If SGW > 0.37 Then SGW = 0.37 'set max size
        If SGW > 0.5 Then
            SGW = 0.5 'set max size
        ElseIf SGW < 0.318 Then
            SGW = 0.318 'set min size
        Else
            SGW = SGW 'set as calculated
        End If
        
        intCount1 = 1
        Do Until .EOF
            Flds = Flds & "'" & !SubjectID & "', "
           'Debug.Print flds
            Me("Text" & intCount1).ControlSource = Replace(!SubjectID, ".", "_")
            Me("Text" & intCount1).visible = True
            'Me("Text" & intCount1).Left = (7.1 + (0.35 * intCount1)) * TwipsNumber
            Me("Text" & intCount1).Left = (6.35 + (SGW * intCount1)) * TwipsNumber
            Me("Text" & intCount1).Width = SGW * TwipsNumber 'CR v5116
                    
            With Me("Text" & intCount1).FormatConditions
            .Delete
            
            'Set traffic light for residuals
                Set Fcn = .Add(acFieldValue, acLessThanOrEqual, -12)
                    Fcn.ForeColor = vbWhite
                    Fcn.BackColor = vbRed
                '    'fc.FontBold = True
                    
                Set Fcn = .Add(acFieldValue, acBetween, -6, -11)
                    Fcn.ForeColor = vbBlack
                    Fcn.BackColor = 39423       'amber
                '    'fc.FontBold = True
                    
                Set Fcn = .Add(acFieldValue, acGreaterThanOrEqual, 6)
                    Fcn.ForeColor = vbWhite
                    Fcn.BackColor = vbGreen        '
                '    'fc.FontBold = True
                    
            End With
            Me("Label" & intCount1).visible = True
            'Me("Label" & intCount1).Left = (7.1 + (0.35 * intCount1)) * TwipsNumber
            Me("Label" & intCount1).Left = (6.35 + (SGW * intCount1)) * TwipsNumber
            Me("Label" & intCount1).Width = SGW * TwipsNumber 'CR v5116
            Me("Label" & intCount1).Caption = " " & Replace(!Description, "&", "&&")
            
            Me("Subj" & intCount1).visible = True
            'Me("Subj" & intCount1).Left = (7.1 + (0.35 * intCount1)) * TwipsNumber
            Me("Subj" & intCount1).Left = (6.35 + (SGW * intCount1)) * TwipsNumber
            Me("Subj" & intCount1).Width = SGW * TwipsNumber 'CR v5116
            'Me("Subj" & intCount1).Caption = !SubjectID
            Me("Subj" & intCount1).Caption = Nz(!Weighting, "") 'CR v4557W
            
            '==========================
            'CR - Subject totals added v4557W
            strCriteria = "Year = " & intYear & " And SubjectID = '" & !SubjectID & "'"
            'Debug.Print strCriteria
            
            'Calculate subject entries
            strText1 = DLookup("Entries", "LeaversGCSESubjectSummary", strCriteria)
            'Me("TCount" & intCount1).Left = (7.1 + (0.35 * intCount1)) * TwipsNumber
            Me("TCount" & intCount1).Left = (6.35 + (SGW * intCount1)) * TwipsNumber
            Me("TCount" & intCount1).Width = SGW * TwipsNumber 'CR v5116
            Me("TCount" & intCount1).Caption = CInt(Nz(strText1, 0))
            Me("TCount" & intCount1).visible = True
            'Calculate subject %A*C
            strText2 = DLookup("[%A*-C]", "LeaversGCSESubjectSummary", strCriteria)
            'Me("TSum" & intCount1).Left = (7.1 + (0.35 * intCount1)) * TwipsNumber
            Me("TSum" & intCount1).Left = (6.35 + (SGW * intCount1)) * TwipsNumber
            Me("TSum" & intCount1).Width = SGW * TwipsNumber 'CR v5116
            Me("TSum" & intCount1).Caption = CInt(Nz(strText2, 0))
            Me("TSum" & intCount1).visible = True
            '=========================
            
            intCount1 = intCount1 + 1
            'Me.LineHeader.Width = (7.1 + (0.35 * intCount1))
            Me.LineHeader.Width = (6.35 + (SGW * intCount1))
                    
            .MoveNext
            
        Loop
        Else
        'nothing
        End If
    
    .Close
    End With
    
    'create In statement and append to StrSQL1 as the record source
    Flds = Left(Flds, Len(Flds) - 2) & ")"
    
    'Debug.Print Me.RecordSource
    
Exit_Handler:
    Exit Sub
   
Err_Handler:
    If Err.Number = 2465 Then
        MsgBox "This report does not have enough columns" & _
        " to display all " & NumberOfSubjects & " subjects         ", vbExclamation
        Exit Sub
    Else
         'create error message & log
        strProc = "Report_Open"
        PopulateErrorLog
        Resume Exit_Handler
    End If
   
End Sub