423,846 Members | 1,959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,846 IT Pros & Developers. It's quick & easy.

Is it Possible to Cycle Through Properties of a FormatCondition?

twinnyfo
Expert Mod 2.5K+
P: 2,703
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:

Expand|Select|Wrap|Line Numbers
  1. For Each prp in ctl.Properties
  2.     'Do what you wish here
  3. Next prp
But with Conditional Formatting, it appears this is the only way to work through it:

Expand|Select|Wrap|Line Numbers
  1. Public Sub FindCF()
  2.     Dim db          As DAO.Database
  3.     Dim appAccess   As Access.Application
  4.     Dim frm         As Object
  5.     Dim frmObj      As Access.Form
  6.     Dim ctl         As Control
  7.     Dim objTB       As TextBox
  8.     Dim fc          As FormatCondition
  9.  
  10.     Set db = CurrentDb()
  11.     Set appAccess = Access.Application
  12.     For Each frm In db.Containers("Forms").Documents
  13.         With appAccess.DoCmd
  14.             Call .OpenForm(FormName:=frm.Name, _
  15.                            View:=acDesign)
  16.         End With
  17.         Set frmObj = appAccess.Forms(frm.Name)
  18.         For Each ctl In frmObj.Controls
  19.             If ctl.ControlType = 109 Then
  20.                 Set objTB = ctl
  21.                 If Not objTB.FormatConditions.Count = 0 Then
  22.                     Debug.Print "Control Name: " & ctl.Name
  23.                     For Each fc In objTB.FormatConditions
  24.                         Debug.Print "BackColor: " & fc.BackColor
  25.                         Debug.Print "Enabled: " & fc.Enabled
  26.                         Debug.Print "Expression1: " & fc.Expression1
  27.                         Debug.Print "Expression2: " & fc.Expression2
  28.                         Debug.Print "FontBold: " & fc.FontBold
  29.                         Debug.Print "FontItalic: " & fc.FontItalic
  30.                         Debug.Print "FontUnderline: " & fc.FontUnderline
  31.                         Debug.Print "ForeColor: " & fc.ForeColor
  32.                         Debug.Print "LongestBarLimit: " & fc.LongestBarLimit
  33.                         Debug.Print "LongestBarValue: " & fc.LongestBarValue
  34.                         Debug.Print "Operator: " & fc.Operator
  35.                         Debug.Print "ShortestBarLimit: " & fc.ShortestBarLimit
  36.                         Debug.Print "ShortestBarValue: " & fc.ShortestBarValue
  37.                         Debug.Print "ShowBarOnly: " & fc.ShowBarOnly
  38.                         Debug.Print "Type: " & fc.Type
  39.                     Next fc
  40.                 End If
  41.                 Set objTB = Nothing
  42.             End If
  43.         Next ctl
  44.         With appAccess.DoCmd
  45.             Call .Close(ObjectType:=acForm, _
  46.                         ObjectName:=frm.Name, _
  47.                         Save:=acSaveNo)
  48.         End With
  49.     Next frm
  50.     Set appAccess = Nothing
  51.     Set db = Nothing
  52. 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!
2 Weeks Ago #1

✓ answered by 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.

Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,121
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.
2 Weeks Ago #2

twinnyfo
Expert Mod 2.5K+
P: 2,703
You’ll have to wait until next week for a DB. Talk to you then. Thanks!
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,121
No worries. No rush :-)
2 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 2,703
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.
Attached Files
File Type: zip Conditional Formatting.zip (26.2 KB, 5 views)
1 Week Ago #5

NeoPa
Expert Mod 15k+
P: 31,121
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.
1 Week Ago #6

twinnyfo
Expert Mod 2.5K+
P: 2,703
Thanks, friend!

Nothing in that DB, so it can stay.

Hope your holiday season is going well!
1 Week Ago #7

NeoPa
Expert Mod 15k+
P: 31,121
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 :
Expand|Select|Wrap|Line Numbers
  1. For Each fc in ctl.FormatConditions
  2.     'Do what you wish here
  3. Next fc
Is there something else you were after or is that helpful?
5 Days Ago #8

