Change report textbox backcolor if null or empty

[RAdams]

I thought this should work (and it does on two date fields in one table, but no other fields):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
  Dim ctl As Control
 
  For Each ctl In Me.Detail.Controls
 
    If ctl.ControlType = acTextBox Then
 
      If (IsNull(ctl) Or ctl = "") Then
         ctl.BackColor = &HFFC0C0 'light blue
      Else
         ctl.BackColor = vbWhite
      End If
 
    End If
 
  Next ctl
 
End Sub
 

Any hints? I've stepped through the running code and the backcolor property is changing as it should, but doesn't display on the preview report. I also changed the backcolor property in Design View and that doesn't display on the report either. (??)

Thanks

[jimatqsi]

Try changing your test to

If (IsNull(ctl.value) Or ctl.Value= "") Then

Jim

[RAdams]

Hi, Jim. No the code above does work; I've traced it through line by line, checking values as it went along in the immediate window, and the backcolor is being changed as far as the program is concerned. It just doesn't display on the report preview. I'm wondering if there's some setting on the detail section that's overriding the code setting? Also, I did an experiment where EVERY textbox has its backcolor set to light blue at runtime and only those with text in them are affected -- empty or null textboxes don't change on the preview, the exact opposite of what I want to happen.

[RAdams]

I've found if I set the code thusly:

    For Each ctl In Me.Detail.Controls
 
        If ctl.ControlType = acTextBox Then
 
            If NOT (IsNull(ctl) Or ctl = "") Then
                ctl.BackColor = &HFFC0C0 'light blue
            Else
                If ctl.ControlType = acTextBox Then ctl.BackColor = vbWhite
            End If
 
        End If
 

then all fields with values in them have the backcolor changed while empty ones do not. Leaving the NOT out of the code, however, does not reverse the situation and no fields display different backcolor (even though, as far as the values in the immediate window are concerned, it IS being changed).

[jimatqsi]

The IF statement is only processed as far as necessary. Without the NOT a ctl with a NULL value is TRUE for the first test (ISNULL(ctl)) so the ctl= test is never done. In the case of ctl with not NULL value the first test fails so the test after the OR is done and those controls with NULL hit an error.

With the NOT prefixed to the test the ctl="" test is never done when the ctl is NULL so no error.

I'm not sure why you are seeing evidence of the color changing as it should But I would change the IF test to be like this:

If LEN(nz(ctl.value))=0 then 

or simply wrap your ctl= in an NZ

If NOT (IsNull(ctl) Or NZ(ctl,"") = "") Then 

I think that will work much better.

Jim

[RAdams]

Here's what I get using the code I posted above:

This WITH the NOT:

This WITHOUT the NOT (the desired action):

Note that for this table, the START DATE and END DATE fields display as desired when empty, but the CONTRACT POC NAME does not. On other, similar reports using different tables, even the date fields don't work.

Well, phooey. Forbidden, eh? Well, take my word for it, when the NOT is in place, any field with data in it has the backcolor changed to light blue. Without the NOT, empth fields do not change, though it seems from the code that they should.

See if the direct links work for you: http://bvpeonyplace.com/WithNOT.JPG

and

http://bvpeonyplace.com/WithoutNOT.JPG

[Seth Schrock]

If the Enabled property of the control is set to NO, then most of your appearance settings will be ignored (unless the Locked property is set to Yes). I don't know know if this is the case for you or not, but that is the only time I have seen the ability to change a background color or font setting removed.

[RAdams]

Nope, all controls enabled, Display When set Always, Visible set Yes, Back Style set Normal. I'm confused.

[RAdams]

Just tried something else. Instead of backcolor, I set the Border Color property on all text boxes to Red and Border Width to 2 pts, with the Border Style set Transparent. In code, I changed BackColor to BorderStyle with 0 for transparent and 1 for solid. Same problem -- now the borders appear where before the color changed, but with the same inconsistencies.

http://bvpeonyplace.com/Borders.JPG

[jimatqsi]

Have you wrapped the ctl in NZ? Have you tried

If (IsNull(ctl) Or NZ(ctl,"") = "") Then

[RAdams]

That part of the code is working and the values are getting changed. Just doesn't display properly. On a couple of fields in one table the report displays when they're empty, but not other fields and not on other reports using different tables.

[RAdams]

Ok, here's what's what so far:

If a text box doesn't have something in it, it won't display at all (my anomalous date fields notwithstanding). So changing the backcolor for an empty textbox doesn't do anything. A kluge for this involves a set of dummy text boxes (or labels) with back- and forecolor set in properties to the desired display color for empty fields. Posisition these behind their matching textboxes, then make the front textboxes visible or invisible depending on whether they have data in them or not.

Since empty textboxes won't display, you'll have to put something in the dummy boxes, either binding them all to some field in the table with dummy data in it or, what I did, for the Data Source in Properties, put a formula (e.g. "=2+2"); since the back- and forecolors are the same on the dummy boxes, the resulting calculation won't show. My code now looks like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim ctl As Control
 
    For Each ctl In Me.Detail.Controls
 
        If ctl.ControlType = acTextBox Then
 
            If (IsNull(ctl) Or ctl = "") Then
                ctl.Visible = False
            Else
                ctl.Visible = True
            End If
 
        End If
 
    Next ctl
 
End Sub
 

and it works fine. Whew.

[RAdams]

I got to thinking: if textboxes bound to fields with no data in them (NULL or "") don't display, then I shouldn't need any code at all. I commented the entire Detail_Format() sub out and indeed that is the case. The dummy textboxes, having something in them (the calculation) stay in place and the textboxes either NULL or empty don't display. Much simpler, actually more intuitive, just a bit tedious for tables with several dozen fields. But it's inellegant. Oh, well, whaddya gonna do? Thanks for everyone's suggestions and help.

[zmbd]

Just to clarify.
Is this on a report or on a form that you are trying to print?

[RAdams]

Hi, zmbd. On a report. On a form I think it would be a lot easier.

[zmbd]

(^_^) IMHO: Forms are for GUI-HIW/D and Reports are for that reports (^_^)

MS Attempts in recent versions to allow user interactivity within reports causes more issues that one would think - and simply confuses the issue between reports and forms.

ANYWAY!

Have you taken a look at the following:
Using Conditional Formatting in Access 2010 Reports
(I think this is available in ACC2007 - IDK, don't have a copy)

Basically:
I did this in layout view, however, you can also do this in design view. I've cut and pasted a few screenshots here to over lap the Ribbon, Conditional Dialog, and New/Edit rule dialog.
The expression is the [fieldname] & "" = ""
(note "" are empty strings, two double-quotes back-to-back... looks like a space between them in the font I use onscreen so I just want to make sure)
based on the propagation of Null Values as disscussed herein What is Null?.

I think this is one the things MS has right. IIRC: This will work even when the VBA/Macro is disabled or in non-trusted state.

(^_^)

[RAdams]

Thanks a lot, zmbd. Much appreciated. Life's a learning experience that never ends -- that's my goal, anyway. :o)

[zmbd]

I think that's why I like Chemistry and Doing this... just when you think you know it all, something new pops up.

Let us know how this works.