problem extending the FormatConditions

i want to extend the limitations of the FormatConditions collection. so, i wrote this code. the first 3 formatconditions of the IF condition work, but the formatconditions under the ELSE don't work. can anyone shed light why? don't see the need for CASE so i went with the IF.ELSE.END IF direction.

tia!

Private Sub Form_Open(Cancel As Integer)
    Dim objFrc As FormatCondition
   
    If Me!txtSTATNUMBER <= 3 Then
        Me![txtSTATCOLOR].FormatConditions.Delete
        Set objFrc = Me![txtSTATCOLOR].FormatConditions.Add(acExpression, , "[txtSTATUS] = 'CLOSED'")
        Set objFrc = Me![txtSTATCOLOR].FormatConditions.Add(acExpression, , "[txtSTATUS] = 'OPEN'")
        Set objFrc = Me![txtSTATCOLOR].FormatConditions.Add(acExpression, , "[txtSTATUS] = 'UNASSND'")
       
        With Me![txtSTATCOLOR].FormatConditions(0)
            .BackColor = vbGreen
        End With
            With Me![txtSTATCOLOR].FormatConditions(1)
            .BackColor = vbRed
        End With
        With Me![txtSTATCOLOR].FormatConditions(2)
            .BackColor = vbMagenta
        End With
    Else
        Me![txtSTATCOLOR].FormatConditions.Delete
        Set objFrc = Me![txtSTATCOLOR].FormatConditions.Add(acExpression, , "[txtSTATUS] = 'APPT'")
        Set objFrc = Me![txtSTATCOLOR].FormatConditions.Add(acExpression, , "[txtSTATUS] = 'CXLD'")
       
        With Me![txtSTATCOLOR].FormatConditions(0)
            .BackColor = vbCyan
        End With
        With Me![txtSTATCOLOR].FormatConditions(1)
            .BackColor = vbBlue
        End With
    End If
   
    Set objFrc = Nothing
End Sub
ironpen45Asked:
Who is Participating?
 
peter57rCommented:
Your results illustrate exactly what I have said.

For these results the current record must be one of the first 8
When the current record has a value of <= 3 the whole list of records is based on the red/green/magenta set of options.(The white results are the 'unformatted' option which you get for free..)  The blue and cyan options are not operative because the current record does not fall into that half of the 'if' condition.

However, if you click into one of the last 2 records, you will get the blue and cyan options showing but all the others will be white.

Yes you can only have 3 conditional formats operating at any time.

Pete

0
 
peter57rCommented:
Hi ironpen45,
What is txtStatnumber? Is it unbound?

Pete
0
 
ironpen45Author Commented:
txtSTATNUMBER is another textbook with actual data, so it's also bound. i tested the reverse condition where

If Me!txtSTATNUMBER > 3 Then

and the first 3 FormatConditions are ignored, but the remaining 2 under ELSE do work!

please help! thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
peter57rCommented:
You cannot test data values in the Form_open event.  No recordsource data is present at that point.
The earliest you can do it is the Form_load event.

Pete
0
 
ironpen45Author Commented:
i tried on both Form_Open and Form_Load. and i get the same result.
0
 
peter57rCommented:
I suggest you put a msgbox in to see what is happening.

Private Sub Form_load()
    Dim objFrc As FormatCondition

msgbox "value of statnumber is:" & me.txtstatnumber

Pete
0
 
ironpen45Author Commented:
well it returns 2. but what's the point? the source of data is a query, and the order of values for status is:

STATUS
4
2
3
2
1
1
1
1
5
4

when the subform opens, those records w/ values 4 and 5 (first and 2nd to the last, and last records) don't behave w/ the formatcondition. thanks!
0
 
ironpen45Author Commented:
just noticed something funny: if the first record has a STATUS value of 3 or less, then the following records under the same conditions are formatted. but if the first record has a STATUS value of 4 or greater, then the reverse occurs! is this making any sense?
0
 
peter57rCommented:
It sounds like you want to chnage the formatting rules on a record by record basis.
In that case you need to use the Form_current event.
I assumes that this is NOT a continuous form.

Pete
0
 
ironpen45Author Commented:
it is a continuous form. i tried every form event, i get the same result. it's gotta be the IF..ELSE..END IF condition.

does formatcondition not work w/ IF..ELSE..END IF? does it behave better w/ CASE?
0
 
peter57rCommented:
You cannot have different formatting rules applying to different records in a continuous form.
The rules that are applied to ALL visible records will be the rules that apply to the current (selected) record.

Pete
0
 
ironpen45Author Commented:
based on various sources, i don't you're correct. here's an example.

http://blogs.msdn.com/frice/archive/2004/06/08/151178.aspx
0
 
peter57rCommented:
Your various sources are incorrect I'm afraid.

You can do what your example shows provided you are using single forms.
But formatting of ALL aspects of a continuous form is based on the formatting applied to the current record.(whatever you do to any control in a continuous form is applied to all visible occurrences of that control -  control visible/enabled/green/sunken etc)

So you can apply different conditional formatting rules for each record in a single form but for a continuous form, the conditional formatting rules which currently prevail for ALL visible records  are those you have applied to the current record.  

Pete
0
 
ironpen45Author Commented:
so are u saying that am i limited to just 3 conditions then?
0
 
ironpen45Author Commented:
also, i'm using continuous forms. w/ the current code, the 10 records show the following colors:

STATUS CELLCOLOR
3           magenta
2           green
3           magenta
2           green
1           red
1           red
1           red
1           red
5           white (default color, supposed to be blue)
4           white (default color, supposed to be cyan)

so, one of us is not understanding what's going on, cuz IT IS WORKING w/ continuous forms. thanks pete, but is there anyone else who can shed light here?
0
 
ironpen45Author Commented:
thanks pete. i still think there's an issue w/ the IF..ELSE..END IF which renders the conditional formatting a limitation to 3. but since no else has participated in the discussion over 2 days, points to you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.