Apply Conditional Formatting to apply a subform by Check Box on Main Form

[DubaiDave]

Apply Conditional Formatting to a subform by Check Box on Main Form

Hi,

I would like to apply Conditional Formatting to a SubForm if a CheckBox on my MainForm is selected.

My SubForm resides on a tabbed sheet on my MainForm (although according to Labans, this makes no difference to its referencing).

The code I am now using runs without error (!) but does not invoke the Conditional Formatting.

Does anyone know why this does not work?

DubaiDave


Here it is:

Public Sub subConditionalFormat()
 
' [URL="http://msdn.microsoft.com/en-us/library/aa139965(office.10).aspx"]http://msdn.microsoft.com/en-us/libr...ffice.10).aspx[/URL]
 
Dim objFrc As FormatCondition
Dim lngRed As Long
Dim lngGreen As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim cntrl As Control
 
' Set up foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngGreen = RGB(0, 255, 0)
 
' Remove any existing format conditions.
Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Delete
 
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Closed")
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Open")
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "NoStatus")
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(0)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(1)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(2)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
Set objFrc = Nothing

 

[Galaxiom]

Would your ClosedStatus control be a combobox displaying the string but with its value bound to a key? If so you will have to set these keys as comparison in the conditon rather than the strings.

 

[DubaiDave]

Hello Again Galaxiom,

Closed Status is a textbox on the SubForm which is bound to a query. Does that help? Could you please add a little further explanation - I am a novice.

DubaiDave

 

[Galaxiom]

Try removing the last line.
Set objFrc = Nothing
It isn't in the example at Microsoft and maybe it wipes the condition.

 

[DubaiDave]

Hi Galaxiom,

Unfortunately that did not solve the problem.

David

 

[Galaxiom]

Run out of sensible ideas. Here are some slightly desperate ones.

I assume you have tried to apply the conditions manually and they work.
If it works when manually applied it should work in VBA.

Could there be extra spaces at the end of the string value in the textbox?

Another technique, particularly if the manual formatting doesn't work.
Sometimes Access is funny about comparing strings.
Try changing the add arguments to:

Add(acExpression, , "StrComp([ClosedStatus], 'Closed') = 0")

Read up on the String Compare function and try the third argument variants.
http://office.microsoft.com/en-us/access/HA012289141033.aspx

Try to determine where it is going wrong by checking if the format object is created. Add this line after the objects are created. This will show in the Immediate Window (press Ctrl G) in the VBA designer.

Debug.Print Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(0)

This line should also return the correct syntax of any manual conditional formatting if you skip all the code that modifies it.

You should also be able to see the Conditional formatting settings in the Form view after the code is run.

 

[DubaiDave]

Galaxiom,

Thanks for your help and encouragment. I have cracked it.

It was a very small mistake I made..but a rather stupid one.

Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Closed")

Should have been

Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "'Closed'")

What a difference those extra ' ' make!


DubaiDave

 

[Galaxiom]

Ah, the old "string is another string" sting. The Microsoft example used numbers rather than strings.

Don't be too hard on yourself Dave. I didn't see it either and at least you worked it out in the end. It will be a lesson well remembered for both of us. One day I will apply Conditional Formatting using VBA and I won't get caught.

The penny should have dropped for me when I wrote the StrComp suggestion as I put the single quotes in on that one. It always seems harder to spot nested string situations when they have nothing else inside the outer quotes.