[AccessD] Code Behind Conditional Formatting

Karen,

    Programmatic manipulation of conditional formatting involves FormatConditions collection of the control in question.

    Maximum limit of three conditions at a time, continues to apply. However, for certain tasks, the level of flexibility and control offered, can make a code based solution the preferred alternative.

    Another interesting advantage - choice of colors need not be confined to the 40 pre-defined ones offered by the dialog box (invoked by clicking Format > Conditional Formatting in the standard menu bar). The whole spectrum offered by Access for the back color of its forms and text boxes (format tab on the properties dialog box), can be availed of. The variety of custom shades that can be formulated, is virtually unlimited.

    Sample code (in form's module) for dynamic manipulation of conditional formatting, is given below. Subroutine named P_SetFormat() can be used for setting up new formatting, while  P_EditFormat() can be used for editing the existing conditions. Desired color values can be substituted in statements (A) to (F).

Regards,
A.D.Tejpal
--------------

===============================
Private Sub P_SetFormat(ByVal Fnm As String)
' Sets fresh Conditional Formatting in 
' text box named Fnm
    Dim Cnt As Integer, Cdn As String, Bkc As Long
    
    With Me(Fnm).FormatConditions
        .Delete
        For Cnt = 1 To 3
            Select Case Cnt
                Case 1
                    Cdn = "[" & Fnm & "] < 0"
                    Bkc = 14001649    ' (A)
                Case 2
                    Cdn = "[" & Fnm & "] > 0 And [" & _
                                Fnm & "] <= 100"
                    Bkc = 10932206    ' (B)
                Case 3
                    Cdn = "[" & Fnm & "] > 100"
                    Bkc = 13434293    ' (C)
            End Select
            
            With .Add(acExpression, , Cdn)
                .BackColor = Bkc
                .FontBold = True
            End With
        Next
    End With
End Sub
-----------------------------------------------------

Private Sub P_EditFormat(ByVal Fnm As String)
' Edits existing Conditional Formatting in 
' text box named Fnm
    Dim Fct  As Integer, Bkc As Long, Cnt As Integer
    
    Fct = Me(Fnm).FormatConditions.Count
    If Fct = 0 Then
        Exit Sub
    End If
    
    For Cnt = 0 To Fct - 1
        Select Case Cnt
            Case 0
                Bkc = 14001649    ' (D)
            Case 1
                Bkc = 10932206    ' (E)
            Case 2
                Bkc = 13434293    ' (F)
        End Select
        
        Me(Fnm).FormatConditions(Cnt).BackColor = Bkc
    Next
End Sub
===============================

  ----- Original Message ----- 
  From: Nicholson, Karen 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, December 23, 2004 20:56
  Subject: RE: [AccessD] Code Behind Conditional Formatting


  And I want to make controls not visible if criteria is met.  Oh boy, what fun it is to ride...

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
  Sent: Thursday, December 23, 2004 10:12 AM
  To: 'Access Developers discussion and problem solving'
  Subject: RE: [AccessD] Code Behind Conditional Formatting

  Karen,

  The current Smart Access Newsletter has a good article about this:

  http://www.smartaccessnewsletter.com/ME2/Audiences/default.asp

  I haven't used code for conditional formatting, but I probably should for two reasons. 

  Most importantly, for complex conditions, you can trap errors and step through the code to verify correctness.  If you only use the conditional format screen, the screen simply displays whatever it can and errors can't be trapped.  Guessing what went wrong gets pretty tedious.

  Secondly, the conditional format screen only allow 3 conditions.  In code you can set up as many as you want.

  HTH,
  Dan Waters
  ProMation Systems


  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nicholson, Karen
  Sent: Thursday, December 23, 2004 8:38 AM
  To: Access Developers discussion and problem solving
  Subject: RE: [AccessD] Code Behind Conditional Formatting

  Lebans doesn't do everything I need.  In the meantime, I found this link which is interesting...

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k
  2/html/odc_conformat.asp

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
  Sent: Thursday, December 23, 2004 9:25 AM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Code Behind Conditional Formatting

  ..its built into A2K and above Karen but has limited functionality ...I find myself still using lebans conditional formatting functions (www.lebans.com) in most cases because it gives me absolute control and significantly more functionality.

  William Hindman


  ----- Original Message ----- 
  From: "Nicholson, Karen" <cyx5 at cdc.gov>
  To: <accessd at databaseadvisors.com>
  Sent: Thursday, December 23, 2004 6:31 AM
  Subject: [AccessD] Code Behind Conditional Formatting

  Time check: 6:28 am.  I am using conditional formatting (continuous forms) , a great little feature but it has its limitations.  The code controlling the conditional formatting must be somewhere, but where?

  Wishing everyone of every religion, except certain grinches (and you know who you are) , a safe and happy holiday season.