Solved Hide controls and theirs labels in a report

[freuzo]

Hello,
I create an order report but the ranges must not be static. This is just an example to illustrate my concern.

Food names are labels and quantities are controls. I would like that for the empty quantities (or equal to 0) the line disappears, the control and its label. For controls, the auto shrink option can do but labels do not have this option.
I would like that at the end of this specific example, the lines AVOCAT and BANANE have completely disappeared and that PATATE are placed just after MANGO.
I'm sure there is a way to do this by VBA but i'm not good enough at it yet.

Thank you in advance.

 

[Deleted member 147267]

No vba required. Base your form or report on a query that eliminates records where a field is blank and don't use the table itself for a record source. Create such a query, then set the report record source to that query.

 

[freuzo]

Yes, it is a good idea, but if a query sends me back only the non-empty controls, will they all line up close to each other?

 

[theDBguy]

Yes, it is a good idea, but if a query sends me back only the non-empty controls, will they all line up close to each other?

Hi. Can you post an image of your report in design view? Thanks.

 

[freuzo]

That's how it looks (sorry my main language is french)

So even if a query does send back just Principal and Annulation, will the report show only those two lines next to each other ? I was thinking more by VBA. And i'm not sure on how i can create such query (and i'm not so bad at creating queries now).

 

[theDBguy]

View attachment 82197

That's how it looks (sorry my main language is french)

So even if a query does send back just Principal and Annulation, will the report show only those two lines next to each other ? I was thinking more by VBA. And i'm not sure on how i can create such query (and i'm not so bad at creating queries now).

Hi. Looks like the sample data you posted earlier really has nothing to do with the issue you're trying to resolve. It was a bit misleading. Okay, so if you have empty controls, you can shrink them (or the opposite, you can grow them, if they contain something), but that is not going to be as easy if you also want to eliminate the zeroes (0) from the report.

 

[Deleted member 147267]

IIRC, can grow/shrink only works in print preview, which may be OK. Also, I cannot recall if in order to shrink a particular control that all other controls in the record must have no data. Perhaps not, but I was reading something about that recently and cannot recall the comment exactly.

To address the other questions, if a report field has no value it still shows if any field in the record has a value. I thought your original issue was about not showing records when certain fields have no value. To not show the actual control, one can either completely remove any control border or shading in design view, or you might be able to use Conditional Formatting to remove them - I can't recall for sure. I know that in a report you can cf the font and backcolor properties so I don't see why you couldn't remove the border as well - assuming you don't want to simply remove them in design view.

 

[freuzo]

Hi. Looks like the sample data you posted earlier really has nothing to do with the issue you're trying to resolve. It was a bit misleading. Okay, so if you have empty controls, you can shrink them (or the opposite, you can grow them, if they contain something), but that is not going to be as easy if you also want to eliminate the zeroes (0) from the report.

Really sorry about the misleading and we can forget about the 0. I already manage to not have them. The problem with the Can Shrink is that the labels don't follow.

 

[freuzo]

IIRC, can grow/shrink only works in print preview, which may be OK. Also, I cannot recall if in order to shrink a particular control that all other controls in the record must have no data. Perhaps not, but I was reading something about that recently and cannot recall the comment exactly.

To address the other questions, if a report field has no value it still shows if any field in the record has a value. I thought your original issue was about not showing records when certain fields have no value. To not show the actual control, one can either completely remove any control border or shading in design view, or you might be able to use Conditional Formatting to remove them - I can't recall for sure. I know that in a report you can cf the font and backcolor properties so I don't see why you couldn't remove the border as well - assuming you don't want to simply remove them in design view.

Sorry again to the misleading.
To be completely clear. These are not records, these are fields of a same table. So one record can just have principal+fraisenvoi (these 2 are ALWAYS there) and another can have principal +fraisenvoi+fraismodification+annulation.
The report will be in pdf and I don't want the display of the fields not used.
I think I told everything. Thank you for your patience

 

[theDBguy]

Really sorry about the misleading and we can forget about the 0. I already manage to not have them. The problem with the Can Shrink is that the labels don't follow.

To solve the problem with shrinking the labels is to use textboxes instead with expressions to display the appropriate labels. In other words, use a textbox instead of a label and display a text if there's data; otherwise, leave it blank, so it can shrink.

 

[Deleted member 147267]

Now I'm more confused. Table fields don't have labels. Reports don't have tables (ones with records in them at least). Labels don't have grow/shrink method (or property, whichever it is) as you pointed out. I think all you can do is "hide" the labels and controls with conditional formatting. To hide a control with no data I would try cf as mentioned. However, I don't think you can set either of them to visible = false using cf. To hide the label, you'd have to set its caption to "" and remove its border - or as I said, do it in design view by removing such properties all together. When you see a report in design view, there is only 1 'customer' control, for example. All of the repeated ones are 'instances' of that control, and what you do programmatically to one will happen to all of them, so I would forget about vba altogether for this. Conditional Formatting can work around that limitation and may do what you want. Still, I suggest you try removing all the formatting in design and see what you get.

 

[freuzo]

Micron I was saying that those lines were not records by themselves but referred to fields of the same record. The report is suppose to be for just one record.
I will try to do with yours advices and see how it goes.
Thanks again.

 

[arnelgp]

is this somewhat similar?

 

[Uncle Gizmo]

is this somewhat similar?

That's Very Clever!

 

[freuzo]

is this somewhat similar?

That is exactly what I'm looking for. Can you give us some explanation?

 

[Gasman]

is this somewhat similar?

@arnelgp
Any chance of a version that will open in 2007 please? I'd like to see what you did.?

TIA

 

[arnelgp]

try this mr.gasman

 

[Gasman]

Thank you arnelgp,

Very neat

 

[freuzo]

Hi, arnelgp. Thanks for the function you gave for this issue.
I have managed to adapt the code to certain situations, but there is another that is beyond me. The colleagues who are going to use the application asked me to add some "number" fields as on the attached image.

For example for the first line, it is 11 items which give a total of 1000. So on the report, it is necessary that it be displayed as follows:

- Principal (11)________________1000
- FraisModification (02)_______50

I told myself that I should create a control that would concatenate this information before using it in the joint_fields functions, but I can't do it.

It's a lot to ask, but if you or someone else can find a solution, it would be really great.

Thank you in advance.

 

[arnelgp]

will this be an exception when Number_P=11?

plz see the report and the code behind.