NeoPa
Expert Mod 15k+
P: 31,121
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'ListFC() lists all FormatConditions for all Controls in a Form/Report object.
  5. Public Sub ListFC(objVar As Object)
  6.     Dim lngIx As Long
  7.     Dim ctlVar As Control
  8.  
  9.     If (Not TypeOf objVar Is Form) _
  10.     And (Not TypeOf objVar Is Report) Then _
  11.         Exit Sub
  12.     For Each ctlVar In objVar.Controls
  13.         If HasFC(ctlVar) Then
  14.             For lngIx = 0 To ctlVar.FormatConditions.Count - 1
  15.                 With ctlVar.FormatConditions(lngIx)
  16.                     Debug.Print Replace("Ctl Name=%CN;", "%CN", ctlVar.Name);
  17.                     Debug.Print Replace(" Index=%IX;", "%IX", lngIx);
  18.                     Debug.Print Replace(" Op=%OP;", "%OP", .Operator);
  19.                     Debug.Print Replace(" Type=%TY;", "%TY", .Type);
  20.                     Debug.Print Replace(" Exp1=%X1;", "%X1", .Expression1);
  21.                     If .Operator < 2 Then _
  22.                         Debug.Print Replace(" Exp2=%X2;" _
  23.                                           , "%X2", CStr(.Expression2));
  24.                     Debug.Print Replace(" ForeColor=%FC;", "%FC", .ForeColor);
  25.                     Debug.Print Replace(" BackColor=%BC;", "%BC", .BackColor);
  26.                     Debug.Print Replace(" Enabled=%EN", "%EN", .Enabled)
  27.                 End With
  28.             Next lngIx
  29.         End If
  30.     Next ctlVar
  31. End Sub
  32.  
  33. 'HasFC() returns True if ctlVar has a FormatConditions Collection.
  34. Private Function HasFC(ctlVar As Control) As Boolean
  35.     Dim lngVar As Long
  36.  
  37.     On Error Resume Next
  38.     lngVar = ctlVar.FormatConditions.Count
  39.     HasFC = (Err.Number = 0)
  40. End Function
Expand|Select|Wrap|Line Numbers
  1. Ctl Name=txtReceivedPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  2. Ctl Name=txtReceivedPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  3. Ctl Name=txtReceivedPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  4. Ctl Name=txtPRFReviewPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  5. Ctl Name=txtPRFReviewPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  6. Ctl Name=txtPRFReviewPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  7. Ctl Name=txtA1KPReviewPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  8. Ctl Name=txtA1KPReviewPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  9. Ctl Name=txtA1KPReviewPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  10. Ctl Name=txtDiscSentPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  11. Ctl Name=txtDiscSentPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  12. Ctl Name=txtDiscSentPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  13. Ctl Name=txtCompletePct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  14. Ctl Name=txtCompletePct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  15. Ctl Name=txtCompletePct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  16. Ctl Name=txtBulletCheckersPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  17. Ctl Name=txtBulletCheckersPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  18. Ctl Name=txtBulletCheckersPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  19. Ctl Name=txtImpPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  20. Ctl Name=txtImpPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  21. Ctl Name=txtImpPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  22. Ctl Name=txtTotalReceivedPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  23. Ctl Name=txtTotalReceivedPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  24. Ctl Name=txtTotalReceivedPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  25. Ctl Name=txtTotalPRFReviewedPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  26. Ctl Name=txtTotalPRFReviewedPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  27. Ctl Name=txtTotalPRFReviewedPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  28. Ctl Name=txtTotalA1KPReviewPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  29. Ctl Name=txtTotalA1KPReviewPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  30. Ctl Name=txtTotalA1KPReviewPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  31. Ctl Name=txtTotalDiscSentPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  32. Ctl Name=txtTotalDiscSentPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  33. Ctl Name=txtTotalDiscSentPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  34. Ctl Name=txtTotalCompletePct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  35. Ctl Name=txtTotalCompletePct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  36. Ctl Name=txtTotalCompletePct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  37. Ctl Name=txtTotalBulletCheckersPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  38. Ctl Name=txtTotalBulletCheckersPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  39. Ctl Name=txtTotalBulletCheckersPct; Index=2; Op=5; Type=0; Exp1=1; ForeColor=0; BackColor=5026082; Enabled=True
  40. Ctl Name=txtTotalImpPct; Index=0; Op=5; Type=0; Exp1=0.5; ForeColor=0; BackColor=2366701; Enabled=True
  41. Ctl Name=txtTotalImpPct; Index=1; Op=5; Type=0; Exp1=0.9; ForeColor=0; BackColor=62207; Enabled=True
  42. 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 ;-)
5 Days Ago #9

twinnyfo
Expert Mod 2.5K+
P: 2,703
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:

Expand|Select|Wrap|Line Numbers
  1. For Each fc in ctl.FormatConditions
  2.     For Each prp in fc.Properties
  3.         'Do what I wish
  4.     Next prp
  5. 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).
3 Days Ago #10

NeoPa
Expert Mod 15k+
P: 31,121
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.
3 Days Ago #11

NeoPa
Expert Mod 15k+
P: 31,121
I've updated the title to make it clearer what to expect of the thread for those searching for a similar problem.
3 Days Ago #12

twinnyfo
Expert Mod 2.5K+
P: 2,703
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.
2 Days Ago #13

NeoPa
Expert Mod 15k+
P: 31,121
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.
2 Days Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.