Conditional Formatting

[shewolf]

I'm trying to create Avery Labels using the reports in Access and have hit a bit of a glitch.

There are several fields where I have to put a Label in front of the actual field so that folks looking at the label know what the value ties to. So I have a label PKG in front of a field called PackageNumber.

Here's the point where I hit the speed bumb. I need the label PKG to only show up if there is actually text in the field PackageNumber. I tried to do conditional formatting on the label, so I could set it to white, but that's not an option.

So then I tried the following code, but it doesn't seem to work either.

I need the labels not to show so they don't print on labels where there is no data, otherwise I'll be wasting a lot of labels.

Private Sub Labels_Avery5161_Make2_Open(Cancel As Integer)
'This routine is designed to change the text field labels to white font
'if the fields they are labeling are blank.
If Me.PackageNumber = IsNull Then
Me.PKG.ForeColor = RGB(255, 255, 255)
End If
If Me.PMISNumber = IsNull Then
Me.PMIS.ForeColor = RGB(255, 255, 255)
End If
If Me.Volume = IsNull Then
Me.VOL.ForeColor = RGB(255, 255, 255)
End If
End Sub​

Any and all assistance is greatly appreciated.

Charis

 

[shewolf]

Success!!!

Pat,

Thanks for the assistance! At first I still couldn't get it to work but then I set it as the event procedure for Detail_Print and voila.

Once again this awesome resource comes through for me.

Thanks again so much for the assistance.

Charis

 

[samehkh]

I am kind of in the same situation, but I am desiging a report for some test results of batteries, I have about 32 text boxes and i need to set the background colour to gray if the value exceeded a certain threshold.
I would normally go like:

if me.V1> 20 then me.v1.backgroundcolour= gray
if me.V2> 20 then me.v2.backgroundcolour= gray
.
.
.
.
.
.
if me.C1>40 then me.C1.backgroundcolour= gray
if me.C2>40 then me.C2.backgroundcolour= gray
.
.
.
.
The threshold value is the same for the first 16 boxes and the second 16, there are some other controls that i want to skip.

I am wondering if there a smarter way to do it, through code.
I tried setting the background colour property of the controls to a function like getcolour() but access wont take it. And I alway hated dealing with objects and collections so I need a tip about doing that.

Thanks.

 

[samehkh]

I did not fully understand your solution. Can you please clarify the idea of using the tag property?

I know how to loop through fields of a table using table.fields(x) but havent tried that with a report, especially that there is some labels and lines as well that I will need to skip.


Thanks.

 

[samehkh]

I had to use trial and error, I tried several things and it finally worked.

For Each Box In RPT
If (Box.Tag = "45") Then

If (Box > 0 And Box < 45) Then
Box.BackColor = White
Else
Box.BackColor = Grey
End If

End If

What confused me a lot is that I declared Box as control, but when i do Box. , and i get the drop down list, i could not find backround color listed, so I thought I might be doing something wrong, and changed it to TextBox, and it didnt work either, so I tried typing .BackgroundColor manually, and it did work, why wouldnt it be listed in the properties, it it is a valid entry ?? That was soo confusing, I thought I was doing the wrong type of variable all the time.