Link to home
Start Free TrialLog in
Avatar of ironpen45
ironpen45

asked on

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
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi ironpen45,
What is txtStatnumber? Is it unbound?

Pete
Avatar of ironpen45
ironpen45

ASKER

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!
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
i tried on both Form_Open and Form_Load. and i get the same result.
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
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!
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?
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
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?
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
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
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
so are u saying that am i limited to just 3 conditions then?
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?
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.