Hi,

I have a simple access report that prints data based on a query. I have 6 
bound textboxes tied to the fields from this query. My issue is: I am trying 
to avoid displaying the textboxes when there is no value for them in the 
database. These are basically dates and some of them don't have any data in 
them. I want to avoid displaying such textboxes. So in the detail section's 
on-print event I wrote the following code thinking that I will accomplish 
this task. I can see the values in the debugger but not when the report has 
finished printing on the screen? - the textboxes don't appear even when there 
is data in them!!

Code to carry out hiding/displaying the textboxes:
If objRS("OPTION1") = " " Then
        Me.lblOption1.Properties("Visible") = False
        Me.txtOpt1Start.Properties("Visible") = False
        Me.txtOpt1End.Properties("Visible") = False
        Me.lnOpt1.Properties("Visible") = False
ElseIf objRS("OPTION1") <> " " Then
        Me.lblOption1.Properties("Visible") = True
        Me.txtOpt1Start.Properties("Visible") = True
        Me.txtOpt1End.Properties("Visible") = True
        Me.lnOpt1.Properties("Visible") = True
End If

Instead of hiding/displaying such textboxes the textboxes are completely 
hidden and only two textboxes appear for the base year date and the base year 
end!

What is it that I am doing wrong here?Has a question like this answered 
before? if so, could anyone of you please send me the link? Thanks in advance 
for all your efforts. 

Jo

Jo wrote:
>I have a simple access report that prints data based on a query. I have 6 
>bound textboxes tied to the fields from this query. My issue is: I am trying 
>to avoid displaying the textboxes when there is no value for them in the 
>database. These are basically dates and some of them don't have any data in 
>them. I want to avoid displaying such textboxes. So in the detail section's 
>on-print event I wrote the following code thinking that I will accomplish 
>this task. I can see the values in the debugger but not when the report has 
>finished printing on the screen? - the textboxes don't appear even when there 
>is data in them!!
>
>Code to carry out hiding/displaying the textboxes:
>If objRS("OPTION1") = " " Then
>        Me.lblOption1.Properties("Visible") = False
>        Me.txtOpt1Start.Properties("Visible") = False
>        Me.txtOpt1End.Properties("Visible") = False
>        Me.lnOpt1.Properties("Visible") = False
>ElseIf objRS("OPTION1") <> " " Then
>        Me.lblOption1.Properties("Visible") = True
>        Me.txtOpt1Start.Properties("Visible") = True
>        Me.txtOpt1End.Properties("Visible") = True
>        Me.lnOpt1.Properties("Visible") = True
>End If
>
>Instead of hiding/displaying such textboxes the textboxes are completely 
>hidden and only two textboxes appear for the base year date and the base year 
>end!


I don't see how that code can hide anything.

What is objRS("OPTION1")?  It sort of looks like it's a
field in a recordset??

normally a field in a table will not have a single space
character.  They will either be Null (recommended) or, if
Required and AllowZeroLength are set to Yes (generally, not
a good idea), it will have "".

If you are using CanShrink with these text boxes, then you
must put the code in the section's Format event.  The Print
event is too late to affect how things are laid out.

I think all you need is code like:
	Me.txtOption1.Visible = Not IsNull(Me.txtOption1)
	Me.txtOpt1Start.Visible = Not IsNull(Me.txtOpt1Start)
	Me.txtOpt1End.Visible = Not IsNull(Me.txtOpt1End)
		. . .

Note that if you make a control invisible, its attached
label, if there is one, will automatically be made
invisible.

-- 
Marsh
MVP [MS Access]

Hi Marshall,

Thanks for the reply. 

> What is objRS("OPTION1")?  It sort of looks like it's a
> field in a recordset?? - Yes it is a recordset field. I first had the recordset field set to "" with no space but I wasn't sure why the code wouldn't execute so, thought may be there is a space and hence changed it!! probably should have used trim function?

I don't have the canShrink property set to Yes so, I am leaving the code in 
the detail_onprint event. Also, was curious to know if it's Ok to leave the 
visible to false?

I will keep you posted on the outcome. I had trouble looking up for the 
reply as I remember posting the question under access.reports and hence the 
delay in getting back to you. sorry about that?

Thanks again!
Jo




"Marshall Barton" wrote:

