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!
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.
13 2252 NeoPa 32,556
Expert Mod 16PB
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.
You’ll have to wait until next week for a DB. Talk to you then. Thanks!
NeoPa 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
Thanks, friend!
Nothing in that DB, so it can stay.
Hope your holiday season is going well!
NeoPa 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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 FormatCondition s and a default format ;-)
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
I've updated the title to make it clearer what to expect of the thread for those searching for a similar problem.
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 32,556
Expert Mod 16PB 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jouke Langhout |
last post by:
Hello all!
For quite some time now, I've got the following problem:
Access won't close properly when a user closes the application. An
ACCESS process stays active and that process can only be...
|
by: Andante.in.Blue |
last post by:
Hello everyone! I'm building a form in Access 97 and I want to to
show a special icon if the record has been marked as deleted.
Obviously, I don't want the icon to show if the record was not...
|
by: Bradley |
last post by:
I have an A2000 database in which I have a continuous form with a tick
box. There is also a text box with a conditional format that is based on
the expression , if it's true then change the...
|
by: Dimitri Furman |
last post by:
Given:
Access 2002/2003
A subform in datasheet or continuous view, placed on a tab page (this last
may or may not matter)
Conditional formatting applied to some controls on the subform -
format...
|
by: Andrew Chanter |
last post by:
Does anyone know a way you can use conditional formatting to create a banded
style view as is commonly seen on the internet. (In othe words the first
record appears on a gray background, the 2nd...
|
by: jodyblau |
last post by:
I'm not certain that what I am trying to do is possible; in any event I
haven't been able to figure it out. Here is what I am trying to do:
I have one table that has a list of cases I'm working...
|
by: Typehigh |
last post by:
I have many text fields with conditional formatting applied,
specifically when the condition is "Field Has Focus". Without any
events associated with the fields the conditional formatting works...
|
by: tg989 |
last post by:
Okay, here goes.
Continuous forms... possibly the best feature of access. The only downside is this: the only way to have a conditional evaluation run on each record invididually is by...
|
by: slinky |
last post by:
Thanks in advance... I have a continuous style form with a field for
each record called "STATUS". I simply want to have the form load and
if the value of the textbox is "Inactive" I want the...
|
by: Lyn |
last post by:
Hi,
I would like to make a bound text box not visible if it is empty (not just
disable it). This option is not available from the standard conditional
formatting feature (at least, not that I can...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |