[AccessD] Code Behind Conditional Formatting

A.D.Tejpal adtp at touchtelindia.net
Thu Dec 23 12:00:35 CST 2004


    You are most welcome  Karen!

A.D.Tejpal
--------------

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


  Thank you!  This is great, a whole new world.  I hit the color problem almost immediately, I wanted to make it the default color or transparent and it was not there.

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
  Sent: Thursday, December 23, 2004 12:11 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [AccessD] Code Behind Conditional Formatting

  Karen,

      Programmatic manipulation of conditional formatting at run time is handled via FormatConditions Collection of the control in question.

      Maximum limit of 3 conditions at a time continues to hold good in code based solution as well. However, it enables a level of flexibility and control, that may be vital for certain tasks.

      Another interesting advantage of code based approach is that you are no longer confined to the limited choice of colors offered by the routine dialog box for conditional formatting. The whole spectrum of virtually unlimited color combinations is available to choose from.

    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.




More information about the AccessD mailing list