Hiding text box in Report View

[wakeboarder141]

I have two text boxes on a report and would like to control which one is visible based on a value in another field. I can accomplish what I want in Print Preview by using VBA:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [TPR_Multplier] > "1" Then
Me.TPR2.Visible = True
Else
Me.TPR2.Visible = False
End If
End Sub

However, this does not affect the Report View. Is it possible to affect the visibility of text boxes in Report View? I have tried using Conditional Formatting to change the text color, but the issue there is that some users copy and paste data from the report and the sneaky white text still gets copied. I would need to have it totally hidden. Thank you.

 

[Pat Hartman]

However, this does not affect the Report View. Is it possible to affect the visibility of text boxes in Report View?

Your VBA code doesn't run in Report View. Try using Conditional formatting. You may need to use boxes to cover both options and manipulate the boxes.

 

[pbaldy]

FYI in Report view the Paint event can be used instead of the Format event.

 

[wakeboarder141]

Your VBA code doesn't run in Report View. Try using Conditional formatting. You may need to use boxes to cover both options and manipulate the boxes.

That explains why it isn't working. The problem with the conditional formatting route is that I need these these boxes to be each other and only have one of them visible at a time. Does conditional formatting support something other than changing colors that might work?

FYI in Report view the Paint event can be used instead of the Format event.

When I try using the code in the OnPaint event from my detail section I get "Run-time error 32521: You can't change the value of this property in the OnPaint event.

 

[DHookom]

I have two text boxes on a report and would like to control which one is visible based on a value in another field. I can accomplish what I want in Print Preview by using VBA:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [TPR_Multplier] > "1" Then
Me.TPR2.Visible = True
Else
Me.TPR2.Visible = False
End If
End Sub

However, this does not affect the Report View. Is it possible to affect the visibility of text boxes in Report View? I have tried using Conditional Formatting to change the text color, but the issue there is that some users copy and paste data from the report and the sneaky white text still gets copied. I would need to have it totally hidden. Thank you.

I would make changes to the record source using IIf() statements. A replacement column might be:
TPT_2: IIf(TPR_Multiplier="1", TPR2, Null)

 

[pbaldy]

I haven't tested visibility. In the app I looked at to verify the event, I'm setting different border colors and such.

 

[wakeboarder141]

I would make changes to the record source using IIf() statements. A replacement column might be:
TPT_2: IIf(TPR_Multiplier="1", TPR2, Null)

This is a new one to me. Can you help me understand what this is doing? Do I use this as the control source? Thanks.

 

[DHookom]

This could be a control source
=IIf(TPR_Multiplier="1", TPR2, Null)
Or as a calculated column in a query.

 

[Pat Hartman]

If you are using standard colors and printing on "white" paper, usually making the text white works to hide it.

 

[wakeboarder141]

If you are using standard colors and printing on "white" paper, usually making the text white works to hide it.

The text does show through a little because of the alternating row color. The printing is good enough, but when users copy a row from the report view, the white text is also being copied.

 

[wakeboarder141]

This could be a control source
=IIf(TPR_Multiplier="1", TPR2, Null)
Or as a calculated column in a query.

This has gotten me very close. I think I just need some help with formatting now. I have 2 text boxes overlapping each other, TPR3 and TPR Amt. TPR3 control source is =IIf([TPR Multplier]>"1",[TPR2],Null). TPR Amt is =IIf([TPR Multplier]<"1",[TPR Amount],Null). TPR2 refers to a hidden text box with a source of =[TPR Multplier] & "/" & [TPR Amount]. It is functioning, but I need the numbers to all show as currency. I have the format set to currency on those 3 text boxes, and it works if [TPR Multplier]<2 and displays as $0.00. However, when [TPR Multplier]>1, I am getting 2/3 instead of 2/$3.00 as intended. Hopefully that makes sense. Thanks!

 

[DHookom]

Try change the control source to:
=IIf([TPR Multplier]>"1",Format([TPR2], "Currency"),Null)