formatting textboxes from VBA to display those with data

[Jo]

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

 

[Marshall Barton]

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.

 

[Jo]

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

 

[Jo]

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]

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?

 

[Jo]

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

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]

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,
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.

 

[Marshall Barton]

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.