Hunting for an IIF statement to hide a label

[lccrews]

I have been hunting for an IIF statement to use on a label. I've set the On NoData event to the following:

Private Sub Report_NoData(Cancel As Integer)
Me.Visible = False
End Sub

However, the label still remains. I've tried setting the label to this:

=iif(isnull([Form.qry_BP40_Gummy_FinalComposition_Orange subform rpt]),null,"FINAL COMPOSITION - ORANGE")

Unfortunately, the label doesn't disappear. Am I being dense? I've seen a couple of examples using the following code, but I do not know how to utilize them as I am trying to hide multiple subforms whenever they have no data. Plus, I cannot find the On Format property in Access 2016.

If IsNull(dataFIELD1) Then
Label1.Visible = False
Else
Label1.Visible = True
End If

With my limited understanding of this code, I thought it wouldn't work as I'm trying to hide several subforms/labels if they have no records.

[isladogs]

The Report_NoData procedure is normally used to show a message if the report has no data ...!
For example

Private Sub Report_NoData(Cancel As Integer)
   MsgBox "There is no data for this report", vbExclamation, "No data"
End Sub

In your case, Me.Visible refers to the report & you are trying to get the report to hide itself ... which won't work!

You need to add code to the Format event of whichever section contains your label. For example:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
     If Nz(Me.MyFieldName,"")="" Then Me.MyLabelName.Visible=False
End Sub

Substitute your own label & field names in the above

NOTE: I'm slightly confused by what you are trying to hide - label or subform

[lccrews]

The code I used in the NoData event works quite well to hide subforms that have no data. I simply set the CanShrink property to Yes and presto. I am trying to hide the labels associated to them. That's the bugger. Also, I'm unable to find the "Format" property. Could you snap a screen shot? I'm using Access 2016.

[isladogs]

Yes it will hide subforms but that's not what you were asking about

Click on the relevant section header in the main form e.g. detail then use the On Format event:



HTH

[lccrews]

Thanks for that!
How do I modify this code to accomodate multiple labels associated to multiple different subforms? I can't even get it to work with one. The label is still visible.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([qry_BP40_Gummy_FinalComposition_Orange subform]) Then
Label55.Visible = False
Else
Label55.Visible = True
End If
End Sub

[June7]

However, the labels are in the Page Header section. Use that section's Format event.

Don't really understand why you want to do this. Why not just let the labels show? Why even generate report if there is no data?

[isladogs]

I don't believe you can do IsNull on a subreport like that

Perhaps you could do a DCount on the subform recordsource
Assuming this is the query qry_BP40_Gummy_FinalComposition_Orange:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If DCount("*","qry_BP40_Gummy_FinalComposition_Orange") = 0 Then Label55.Visible = False

End Sub

If your label is normally visible, then you don't need the Else part

One other thing. If the label is bound to the subform & you are also hiding the subform, it SHOULD automatically hide the label with NO code!

P.S. STRONGLY recommend you rename your query & subform something simpler with no underscores or spaces
e.g. fsubFinalComposition, qryFinalComposition

[lccrews]

The reason I'm doing this:

I'm creating a report for recipes that may have any combination of the following flavors: orange, strawberry, cranberry, etc.... Not all recipes will have every flavor and I need to suppress that label.

I've done it before, but I can't remember it for the life of me. I used an =iif statement in the label and it disappeared as expected.


Colin,
Thanks for that tweak. It works perfectly now. I assume I can add as many similar statements as necessary.

[June7]

How did you use an =IIf in a label? Label control does not have ControlSource property and the Caption property cannot be a conditional expression, at least not in 2010.

[isladogs]

Glad its now working
Yes you can repeat as often as necessary but as June rightly said, not with an IIf statement as it won't work
Don't confuse If and IIf

BUT do try having bound labels so these disappear automatically if you hide the subform - MUCH easier!

[June7]

@ridders52, did you mean 'associated' labels? AFAIK, can't be associated when in different section.

[isladogs]

Sorry - I did mean associated labels.
I can't see any mention of the label being in a different section to the subform - they aren't normally - but perhaps I missed that?

[June7]

You're right, I was seeing your image in post 4 as the OP's situation.