View conditional formatting values outside of dialog box

[dcdimon]

I'm looking to see if it's possible to view the values of a conditional format that was set up via the conditional format dialog box via VBA or something similar. There are several forms with conditional formats for when a reading is out of spec and I'd like to be able to get that range programmatically from the conditional format.Because I haven't found a single thing that even hints that this is do-able, I'm beginning to think that it's not. However, before i go and try to redo the formatting via a VBA process (using a lookup table of the min/max tables on all 50 forms) I thought I'd ask the wizards on this forum. Anyone?Fingers crossed.DD

[June7]

It is possible to programmatically set Conditional Formatting properties. This article concerned pre-2010 Access http://blogs.msdn.com/b/frice/archiv...08/151178.aspx

I suppose can read the existing property settings as well. Why exactly would you need to?

[rpeare]

Here's some code that goes through conditional formatting information:

http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

[ItsMe]

Because the formatting does not happen until after a condition is met you would have to write VBA to observe the condition, regardless of what acction the VBA takes.

Write code to see if conditional formatting will be implemented.
Write code to format conditionaly.

I see the same amount of coding either way. Perhaps more coding to determine what the user decided to do with the format window thing. (Not too sure what all you are trying to do here)

[dcdimon]

Because the formatting does not happen until after a condition is met you would have to write VBA to observe the condition, regardless of what acction the VBA takes.

Write code to see if conditional formatting will be implemented.
Write code to format conditionaly.

I see the same amount of coding either way. Perhaps more coding to determine what the user decided to do with the format window thing. (Not too sure what all you are trying to do here)

What I'm trying to do is use the conditions for the control so I can evaluate the entry in a query to determine if out of range. I was hoping that I wouldn't have to re-enter the hi/lo values in my script, but just refer to them from the conditional format, which is entered via the conditional formatting on the Access ribbon, not VBA.

When the forms were made, they weren't thinking about how we'd want to evaluate/see the data on the out-side. Looking back at this I think the best thing would have been to create a table that holds the hi/lo data by field name and have a script that looks up the values to set the control back color. Then I could use that table to evaluate the field data to see if it's out of compliance and populate a report/query/scorecard.

I've decided that the conditional formatting dialog box is internal to Access and the values (arguments) that are entered can't be seen outside of that dialog box. As this seems to be true I'm in the process of putting together a table for the values. I was just trying to avoid this.

However, even though I LOVE making data tables, if I'm incorrect I'd welcome knowing that. :P


DD

[rpeare]

I think you are far better off creating the tolerances in a table, then comparing that to your score (what you're on the road to doing) conditional formatting of values that never change will turn your database into a not so useful hunk of junk over time.

[ItsMe]

...I've decided that the conditional formatting dialog box is internal to Access and the values (arguments) that are entered can't be seen outside of that dialog box. As this seems to be true I'm in the process of putting together a table for the values. I was just trying to avoid this.

DD

Can't just do the calculation of historical data?
If you do not want to run the calculation again, store the High Low and refer to that for formatting and analyzing historical data

[June7]

In case anyone is interested. It is possible to view the Conditional Formatting settings, including the conditional expression. This worked for me:

Debug.Print Me.SectionName.FormatConditions(0).Expression1

[rpeare]

That's in the link I posted too