Is it Possible to Cycle Through Properties of a FormatCondition?

[twinnyfo]

Friends,

This might be relatively easy, or relatively impossible.

I am trying to find all the Text Boxes and Combo Boxes that have conditionial formatting applied to them. That, in itself, is easy enough.

However, there appears to be no way to simply run through the list of Conditional Formatting properties. For example, when I cycle through properties for a Control, the language might be:

For Each prp in ctl.Properties
    'Do what you wish here
Next prp

But with Conditional Formatting, it appears this is the only way to work through it:

Public Sub FindCF()
    Dim db          As DAO.Database
    Dim appAccess   As Access.Application
    Dim frm         As Object
    Dim frmObj      As Access.Form
    Dim ctl         As Control
    Dim objTB       As TextBox
    Dim fc          As FormatCondition
 
    Set db = CurrentDb()
    Set appAccess = Access.Application
    For Each frm In db.Containers("Forms").Documents
        With appAccess.DoCmd
            Call .OpenForm(FormName:=frm.Name, _
                           View:=acDesign)
        End With
        Set frmObj = appAccess.Forms(frm.Name)
        For Each ctl In frmObj.Controls
            If ctl.ControlType = 109 Then
                Set objTB = ctl
                If Not objTB.FormatConditions.Count = 0 Then
                    Debug.Print "Control Name: " & ctl.Name
                    For Each fc In objTB.FormatConditions
                        Debug.Print "BackColor: " & fc.BackColor
                        Debug.Print "Enabled: " & fc.Enabled
                        Debug.Print "Expression1: " & fc.Expression1
                        Debug.Print "Expression2: " & fc.Expression2
                        Debug.Print "FontBold: " & fc.FontBold
                        Debug.Print "FontItalic: " & fc.FontItalic
                        Debug.Print "FontUnderline: " & fc.FontUnderline
                        Debug.Print "ForeColor: " & fc.ForeColor
                        Debug.Print "LongestBarLimit: " & fc.LongestBarLimit
                        Debug.Print "LongestBarValue: " & fc.LongestBarValue
                        Debug.Print "Operator: " & fc.Operator
                        Debug.Print "ShortestBarLimit: " & fc.ShortestBarLimit
                        Debug.Print "ShortestBarValue: " & fc.ShortestBarValue
                        Debug.Print "ShowBarOnly: " & fc.ShowBarOnly
                        Debug.Print "Type: " & fc.Type
                    Next fc
                End If
                Set objTB = Nothing
            End If
        Next ctl
        With appAccess.DoCmd
            Call .Close(ObjectType:=acForm, _
                        ObjectName:=frm.Name, _
                        Save:=acSaveNo)
        End With
    Next frm
    Set appAccess = Nothing
    Set db = Nothing
End Sub

Any suggestions? If it can't be done, that is also an acceptable answer, as I am able to use the current method for my purposes--it's just a little clumsy for my tastes.

Thanks for the hepp!

[NeoPa]

Do you have a database I can play with in 2010 accessible format.

I feel confident I can find some answers for you but I have nothing to try it on. Even a very cut-down version would be fine without any real data in it. Just to do some playing around with.

[twinnyfo]

You’ll have to wait until next week for a DB. Talk to you then. Thanks!

[NeoPa]

No worries. No rush :-)

[twinnyfo]

NeoPa,

Attached is one such report in which several text boxes have multiple Conditional Formatting conditions (all Text Boxes ending with "Pct".

This DB is "not workable" as it only contains the report itself, but assessment of the Conditional Formatting is possible.

Thanks for your review.

[NeoPa]

Thanks for that Twinny.

I'm out of time for now but will try to look into it later in the week.

PS. I have the database now so feel free to remove it if you don't want it shared generally.

[twinnyfo]

Thanks, friend!

Nothing in that DB, so it can stay.

Hope your holiday season is going well!

[NeoPa]

I've looked into this now Twinny.

It seems that your fundamental question of why can't you list through the .Properties of any Control has the simple answer that Conditional Formatting is not handled within Access as Properties, but rather as a completely separate Collection altogether (FormatConditions). Is that really what you were after? It seems you already knew that.

In short - it seems that, while your original requirement is clearly impossible, something very similar does exactly the same :

For Each fc in ctl.FormatConditions
    'Do what you wish here
Next fc

Is there something else you were after or is that helpful?

[NeoPa]

Something about your database kept playing SillyB*ggers with me and refused to save my code so I had to create a new one and type it all out again.

Nevertheless, I put together some code that I think illustrates the simplicity of the concept. Obviously you can include what you choose of the information but I found my reduced list helpful as an overview. Note that I used the index approach rather than the For ... Each one. This was so that we could also list the index value for each Control.

Option Compare Database
Option Explicit
 
'ListFC() lists all FormatConditions for all Controls in a Form/Report object.
Public Sub ListFC(objVar As Object)
    Dim lngIx As Long
    Dim ctlVar As Control
 
    If (Not TypeOf objVar Is Form) _
    And (Not TypeOf objVar Is Report) Then _
        Exit Sub
    For Each ctlVar In objVar.Controls
        If HasFC(ctlVar) Then
            For lngIx = 0 To ctlVar.FormatConditions.Count - 1
                With ctlVar.FormatConditions(lngIx)
                    Debug.Print Replace("Ctl Name=%CN;", "%CN", ctlVar.Name);
                    Debug.Print Replace(" Index=%IX;", "%IX", lngIx);
                    Debug.Print Replace(" Op=%OP;", "%OP", .Operator);
                    Debug.Print Replace(" Type=%TY;", "%TY", .Type);
                    Debug.Print Replace(" Exp1=%X1;", "%X1", .Expression1);
                    If .Operator < 2 Then _
                        Debug.Print Replace(" Exp2=%X2;" _
                                          , "%X2", CStr(.Expression2));
                    Debug.Print Replace(" ForeColor=%FC;", "%FC", .ForeColor);
                    Debug.Print Replace(" BackColor=%BC;", "%BC", .BackColor);
                    Debug.Print Replace(" Enabled=%EN", "%EN", .Enabled)
                End With
            Next lngIx
        End If
    Next ctlVar
End Sub
 
'HasFC() returns True if ctlVar has a FormatConditions Collection.
Private Function HasFC(ctlVar As Control) As Boolean
    Dim lngVar As Long
 
    On Error Resume Next
    lngVar = ctlVar.FormatConditions.Count
    HasFC = (Err.Number = 0)
End Function

Ctl Name=txtReceivedPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtReceivedPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtReceivedPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtPRFReviewPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtPRFReviewPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtPRFReviewPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtA1KPReviewPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtA1KPReviewPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtA1KPReviewPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtDiscSentPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtDiscSentPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtDiscSentPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtCompletePct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtCompletePct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtCompletePct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtBulletCheckersPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtBulletCheckersPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtBulletCheckersPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtImpPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtImpPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtImpPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalReceivedPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalReceivedPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalReceivedPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalPRFReviewedPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalPRFReviewedPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalPRFReviewedPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalA1KPReviewPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalA1KPReviewPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalA1KPReviewPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalDiscSentPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalDiscSentPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalDiscSentPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalCompletePct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalCompletePct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalCompletePct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalBulletCheckersPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalBulletCheckersPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalBulletCheckersPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
Ctl Name=txtTotalImpPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
Ctl Name=txtTotalImpPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
Ctl Name=txtTotalImpPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True

Bear in mind, for such logic, you only need two FormatConditions and a default format ;-)

[twinnyfo]

NeoPa,

From your post in #8, you will see that is exactly what I have in the original block of Code. However, if you look at lines 24-38 of that original block of Code, there appears to be no way to gather those values than to list each one out separately. One must specifically ask for the value of .BackColor to get that value.

I would have expected something like this:

For Each fc in ctl.FormatConditions
    For Each prp in fc.Properties
        'Do what I wish
    Next prp
Next fc

But, there is no "Property" or other characteristic that can be run through, like other objects. This may just be something unavailable in Access (at this time, anyway).

[NeoPa]

Ah. I see what you're getting at now. No.

Forms and Controls do, indeed, have Properties Collections, and Controls certainly even have a FormatConditions Collection, but a FormatCondition object doesn't have a Properties Collection, no. That's not too unusual TBF. Most objects don't. It's only the main Access Objects that do as far as I can see.

To check this sort of thing out for yourself simply use F2 from the VBAIDE to open the Object Browser and select Access from the Libraries ComboBox and then type "FormatCondition" into the search ComboBox. The results should include an entry for Library=Access & Class=FormatCondition. Click on that then click on FormatCondition in the Classes box to review all the possible (visible) properties. There is none called Properties :-(

Even if you show the hidden members (Right-click | Show Hidden Members) it's not there.

[NeoPa]

I've updated the title to make it clearer what to expect of the thread for those searching for a similar problem.

[twinnyfo]

Thanks Brother!

I was expecting this result, as I had been through all the surface possibilities. I was just hoping that maybe you knew of some magical “behind the curtain” method to run through these in one line of code.

However, what I have works, and it is fairly straightforward. Just not as neat as I would have desired.

Thanks for the hepp!

P.S. Even though it is not a “solution”, I’ve marked your response as the best answer, as it does answer the question if it is possible.

[NeoPa]

TwinnyFo:
P.S. Even though it is not a “solution”, I’ve marked your response as the best answer, as it does answer the question if it is possible.

And that's exactly why that's appropriate to do. Others looking for the same help can peruse the thread if they choose, but will see immediately the precise answer they need first.