By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,948 Members | 1,587 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,948 IT Pros & Developers. It's quick & easy.

Change report textbox backcolor if null or empty

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.  
  3.   Dim ctl As Control
  4.  
  5.   For Each ctl In Me.Detail.Controls
  6.  
  7.     If ctl.ControlType = acTextBox Then
  8.  
  9.       If (IsNull(ctl) Or ctl = "") Then
  10.          ctl.BackColor = &HFFC0C0 'light blue
  11.       Else
  12.          ctl.BackColor = vbWhite
  13.       End If
  14.  
  15.     End If
  16.  
  17.   Next ctl
  18.  
  19. End Sub
  20.  
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
May 15 '14 #1
Share this Question
Share on Google+
17 Replies


Expert 100+
P: 1,240
Try changing your test to

Expand|Select|Wrap|Line Numbers
  1. If (IsNull(ctl.value) Or ctl.Value= "") Then
Jim
May 15 '14 #2

P: 11
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.
May 16 '14 #3

P: 11
I've found if I set the code thusly:

Expand|Select|Wrap|Line Numbers
  1.     For Each ctl In Me.Detail.Controls
  2.  
  3.         If ctl.ControlType = acTextBox Then
  4.  
  5.             If NOT (IsNull(ctl) Or ctl = "") Then
  6.                 ctl.BackColor = &HFFC0C0 'light blue
  7.             Else
  8.                 If ctl.ControlType = acTextBox Then ctl.BackColor = vbWhite
  9.             End If
  10.  
  11.         End If
  12.  
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).
May 16 '14 #4

Expert 100+
P: 1,240
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:
Expand|Select|Wrap|Line Numbers
  1. If LEN(nz(ctl.value))=0 then 
or simply wrap your ctl= in an NZ
Expand|Select|Wrap|Line Numbers
  1. If NOT (IsNull(ctl) Or NZ(ctl,"") = "") Then 
I think that will work much better.

Jim
May 16 '14 #5

P: 11
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
May 16 '14 #6

Seth Schrock
Expert 2.5K+
P: 2,951
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.
May 16 '14 #7

P: 11
Nope, all controls enabled, Display When set Always, Visible set Yes, Back Style set Normal. I'm confused.
May 16 '14 #8

P: 11
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
May 16 '14 #9

Expert 100+
P: 1,240
Have you wrapped the ctl in NZ? Have you tried
Expand|Select|Wrap|Line Numbers
  1. If (IsNull(ctl) Or NZ(ctl,"") = "") Then
May 16 '14 #10

P: 11
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.
May 16 '14 #11

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  2.  
  3.     Dim ctl As Control
  4.  
  5.     For Each ctl In Me.Detail.Controls
  6.  
  7.         If ctl.ControlType = acTextBox Then
  8.  
  9.             If (IsNull(ctl) Or ctl = "") Then
  10.                 ctl.Visible = False
  11.             Else
  12.                 ctl.Visible = True
  13.             End If
  14.  
  15.         End If
  16.  
  17.     Next ctl
  18.  
  19. End Sub
  20.  
and it works fine. Whew.
May 16 '14 #12

P: 11
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.
May 16 '14 #13

zmbd
Expert Mod 5K+
P: 5,397
Just to clarify.
Is this on a report or on a form that you are trying to print?
May 18 '14 #14

P: 11
Hi, zmbd. On a report. On a form I think it would be a lot easier.
May 19 '14 #15

zmbd
Expert Mod 5K+
P: 5,397
(^_^) 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.

(^_^)
Attached Images
File Type: jpg BytesThread_956734.jpg (62.7 KB, 3519 views)
May 19 '14 #16

P: 11
Thanks a lot, zmbd. Much appreciated. Life's a learning experience that never ends -- that's my goal, anyway. :o)
May 19 '14 #17

zmbd
Expert Mod 5K+
P: 5,397
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.
May 19 '14 #18

Post your reply

Sign in to post your reply or Sign up for a free account.