> Jo wrote:
> >I have a simple access report that prints data based on a query. I have 6 
> >bound textboxes tied to the fields from this query. My issue is: I am trying 
> >to avoid displaying the textboxes when there is no value for them in the 
> >database. These are basically dates and some of them don't have any data in 
> >them. I want to avoid displaying such textboxes. So in the detail section's 
> >on-print event I wrote the following code thinking that I will accomplish 
> >this task. I can see the values in the debugger but not when the report has 
> >finished printing on the screen? - the textboxes don't appear even when there 
> >is data in them!!
> >
> >Code to carry out hiding/displaying the textboxes:
> >If objRS("OPTION1") = " " Then
> >        Me.lblOption1.Properties("Visible") = False
> >        Me.txtOpt1Start.Properties("Visible") = False
> >        Me.txtOpt1End.Properties("Visible") = False
> >        Me.lnOpt1.Properties("Visible") = False
> >ElseIf objRS("OPTION1") <> " " Then
> >        Me.lblOption1.Properties("Visible") = True
> >        Me.txtOpt1Start.Properties("Visible") = True
> >        Me.txtOpt1End.Properties("Visible") = True
> >        Me.lnOpt1.Properties("Visible") = True
> >End If
> >
> >Instead of hiding/displaying such textboxes the textboxes are completely 
> >hidden and only two textboxes appear for the base year date and the base year 
> >end!
> 
> 
> I don't see how that code can hide anything.
> 
> What is objRS("OPTION1")?  It sort of looks like it's a
> field in a recordset??
> 
> normally a field in a table will not have a single space
> character.  They will either be Null (recommended) or, if
> Required and AllowZeroLength are set to Yes (generally, not
> a good idea), it will have "".
> 
> If you are using CanShrink with these text boxes, then you
> must put the code in the section's Format event.  The Print
> event is too late to affect how things are laid out.
> 
> I think all you need is code like:
> 	Me.txtOption1.Visible = Not IsNull(Me.txtOption1)
> 	Me.txtOpt1Start.Visible = Not IsNull(Me.txtOpt1Start)
> 	Me.txtOpt1End.Visible = Not IsNull(Me.txtOpt1End)
> 		. . .
> 
> Note that if you make a control invisible, its attached
> label, if there is one, will automatically be made
> invisible.
> 
> -- 
> Marsh
> MVP [MS Access]
> 

Hi Marshall,

I still don't have any luck with this! The report still prints just the base 
start and end dates even when there is data for option years? I changed the 
code to what you had suggested but got a runtime error saying that "you 
entered an expression that has no value" so I modified the code to something 
like this:

If Trim(objRS("OPTION1")) = "" Then
        Me.txtOpt1Start.Properties("Visible") = IsNull(objRS("OPTION1"))
        Me.txtOpt1End.Properties("Visible") = IsNull(objRS("OPTION1"))
        Me.lnOpt1.Properties("Visible") = IsNull(objRS("OPTION1"))
End If

Also, 
> Note that if you make a control invisible, its attached
> label, if there is one, will automatically be made
> invisible. - This is not happening either!

Am i missing anything here again? All I want to do is make the option year 
textboxes display/hide depending on availability of data for the option 
years. 

Thanks for your help,
Jo

"Marshall Barton" wrote:

> Jo wrote:
> >I have a simple access report that prints data based on a query. I have 6 
> >bound textboxes tied to the fields from this query. My issue is: I am trying 
> >to avoid displaying the textboxes when there is no value for them in the 
> >database. These are basically dates and some of them don't have any data in 
> >them. I want to avoid displaying such textboxes. So in the detail section's 
> >on-print event I wrote the following code thinking that I will accomplish 
> >this task. I can see the values in the debugger but not when the report has 
> >finished printing on the screen? - the textboxes don't appear even when there 
> >is data in them!!
> >
> >Code to carry out hiding/displaying the textboxes:
> >If objRS("OPTION1") = " " Then
> >        Me.lblOption1.Properties("Visible") = False
> >        Me.txtOpt1Start.Properties("Visible") = False
> >        Me.txtOpt1End.Properties("Visible") = False
> >        Me.lnOpt1.Properties("Visible") = False
> >ElseIf objRS("OPTION1") <> " " Then
> >        Me.lblOption1.Properties("Visible") = True
> >        Me.txtOpt1Start.Properties("Visible") = True
> >        Me.txtOpt1End.Properties("Visible") = True
> >        Me.lnOpt1.Properties("Visible") = True
> >End If
> >
> >Instead of hiding/displaying such textboxes the textboxes are completely 
> >hidden and only two textboxes appear for the base year date and the base year 
> >end!
> 
> 
> I don't see how that code can hide anything.
> 
> What is objRS("OPTION1")?  It sort of looks like it's a
> field in a recordset??
> 
> normally a field in a table will not have a single space
> character.  They will either be Null (recommended) or, if
> Required and AllowZeroLength are set to Yes (generally, not
> a good idea), it will have "".
> 
> If you are using CanShrink with these text boxes, then you
> must put the code in the section's Format event.  The Print
> event is too late to affect how things are laid out.
> 
> I think all you need is code like:
> 	Me.txtOption1.Visible = Not IsNull(Me.txtOption1)
> 	Me.txtOpt1Start.Visible = Not IsNull(Me.txtOpt1Start)
> 	Me.txtOpt1End.Visible = Not IsNull(Me.txtOpt1End)
> 		. . .
> 
> Note that if you make a control invisible, its attached
> label, if there is one, will automatically be made
> invisible.
> 
> -- 
> Marsh
> MVP [MS Access]
> 

