Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

problem extending the FormatConditions

Posted on 2005-05-04
16
Medium Priority
?
313 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:ironpen45
  • 9
  • 7
16 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 13925069
Hi ironpen45,
What is txtStatnumber? Is it unbound?

Pete
0
 

Author Comment

by:ironpen45
ID: 13926694
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
 
LVL 77

Expert Comment

by:peter57r
ID: 13926752
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ironpen45
ID: 13926877
i tried on both Form_Open and Form_Load. and i get the same result.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 13927135
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
 

Author Comment

by:ironpen45
ID: 13927221
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
 

Author Comment

by:ironpen45
ID: 13927272
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
 
LVL 77

Expert Comment

by:peter57r
ID: 13927343
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
 

Author Comment

by:ironpen45
ID: 13927380
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
 
LVL 77

Expert Comment

by:peter57r
ID: 13927482
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
 

Author Comment

by:ironpen45
ID: 13927640
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
 
LVL 77

Expert Comment

by:peter57r
ID: 13928004
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
 

Author Comment

by:ironpen45
ID: 13929609
so are u saying that am i limited to just 3 conditions then?
0
 

Author Comment

by:ironpen45
ID: 13930191
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
 
LVL 77

Accepted Solution

by:
peter57r earned 1050 total points
ID: 13933340
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
 

Author Comment

by:ironpen45
ID: 13946300
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question