Label on Report: How to shrink it to height 0 1

[Ogart]

It’s easy enough to get the text box on a report to shrink to 0 height if it’s null. No problems. I’m struggling with the label that’s associated with those text boxes. What I’d like to do is get the heights on those set to 0 when the text boxes they label are null (have no data). Labels seem to have a lot less options in terms of conditional formatting.

Q: How do I set the height of a label 0 when the text box it's labelling has no data in it (is null)?

You guys rock. Thanks for all the help, in advance.

 

[jiqjaq]

For labels on reports, if the problem is that you don't want to see them, just set the Visible property to False, it easier than changing the height (which you can only set to 0 if the caption is "&quot.

Keep in mind that a report's section height will only shrink when the heights of the controls within it shrink, not when they are not visible but are still the same height.

 

[Ogart]

Yeah, I was afraid of that.

In my perfect world, the labels disappear with height 0 if the text box (that the labels are...labelling) has a null value.

The two approaches that come to mind are:
a) Blow away all the label boxes, and create text boxes. Have these look at the text boxes with my data in question. If there's data, display a "label", if not, height = 0, and invisible, just like the text box it's adjacent to/labelling.
b) Some way which I don't think exists of having a caption of "" in the label, then make the label height 0 and invis, all of which is dependant on whether or not its text box has any data. I strongly suspect this approach won't work--labels seem not to have as much "smarts" built in.

The requirement that drives all this stuff is that I've got a table that describes all items. The problem is that not all fields make sense for all items. What I'm trying to do is say on my report: "If this field doesn't have anything useful to tell a client, don't show it on a report."

I COULD have a custom report for every single type of item, but that's just inelegant, labor intensive, maintenance nightmare, etc.

Any thoughts appreciated. Thanks!!

 

[jiqjaq]

In code you can set the label's caption to "", but not in design view, which won't define it if there's no caption.

If you want the labels to actually shrink to a 0 height then you'd be better off making them textboxes. When you set the textbox value to "", if it has canshrink=true, it will atuomatically shrink to 0; you don't have to set the height. Labels, on the other hand, woould have to have both the caption and the height set.

If you name the data textboxes and the label textboxes so that only the prefix/suffiox of the name is different, you can loop thru them in code in the Format section to get the effect you want. E.g., if the names were txtFieldT_1/txtFieldL_1 , txtFieldT_2/txtFieldL_1 you can do something like
For intNdx to <fieldcount>
if me(&quot;txtFieldT_&quot; & intndx) = &quot;&quot; then
me(&quot;txtFieldL_&quot; & intndx) = &quot;&quot;
End if

As long as you rest the textboxes used as labels at the beginning so you can have labels again, you should be okay.

 

[Ogart]

Yeah, makes sense. I'll give that a try. Thanks.

How do I &quot;Trigger&quot; that code? I am under the impression that access is all event driven.

I appreciate the help, seriously.

 

[jiqjaq]

You put the code in the Format procedure for the section that contains those controls in the Design view. If the controls are in the Detail section, that would be Detail_Format.
----------
This next part is kind of lengthy, but it's not easy to explain.
----------
The code in the report Format sections is event driven, but Access is doing the &quot;driving&quot;. When the report is run, Access runs the Report_Open. Then each section (vertically), as you've defined it in the design, is &quot;printed&quot;, first by interpreting any controlsources defined for fields on the report layout, and then by running any code in the Format procedure for that section. When the formatting procedure has completed, any code in the Print procedure will then run. After the report has finished printing, any code in the Report_Close (e.g. query deletion, file closing, etc.) will run.

The Format-Print,Format-Print cycle is run each time a new record is read from the RecordSource for the report, when that record forces activation of the section. The Detail section is run for every record, but the other sections will only be run if the header or footer is triggered by the data. Headers/Footers will run each time the grouping associated with that header/footer changes. The header runs on the new value and the footer on the previous value.

When you make a section invisible (or have a height of 0), it will still format, but the Print routine will not run. If the section is visible but the control is invisible, the Format and Print routines will run, but that control will just not be physically printed.

Depending on what you're doing in the report and how you've designed the layout, the Format procedure may run mutiple times for each print of the section.

For example, suppose at some point in the formatting, Access decides you're beyond the page margins and a new page is needed. The Print routine will run as usual after the Format, but the Format will then automatically restart, since the entire section could not be formatted on the first pass. (The Print routine may also be forced to run multiple times but that's much rarer.)

This is the reason why the Format procedure has an automatic variable passed in (FormatCount) on the procedure line. Print has as PrintCount. These will tell you what iteration you're on, if the procedure was forced to reiterate. When the routines are done with any iterating, the counts automatically reset to 1 for the next time.

Usually you don't have to worry about the FormatCounts but you still need to be aware of them.

Similarly, the other Report routines are run when needed. The Page routine runs each time a new page is detected, etc.