How do I make a LABEL disappear?

[Pharma Down]

I have seen a few posts about making different fields disappear, but this isn't my problem - if the (textbox) field is empty then it doesn't show up on my report; however, the textbox label remains. The report contains over 50 textboxes (plus labels), so if the textboxes don't contain info then I need the labels to disappear (otherwise the report could be 4 pages long but only contain a few lines of actual info).

I have almost no understanding of coding/VBA, so if I have to do something please try to explain very carefully... using small words!

The report is based on a query that uses info from multiple tables.

Thanks

 

[sxschech]

In the code for the report you could do something like:

If me.txtBoxName is null then
       me.lblName.visible = False
Else
      me.lblName.visible = True
End If

txtBoxName would be the name of the text box that you say is blank
lblName would be the name of the label associated with the text box.

To do this, you would open the report in design view and assuming that the labels are in the detail section, you would put the code in the Format or On Print Event. I usually end up putting the same code in both events just in case so it shows up on screen and when printed out.

 

[vbaInet]

What is the layout of your report? Is it in Tabular or Columnar form?

If it's in Tabular form it won't make sense to hide them because it will leave empty columns.

If it's in Columnar form, i.e. Label on left and Textbox on right, then you can:

1. Change your Label to a Textbox
2. In the Control Source of the textbox in step 1 put:

=IIF(IsNull([[COLOR=Red]FieldName[/COLOR]]), Null, "[COLOR=Red]Label[/COLOR] [COLOR=Red]Caption[/COLOR]")

Substitute FieldName for the name of the name of the field.
3. Set the Can Grow and Can Shrink properties of this textbox to Yes.

 

[sxschech]

Thanks vbaInet, forgot that we could use IIf statement to accomplish this.

 

[vbaInet]

Thanks vbaInet, forgot that we could use IIf statement to accomplish this.

No problemo! A good suggestion from you though.

Just fyi for you as per the event to put it in, for this kind of thing it can go in the Print event. If you're dealing with changing values of a control or moving controls you will need the Format event. Code executes quicker in the Print event so use it when you can.

The other thing is, Is Null is valid in queries but when used in VBA it's meant as a test for objects so instead of Is Null you use the IsNull() function. The general rule is use in a query, use Is Null in the Criteria row, and IsNull() for field testing.

 

[Pharma Down]

Er... thank you, but...

I can't remember whether I used the report wizard with a columnar or tabular set, but enough to say that it is highly customised - the field labels sit just above the textbox, within the Detail section of the report, eg:

Title Label
Text box with the info

I think that I understand the 'change to a textbox' approach, although I haven't had a chance to try it, but with the other approach you might have to explain a little more - where do I find the Format and On Print Event? Since the label is a label (!) there is nothing in the Property Sheet under the Event Tab, and I can't find a Format either! When i explore this for a textbox I founf Format under the Format tab of the Property Sheet (is this the place to put the text?), but still couldn't find an On Print Event"!

You lost me with the IsNull bit!

 

[vbaInet]

So why put the Label in the Detail section when you can put it in the Report or Header section?

 

[Pharma Down]

There are roughly 55 textboxes and labels and I want it to print out on A4. It also needs to roughly match a predetermined format that has nothing to do with Access!

 

[vbaInet]

My point is, the Report Header section is printed only once, so why not put it there?

 

[Pharma Down]

I think that I see your point...

I use a form to collect/type up the info and then want to print out a report from a single record, so each report should (will) only show each label once and with a single textbox linked to it, eg

Report for Record 1

Label 1
Textbox 1 with info

Label 2
Textbox 2 with no info

Label 3 etc...
Text box 3 etc

I'm sure it would be easier to explain if I could upload a picture, but my text version (above) isn't a bad approximation!

 

[Pharma Down]

I have just tried changing the label to a textbox, but I can't get around the control source element - so each time I try to make this new textbox look like a label (ie give a title/header to the subsequent actual textbox) it either uses the relating info (because it uses the control source) or asks me to provide the control for the unlinked textbox.

 

[Pharma Down]

Is it possible to combine the Is Null code as advised by vbaInet and the system advised by sxscheck, because being null would be better than simply not being visible

Would the item being null be better than being not visible? I assume that if it is not visible then the field still takes up space in the report, but if it is null then all the other fields shift up the screen/report, so there is no blank space. Is this right or am I way off the mark? I am also still struggling to find the Format and On Print Event bits (see reply #6) - any advice?

 

[vbaInet]

So all the controls are stacked?

Show me what you put in the Control Source of the label (i.e. the label which you converted to a textbox) of Textbox1.

 

[vbaInet]

Show us a screenshot of your report in Design View.

 

[Pharma Down]

I will try, but I can't work out how. I think that this might be because I have less than 10 posts!

 

[Pharma Down]

Right then... I have attempted to attach a screenshot. I apologise for the quality - it is the best I could do with the facilities available to me on this (someone else's work) computer.

The grey bits are the labels and the boxes directly below are the associated textboxes. I have opened up the Property Sheet and you can see that I have one of the labels selected.

If this is not clear or doesn't show you what you need to see please let me know and I'll try to sort out a better shot.

 

[vbaInet]

Ok, I can see the layout but I can't see what you put in the Control Source of the converted label.

 

[Pharma Down]

It doesn't give me the option of a control source for the label. When I cahnge it to being a textbox I want it to read the same way as the label would, but if I try to type in the text (as though I were typping in a label) it uses this as the control source - if it matches an actual control then it uses the info for that control (ie shows what would otherwise be in the proper textbox) or if it doens't match a control then when I try to veiw the report it throws up a box asking me to provide info for that control (which doesn't exist, because it is my new textbox).

 

[vbaInet]

In my first post #3, I gave you the exact steps to achieve this. The reason why I said you should convert the label to a textbox is so that you can take advantage of the Can Grow and Can Shrink properties of a textbox. When these properties are set, it will shrink when the value is Null and grow when it's not.

So, I gave you code in step 2 to emulate this. The only bits you need to change are in red. FieldName will be the name of the field bound to the textbox below the label, and Label Caption is what you want the label to read when it's visible.