how to apply Conditional Formatting in run time

[selvakumar.arc]

Hi,
I have requirement to apply conditional formatting during run time. Please have a look into following example...
Id -- version -- Name
1 -- 0 -- A
1 -- 1 -- B
2 -- 1 -- C
2 -- 2 -- C

I have to show above data in datasheet and here I need to show only max 2 record for group based on Id and version.
After considering above example, if there is any change in the Name value based on Id & Version, I want to show different background color for that particular column.
After considering above example for Id 1, there is a change in Name field that is Name field changed from "A' to "B". So in my DataSheet view I have to add different background color for [Name] - "B" column.

please find code snippet that I done for identifing modified column.

rsLatestRules - contains latest rule information, after condiering above example it will contain following information > Id-1/Version-1/Name-B & Id-2/Version-2/Name-C
Do While Not rsLatestRules.EOF

sRuleId = rsLatestRules.Fields("ID")
sVersion = rsLatestRules.Fields("Version")

'rsProductRules contains above sample data.
Set rsPreviousRules = rsProductRules.Clone
Set rsCurrentRules = rsProductRules

rsPreviousRules.FindFirst ("RWID=" & sRuleId & " And Version<" & sVersion)
rsCurrentRules.FindFirst ("RWID=" & sRuleId & " And Version=" & sVersion)

Dim iCount As Integer
Dim ctl As Control
Dim frmProductRules As Form

'[ProductRules] is a subform in a main form. [ProductRules] form type is Datasheet view and main form is Form Type.
Set frmProductRules = [ProductRules].Form

'Looping through each control in the subform
For Each ctl In frmProductRules.Controls

Dim sColumnName As String
sColumnName = ctl.Name

If (InStr(sColumnName, "_Label") > 0) Then
'If Column name contain _Lable then it will not process...
Else
'if previous value and current value is not matching will fall into this condition. In the example, Id-1/Version-0 is previous rule and Id-1/Version-1 is current rule. So here I need to apply conditional format, that is in the current rule column I have to apply formatting because the value is changed from its previous version "A to B".
If rsPreviousRules.Fields(sColumnName) <> rsCurrentRules.Fields(sColumnName) Then
'MsgBox (sColumnName)
'below all are not supporting to apply font weight for particular column. I have to apply background color as well as font weight.


'rsCurrentRules.Fields(sColumnName).Properties("Fo ntWeight") = "Bold"
'ctl.Properties("FontWeight") = Bold
'frmProductRules.Controls(ctl.Name).FontWeight = "Bold"


''With rsCurrentRules![sColumnName].FormatConditions(0)
'' .FontBold = True
''End With

End If

End If

Next

rsLatestRules.MoveNext
Loop

If any one have idea how to apply conditional format for particular column in datasheet view, then please help me?

Regards,
Selvakumar R

[June7]

For form in continuous or datasheet view, run-time or otherwise is probably irrelevant. Setting these properties affects all instances of control on form. Might work on report. If the Conditional Format wizard will not serve then you may be out of luck.

[selvakumar.arc]

Thanks for your reply.
There is any other way to differentiate that particular column, "Name - B" ?

[June7]

Don't understand the question.

Note: 'Name' is not a good name for a field. I think 'Name' is a reserved word. Should not use reserved words as names.

[Missinglinq]

As June7 said, in a Datasheet View or Continuous View Form, formatting (except Locking/Disabling a Control) can only be done using Conditional Formatting, off of the Ribbon/Menu, for the stated reason he gave. Highly suspect that this is going to be a no-can-do!

Sorry!

Linq ;0)>

[selvakumar.arc]

Thanks for reply.
I think this cannot be accomplished using conditional format.
Here column name "Name" is given for an example, not an actual field.
My requirement is I have identify latest values from the datasheet based on ID. In above example, ID 1 have two version of data and its column (Name) value also changed from "A" to "B". So if there is any change from its previous data then I have to differentiate that particular column by changing its font color/font style etc.
I am not sure whether I can apply property to particular cell in datasheet view.

Once again, Thank you very much for your support and suggestion.

[June7]

That would be tricky. Getting value from another record of same table can be difficult. One way is with subquery. Review http://allenbrowne.com/subquery-01.html
Another might be with domain aggregate functions (DLookup, DMax, etc).

Need a field in query that returns a value that can be used by the Conditional Formatting setup. That field could be result of subquery or expression using domain aggregate functions.

Maybe something like:

IsChange: IIf(IsNull(DLookup("[Name]", "tablename", "ID=" & [ID] & " AND [Name]<>'" & [Name] & "' AND Version<" & [Version])), "N", "Y")

In a simple test with the sample data you posted, only the "B" record returns "Y".

However, if there could be more than two versions of ID and more than one has a change, then the code will be more complicated if you want only the last version to be emphasized.

[selvakumar.arc]

Thanks alot for your help. I added formatting syntax via Format - Conditional Formatting menu and it is working

Millions of Thanks to you...