Shaded empty text boxes in a report 1

[VillaRestal]

Does anyone one know if its possible to shade text boxes on a report if they pull a null and or zero value from a query?

Thanks in advance, I am pretty new to this; but am very eager to learn.

 

[scriverb]

Sure, in the Detail Section OnFormat Event Procedure put the following code:
If (IsNull([ControlName1]) or [ControlName1] = 0) then
Me.ControlName1.BackStyle = 1
else
Me.ControlName1.BackStyle = 0
end if

Also, set the controls BackColor to whatever color you would like and set the BackStyle to 0(transparent). The above code will analyze the controls value and change the BackStyle either Normal(1) to show the shading color or to Transparent(0) to NOT show the shading.

These settings and the above VBA code needs to be done for each control that you want to analyze and backshade.

Bob Scriver

 

[VillaRestal]

Bob,

Thanks for your reply. I tried to use the code you provided, but was unable to locate the "OnFormat Event Procedure" in the detail section. I tried the code in the OnOpen field of the report itself, but that ended up giving a compile error (247 if I remember correctly).

I am very sure that the problem lies somewhere between the keyboard and my chair.

Where should I be able to find the OnFormat controls?

Thanks again for the help.

 

[VillaRestal]

Ok I was able to get some much needd sleep and found where I am supposed to place the code, but I am getting a Compile Error, Method or data member not found. The .BackStyle is highlighted on the debug screen.

I am sure that i am doing something incorrectly, my VBA knowledge is nearly nil.

Thanks in advance for any help or explanations

 

[scriverb]

I probably has something to do with a library registration problem. So, let's do it another way. Try this code:

If (IsNull([ControlName1]) or [ControlName1] = 0) then
Me.ControlName1.BackColor = 12632256
else
Me.ControlName1.BackStyle = 16777215
end if

This will change the back color from white to lt. grey and back again depending upon the value in the control.


Bob Scriver

 

[VillaRestal]

Bob,

Thanks for the help. It looks like it works, I noticed the majority of the headings from the xls have spaces, so I think that is causing most of the problem. But at least now I know it can be done.

Thank you very much.