conditional formatting to detect nulls

Topic

New Reply

I found that you can do this only on a per-control basis, so I need to figure out how to poll all controls and change formatting in VBA

Searching for the syntax… how do you format a form field when there is nothing in the control? i’ve tried ISNULL, NULL, =””, ISBLANK and did this in Expression Is and Field Value Is.

TIA

Reply | Quote

Replies

In the format property you can enter 4 formats separated by a semicolon. the fist is for the case the field is positive, the second if the field is negative, the third if the field is 0 and the forth if the field is NULL
Something like $#,##[GREEN];($#,##)[RED];”Zero”;”Null”

Reply | Quote

It is possible to cycle through all of the controls on a form (or forms) and set properties such as conditional formatting, but the concepts are beyond the scope of what can be done in a single message or thread. One of the more advanced Access books should have a section on how to do that – the Wrox series had some good advice on doing that sort of thing. One potential gottcha is if you don’t have a consistent set of control names, and another is if you already have some conditional formatting.

Reply | Quote

This is how to run thru controls on a form and set the back color to red (255). I use Smart Tags in properties to create a family for controls that i can poll. this code is targeted to a tabbed form (where i only want to hit the first 8 tabs), but can easily be changed to suit your needs…

Public Function FormatRUGItems()
Dim UserCtlString As String
Dim UserCtl As Control
Dim ctl As Control
For i = 0 To 7
For Each ctl In Forms!.MainTab.Pages(i).Controls
With ctl
If .Tag = “OR” Then
ControlValue = .Value
If IsNull(ControlValue) Then
.BackColor = 255
End If
End If
End With
Next
Next i
End Function

having said that, strangely enough the same technique doesn’t work on another set of controls i have tagged “UE”. perhaps it’s because several of these are drop-downs… not sure yet.

Reply | Quote