Sorry, but at this point I am completely lost.  I thought
you were trying to hide a text box when the value in a
record was null (or a ZLS??).  Now it sounds more like you
want to hide a text box when there is no data in an entire
column (in the record source table/query).  These are very
different objectives and the latter goal raises additional
questions about what to do with the empty space (presumably
a column on the report.)

You still haven't explained what objRS("OPTION1")
represents.  Are you only posting a small part of the code
you are trying to get working?
-- 
Marsh
MVP [MS Access]


Jo wrote:
>I still don't have any luck with this! The report still prints just the base 
>start and end dates even when there is data for option years? I changed the 
>code to what you had suggested but got a runtime error saying that "you 
>entered an expression that has no value" so I modified the code to something 
>like this:
>
>If Trim(objRS("OPTION1")) = "" Then
>        Me.txtOpt1Start.Properties("Visible") = IsNull(objRS("OPTION1"))
>        Me.txtOpt1End.Properties("Visible") = IsNull(objRS("OPTION1"))
>        Me.lnOpt1.Properties("Visible") = IsNull(objRS("OPTION1"))
>End If
>
>Also, 
>> Note that if you make a control invisible, its attached
>> label, if there is one, will automatically be made
>> invisible. 
> This is not happening either!
>
>Am i missing anything here again? All I want to do is make the option year 
>textboxes display/hide depending on availability of data for the option 
>years. 
>
>
>> Jo wrote:
>> >I have a simple access report that prints data based on a query. I have 6 
>> >bound textboxes tied to the fields from this query. My issue is: I am trying 
>> >to avoid displaying the textboxes when there is no value for them in the 
>> >database. These are basically dates and some of them don't have any data in 
>> >them. I want to avoid displaying such textboxes. So in the detail section's 
>> >on-print event I wrote the following code thinking that I will accomplish 
>> >this task. I can see the values in the debugger but not when the report has 
>> >finished printing on the screen? - the textboxes don't appear even when there 
>> >is data in them!!
>> >
>> >Code to carry out hiding/displaying the textboxes:
>> >If objRS("OPTION1") = " " Then
>> >        Me.lblOption1.Properties("Visible") = False
>> >        Me.txtOpt1Start.Properties("Visible") = False
>> >        Me.txtOpt1End.Properties("Visible") = False
>> >        Me.lnOpt1.Properties("Visible") = False
>> >ElseIf objRS("OPTION1") <> " " Then
>> >        Me.lblOption1.Properties("Visible") = True
>> >        Me.txtOpt1Start.Properties("Visible") = True
>> >        Me.txtOpt1End.Properties("Visible") = True
>> >        Me.lnOpt1.Properties("Visible") = True
>> >End If
>> >
>> >Instead of hiding/displaying such textboxes the textboxes are completely 
>> >hidden and only two textboxes appear for the base year date and the base year 
>> >end!
>> 
>
>"Marshall Barton" wrote:
>> I don't see how that code can hide anything.
>> 
>> What is objRS("OPTION1")?  It sort of looks like it's a
>> field in a recordset??
>> 
>> normally a field in a table will not have a single space
>> character.  They will either be Null (recommended) or, if
>> Required and AllowZeroLength are set to Yes (generally, not
>> a good idea), it will have "".
>> 
>> If you are using CanShrink with these text boxes, then you
>> must put the code in the section's Format event.  The Print
>> event is too late to affect how things are laid out.
>> 
>> I think all you need is code like:
>> 	Me.txtOption1.Visible = Not IsNull(Me.txtOption1)
>> 	Me.txtOpt1Start.Visible = Not IsNull(Me.txtOpt1Start)
>> 	Me.txtOpt1End.Visible = Not IsNull(Me.txtOpt1End)
>> 		. . .
>> 
>> Note that if you make a control invisible, its attached
>> label, if there is one, will automatically be made
>> invisible.

Hi Marshall,

I am sorry couldn't reply to your questions yesterday as our network was 
down. I was able to figure out a way to do this report by using Union query. 
I had a different approach to begin with but turned out to be an easy Union 
query solution! Thanks for all your efforts once again.

