Set report controls visible property depending on value

[Clint Marshall]

I'm struggling to control whether a text box in a subreport prints. I'm
trying to set the control's visible property (using VBA) to true or false
depending on the value of the control.
I'm doing this because depending on the record I'm dealing with, only one of
two fields will appear and I want to show different data depending on the
data in one of the two fields.

Unfortunately, I can't seem to get access to the value of the control from
within VBA. Despite trying many different formats to access the value, I
get either a"can't find the field" or a "has no value" error.
I've tried things like: If [txtValue] is null then...
If me.[txtValue] is null then...
If Query.qrySR1.[txtValue] is null then...


Report: rptReport1
SubReport: rptSR1
Query used in report: qryReport1
Query used in subreport: qrySR1
Field from query qrySR1 whose value I'm trying to access (also the control
name): txtValue

Here are some specific questions:

1) Is controlling the control's visible property from VBA the appropriate
way to show or hide a control in a report?
2) If so, under what event should I be placing my code?
3) How do I access the variable's value? What is the proper code to address
the value?
4) How else can I accomplish this?

Thank you!

-Clint Marshall

 

[Duane Hookom]

Assuming the name of your text box is txtValue then you should be able to
use something like:

Me.txtValue.Visible = IsNull(Me.txtValue)
or
Me.txtValue.Visible = Not IsNull(Me.txtValue)

The code should be in the On Format event of the section containing the
control(s).

 

[Wayne Morgan]

The correct VBA syntax to check for a Null value in the control is
IsNull(Me.txtValue). You would set the visible property in the Format event
of the section where the control is located. If you are trying to hide the
control when Null to get rid of a vertical space it creates in the report,
you may also want to check the Can Shrink property of the control and
possibly the Can Shrink property of the section the control is in.

 

[Clint Marshall]

Great! Thank you!

Is there any way to identify whether a control has a null value for all
instances so that I can hide the column altogether in the subreport?

What would the code be to achieve this (test for all nulls and hide the
entire column)?

-Clint

Assuming the name of your text box is txtValue then you should be able to
use something like:

Me.txtValue.Visible = IsNull(Me.txtValue)
or
Me.txtValue.Visible = Not IsNull(Me.txtValue)

The code should be in the On Format event of the section containing the
control(s).

--
Duane Hookom
MS Access MVP
--

I'm struggling to control whether a text box in a subreport prints. I'm
trying to set the control's visible property (using VBA) to true or false
depending on the value of the control.
I'm doing this because depending on the record I'm dealing with, only one
of two fields will appear and I want to show different data depending on
the data in one of the two fields.

Unfortunately, I can't seem to get access to the value of the control
from within VBA. Despite trying many different formats to access the
value, I get either a"can't find the field" or a "has no value" error.
I've tried things like: If [txtValue] is null then...
If me.[txtValue] is null then...
If Query.qrySR1.[txtValue] is null then...


Report: rptReport1
SubReport: rptSR1
Query used in report: qryReport1
Query used in subreport: qrySR1
Field from query qrySR1 whose value I'm trying to access (also the
control name): txtValue

Here are some specific questions:

1) Is controlling the control's visible property from VBA the appropriate
way to show or hide a control in a report?
2) If so, under what event should I be placing my code?
3) How do I access the variable's value? What is the proper code to
address the value?
4) How else can I accomplish this?

Thank you!

-Clint Marshall

 

[Clint Marshall]

Great! Thank you!

Is there any way to identify whether a control has a null value for all
instances so that I can hide the column altogether in the subreport?

What would the code be to achieve this (test for all nulls and hide the
entire column)?

-Clint

The correct VBA syntax to check for a Null value in the control is
IsNull(Me.txtValue). You would set the visible property in the Format
event of the section where the control is located. If you are trying to
hide the control when Null to get rid of a vertical space it creates in
the report, you may also want to check the Can Shrink property of the
control and possibly the Can Shrink property of the section the control is
in.

--
Wayne Morgan
MS Access MVP

I'm struggling to control whether a text box in a subreport prints. I'm
trying to set the control's visible property (using VBA) to true or false
depending on the value of the control.
I'm doing this because depending on the record I'm dealing with, only one
of two fields will appear and I want to show different data depending on
the data in one of the two fields.

Unfortunately, I can't seem to get access to the value of the control
from within VBA. Despite trying many different formats to access the
value, I get either a"can't find the field" or a "has no value" error.
I've tried things like: If [txtValue] is null then...
If me.[txtValue] is null then...
If Query.qrySR1.[txtValue] is null then...


Report: rptReport1
SubReport: rptSR1
Query used in report: qryReport1
Query used in subreport: qrySR1
Field from query qrySR1 whose value I'm trying to access (also the
control name): txtValue

Here are some specific questions:

1) Is controlling the control's visible property from VBA the appropriate
way to show or hide a control in a report?
2) If so, under what event should I be placing my code?
3) How do I access the variable's value? What is the proper code to
address the value?
4) How else can I accomplish this?

Thank you!

-Clint Marshall

 

[Duane Hookom]

The txtValue text box will not be visible for any record if all values are
null. If you want to hide the column label in the report header section, you
could use add a text box to the subreport header with a control source like:

=IIf(Count([YourField])>0,"Your Field Caption","")


--
Duane Hookom
MS Access MVP
--

Great! Thank you!

Is there any way to identify whether a control has a null value for all
instances so that I can hide the column altogether in the subreport?

What would the code be to achieve this (test for all nulls and hide the
entire column)?

-Clint

Assuming the name of your text box is txtValue then you should be able to
use something like:

Me.txtValue.Visible = IsNull(Me.txtValue)
or
Me.txtValue.Visible = Not IsNull(Me.txtValue)

The code should be in the On Format event of the section containing the
control(s).

--
Duane Hookom
MS Access MVP
--

I'm struggling to control whether a text box in a subreport prints. I'm
trying to set the control's visible property (using VBA) to true or
false depending on the value of the control.
I'm doing this because depending on the record I'm dealing with, only
one of two fields will appear and I want to show different data
depending on the data in one of the two fields.

Unfortunately, I can't seem to get access to the value of the control
from within VBA. Despite trying many different formats to access the
value, I get either a"can't find the field" or a "has no value" error.
I've tried things like: If [txtValue] is null then...
If me.[txtValue] is null then...
If Query.qrySR1.[txtValue] is null then...


Report: rptReport1
SubReport: rptSR1
Query used in report: qryReport1
Query used in subreport: qrySR1
Field from query qrySR1 whose value I'm trying to access (also the
control name): txtValue

Here are some specific questions:

1) Is controlling the control's visible property from VBA the
appropriate way to show or hide a control in a report?
2) If so, under what event should I be placing my code?
3) How do I access the variable's value? What is the proper code to
address the value?
4) How else can I accomplish this?

Thank you!

-Clint Marshall