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.