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].FormatCo nditions.D elete
Set objFrc = Me![txtSTATCOLOR].FormatCo nditions.A dd(acExpre ssion, , "[txtSTATUS] = 'CLOSED'")
Set objFrc = Me![txtSTATCOLOR].FormatCo nditions.A dd(acExpre ssion, , "[txtSTATUS] = 'OPEN'")
Set objFrc = Me![txtSTATCOLOR].FormatCo nditions.A dd(acExpre ssion, , "[txtSTATUS] = 'UNASSND'")
With Me![txtSTATCOLOR].FormatCo nditions(0 )
.BackColor = vbGreen
End With
With Me![txtSTATCOLOR].FormatCo nditions(1 )
.BackColor = vbRed
End With
With Me![txtSTATCOLOR].FormatCo nditions(2 )
.BackColor = vbMagenta
End With
Else
Me![txtSTATCOLOR].FormatCo nditions.D elete
Set objFrc = Me![txtSTATCOLOR].FormatCo nditions.A dd(acExpre ssion, , "[txtSTATUS] = 'APPT'")
Set objFrc = Me![txtSTATCOLOR].FormatCo nditions.A dd(acExpre ssion, , "[txtSTATUS] = 'CXLD'")
With Me![txtSTATCOLOR].FormatCo nditions(0 )
.BackColor = vbCyan
End With
With Me![txtSTATCOLOR].FormatCo nditions(1 )
.BackColor = vbBlue
End With
End If
Set objFrc = Nothing
End Sub
tia!
Private Sub Form_Open(Cancel As Integer)
Dim objFrc As FormatCondition
If Me!txtSTATNUMBER <= 3 Then
Me![txtSTATCOLOR].FormatCo
Set objFrc = Me![txtSTATCOLOR].FormatCo
Set objFrc = Me![txtSTATCOLOR].FormatCo
Set objFrc = Me![txtSTATCOLOR].FormatCo
With Me![txtSTATCOLOR].FormatCo
.BackColor = vbGreen
End With
With Me![txtSTATCOLOR].FormatCo
.BackColor = vbRed
End With
With Me![txtSTATCOLOR].FormatCo
.BackColor = vbMagenta
End With
Else
Me![txtSTATCOLOR].FormatCo
Set objFrc = Me![txtSTATCOLOR].FormatCo
Set objFrc = Me![txtSTATCOLOR].FormatCo
With Me![txtSTATCOLOR].FormatCo
.BackColor = vbCyan
End With
With Me![txtSTATCOLOR].FormatCo
.BackColor = vbBlue
End With
End If
Set objFrc = Nothing
End Sub
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!
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
The earliest you can do it is the Form_load event.
Pete
ASKER
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
Private Sub Form_load()
Dim objFrc As FormatCondition
msgbox "value of statnumber is:" & me.txtstatnumber
Pete
ASKER
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!
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!
ASKER
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
In that case you need to use the Form_current event.
I assumes that this is NOT a continuous form.
Pete
ASKER
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?
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
The rules that are applied to ALL visible records will be the rules that apply to the current (selected) record.
Pete
ASKER
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
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/sunk en 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
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/sunk
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
ASKER
so are u saying that am i limited to just 3 conditions then?
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
What is txtStatnumber? Is it unbound?
Pete