Conditional Formatting - Date function not consistent 2

[flize]

Problem: Coding conditional formats returns different (inaccurate) results then when using the built in conditional formatting functionality (via the menu).

In Access on a datasheet form, when I create multiple formatting conditions for a field using the 'Conditional Formatting...' option on the field's menu, all the conditions work as expected. However, when I code the same conditions in the On Open event, it only responds(changes font color) to the 'GreaterThan' condition. This seems to only impact a Date field where the conditions are comparing the field's date to the date condition (today's date - using the DATE function).

For example, using the menu option to format the conditions, I create these 2 conditions:

Condition 1: Field Value Is : Greater Than : Date()
If condition 1 is met, turn font color Yellow.
Condition 2: Field Value Is : Less Than : Date()
If condition 2 is met, turn font color Red.

Each condition changes the text color (Yellow or Red) for fields with date values 'Greater Than' or 'Less Than' the current date. This all works fine since I added conditions directly in the field itself.

Now, I want to change the conditions using code instead. I've coded the 'On Open' event of the datasheet form to delete the pre-existing conditions, then add new conditions. The problem is that when it runs, it changes ALL every records field colors to the 'greater than' condition color (Blue in this case) and none to the 'less than' condition even though several records meet the 'less than' criteria condition. I've tried rearranging the condition order, using actual dates (ie, #08/24/05#) instead of the DATE function, but nothing works for any other date conditional formatting except when I use the 'Greater Than' operator!

Here is the code in the On Open event:

Private Sub Form_Open(Cancel As Integer)
Dim objFrc As FormatCondition

[fldFormat].FormatConditions.Delete

Set objFrc = Me![fldFormat].FormatConditions.Add(acFieldValue, acLessThan, Date)

Me![fldFormat].FormatConditions(0).ForeColor = 4227072 ' green

Set objFrc = Me![fldFormat].FormatConditions.Add(acFieldValue, acGreaterThan, Date)

Me![fldFormat].FormatConditions(1).ForeColor = 16711680 ' blue

End Sub


The form opens with no errors. Even though both conditions are there for 'Greater Than' or 'Less Than', it paints ALL records date values BLUE which is the 'Greater Than' condition even though several records don't meet that condition!

Also, I tried the same operator using a text field and again, only the 'Greater Than' condition format is recognized and all records 'appear' to meet only that condition, not any others ??

Any idea's??

Thanks, flize

 

[BSman]

You're out of luck. You can't change the properties of a single row's text box using code on a form. That's why the conditional formatting capability was added in Access 2000.

As you've discovered, when you try to change, for example, the background color of a text box on a continuous form based on it's value in the current row, ALL instances of that text box displayed on the form will change. Go to another row and do whatever triggers your code (which could be in the OnCurrent property of the form), and you can see them all change to a different color depending on the value in the current row.

It's very frustrating, but as far as I know, there's no way around it using code.

Bob

 

[tbclbd]

I once wanted to do this, found help at

http://www.lebans.com.

You can download a zip file with code and samples in it which you can integrate into your code and then modify to suit your needs. When you get onto the website, search for a zip file called A2KCondtional

 

[TheAceMan1]

How are ya flize . . . . .

Dates get a little picky depending how their used. Try the following:

[blue]   Dim FCS As FormatConditions, FC As FormatCondition
   
   Set FCS = Me!fldFormat.FormatConditions
   FCS.FormatConditions.Delete [green]' Delete all format conditions[/green]
  
   Set FC = FCS.Add(acFieldValue, acLessThan, [purple][b]"#"[/b][/purple] & Date & [purple][b]"#"[/b][/purple])
   FC.ForeColor = 4227072 [green]' green[/green]
  
   Set FC = FCS.Add(acFieldValue, acGreaterThan, [purple][b]"#"[/b][/purple] & Date & [purple][b]"#"[/b][/purple])
   FC.ForeColor = 16711680 [green]' blue[/green]
   
   Set FC = Nothing
   Set FCS = Nothing[/blue]

[purple]To hilite an entire row, set the other controls to the same format conditions . . .[/purple]

See Ya! . . . . . .

 

[flize]

Thanks AceMan1 - that code did the trick!

One minor error in the code with the line: FCS.FormatConditions.Delete

It should just be:
FCS.Delete

since FCS was already set as FormatConditions


Also, I have a couple other 'easy' question(s) for you if you don't mind (regarding a couple lines of your code).

What happens (memory penalty, etc.) when you don't set the objects (like the formatcondition ojects) back to "Nothing" at the end of the sub? I normally do this, but sometimes I forget. And is the problem worse if a form like this (using the On Open event) is re-opened and closed often in a session?

Thanks again, flize

 

[TheAceMan1]

[blue]It should just be:
FCS.Delete[/blue]

Correct! I forgot to copy the modified line here.

[blue]What happens (memory penalty, etc.) when you don't set the objects (like the formatcondition ojects) back to "Nothing" at the end of the sub?[/blue]

In the code, put the cursor on [blue]Nothing[/blue] and hit F1! . . . do the same for [blue]Set[/blue].

See Ya! . . . . . .