Thanks,
Jo

"Marshall Barton" wrote:

> Sorry, but at this point I am completely lost.  I thought
> you were trying to hide a text box when the value in a
> record was null (or a ZLS??).  Now it sounds more like you
> want to hide a text box when there is no data in an entire
> column (in the record source table/query).  These are very
> different objectives and the latter goal raises additional
> questions about what to do with the empty space (presumably
> a column on the report.)
> 
> You still haven't explained what objRS("OPTION1")
> represents.  Are you only posting a small part of the code
> you are trying to get working?
> -- 
> Marsh
> MVP [MS Access]
> 
> 
> Jo wrote:
> >I still don't have any luck with this! The report still prints just the base 
> >start and end dates even when there is data for option years? I changed the 
> >code to what you had suggested but got a runtime error saying that "you 
> >entered an expression that has no value" so I modified the code to something 
> >like this:
> >
> >If Trim(objRS("OPTION1")) = "" Then
> >        Me.txtOpt1Start.Properties("Visible") = IsNull(objRS("OPTION1"))
> >        Me.txtOpt1End.Properties("Visible") = IsNull(objRS("OPTION1"))
> >        Me.lnOpt1.Properties("Visible") = IsNull(objRS("OPTION1"))
> >End If
> >
> >Also, 
> >> Note that if you make a control invisible, its attached
> >> label, if there is one, will automatically be made
> >> invisible. 
> > This is not happening either!
> >
> >Am i missing anything here again? All I want to do is make the option year 
> >textboxes display/hide depending on availability of data for the option 
> >years. 
> >
> >
> >> Jo wrote:
> >> >I have a simple access report that prints data based on a query. I have 6 
> >> >bound textboxes tied to the fields from this query. My issue is: I am trying 
> >> >to avoid displaying the textboxes when there is no value for them in the 
> >> >database. These are basically dates and some of them don't have any data in 
> >> >them. I want to avoid displaying such textboxes. So in the detail section's 
> >> >on-print event I wrote the following code thinking that I will accomplish 
> >> >this task. I can see the values in the debugger but not when the report has 
> >> >finished printing on the screen? - the textboxes don't appear even when there 
> >> >is data in them!!
> >> >
> >> >Code to carry out hiding/displaying the textboxes:
> >> >If objRS("OPTION1") = " " Then
> >> >        Me.lblOption1.Properties("Visible") = False
> >> >        Me.txtOpt1Start.Properties("Visible") = False
> >> >        Me.txtOpt1End.Properties("Visible") = False
> >> >        Me.lnOpt1.Properties("Visible") = False
> >> >ElseIf objRS("OPTION1") <> " " Then
> >> >        Me.lblOption1.Properties("Visible") = True
> >> >        Me.txtOpt1Start.Properties("Visible") = True
> >> >        Me.txtOpt1End.Properties("Visible") = True
> >> >        Me.lnOpt1.Properties("Visible") = True
> >> >End If
> >> >
> >> >Instead of hiding/displaying such textboxes the textboxes are completely 
> >> >hidden and only two textboxes appear for the base year date and the base year 
> >> >end!
> >> 
> >
> >"Marshall Barton" wrote:
> >> I don't see how that code can hide anything.
> >> 
> >> What is objRS("OPTION1")?  It sort of looks like it's a
> >> field in a recordset??
> >> 
> >> normally a field in a table will not have a single space
> >> character.  They will either be Null (recommended) or, if
> >> Required and AllowZeroLength are set to Yes (generally, not
> >> a good idea), it will have "".
> >> 
> >> If you are using CanShrink with these text boxes, then you
> >> must put the code in the section's Format event.  The Print
> >> event is too late to affect how things are laid out.
> >> 
> >> I think all you need is code like:
> >> 	Me.txtOption1.Visible = Not IsNull(Me.txtOption1)
> >> 	Me.txtOpt1Start.Visible = Not IsNull(Me.txtOpt1Start)
> >> 	Me.txtOpt1End.Visible = Not IsNull(Me.txtOpt1End)
> >> 		. . .
> >> 
> >> Note that if you make a control invisible, its attached
> >> label, if there is one, will automatically be made
> >> invisible.
> 

Jo wrote:
>I am sorry couldn't reply to your questions yesterday as our network was 
>down. I was able to figure out a way to do this report by using Union query. 
>I had a different approach to begin with but turned out to be an easy Union 
>query solution! Thanks for all your efforts once again.


That's ok.  At least you have it working and that is the
important thing.

-- 
Marsh
MVP [MS Access]