Conditional formatting problem

[Lyn]

Hi,
I would like to make a bound text box not visible if it is empty (not just
disable it). This option is not available from the standard conditional
formatting feature (at least, not that I can find).

I thought of calling a class module function via Expression Builder for the
control. I have written a basic function, but don't seem to be able to
pass the relevant arguments correctly. It seems to me that I need to pass
two arguments: the name of the control, and the value of the database
field. The function is intended to set the control's .Visible property to
FALSE (if it is empty) and to return the value to display (blank or some
other value). And I would like to make the function generic so that I can
use it in future for any control on any form.

This is what I have so far for the function:

'---------------
Public Function HideIfEmpty(Ctl Name As Variant, ctlValue As Variant) _
As Variant

Dim ctl As Control

Set ctl = CtlName
HideIfEmpty = ctlValue
ctl.Visible = Iif(Nz(ctlValue ,"") = "", False, True)

End Function
'--------------

I think that my main problem is in passing the CtlName value. This would
need to be a fully qualified pathname if the function is to be generic, but
selecting the control using Expression Builder, only the local controlname
is inserted. Thus the Expression Builder result looks like:

=HideIfEmpty([txtControlname],[FieldValue])

When running the form, the function gives an error 2427 "You entered an
expression that has no value."

Obvioulsy I am not doing this correctly. Any pointers would be much
appreciated!

Cheers,
Lyn.

[Lyn]

Re: Conditional formatting problem

On Sun, 10 Jun 2007 12:36:34 +1000, Lyn wrote:

Hi,
I would like to make a bound text box not visible if it is empty (not just
disable it). This option is not available from the standard conditional
formatting feature (at least, not that I can find).
>

[snip]

This is what I have so far for the function:
>
'---------------
Public Function HideIfEmpty(Ctl Name As Variant, ctlValue As Variant) _
As Variant
>
Dim ctl As Control
>
Set ctl = CtlName
HideIfEmpty = ctlValue
ctl.Visible = Iif(Nz(ctlValue ,"") = "", False, True)
>
End Function
'--------------
>
I think that my main problem is in passing the CtlName value.

OK, I've got it half working. I was agonizing over the problem in the
shower (as you do) and it came to me that I shouldn't be passing the
control NAME as a Variant, but the control itself as a control object.
Changed the function header to the following:

Public Function HideIfEmpty(Ctl Name As Control, ctlValue As Variant) _
As Variant

The situation now in the continous subform (where the control in
question is located), is that if the text box is blank in all rows, then
the text box will be not Visible in all rows. However, if the text box in
ANY row contains non-blank data, it will be visible in ALL rows.

I had thought that any Expression Builder expression for a control was
resolved on a row-by-row basis. Apparently this is not always the case.

So now my question has changed to: how can I do this on a row-by-row basis
(rather than the all-or-nothing result I am getting thus far).

Thanks again for any pearls of wisdom!

Cheers,
Lyn.

[Marshall Barton]

Re: Conditional formatting problem

Lyn wrote:

>On Sun, 10 Jun 2007 12:36:34 +1000, Lyn wrote:
>

>Hi,
>I would like to make a bound text box not visible if it is empty (not just
>disable it). This option is not available from the standard conditional
>formatting feature (at least, not that I can find).
>>

>[snip]
>

>This is what I have so far for the function:
>>
>'---------------
>Public Function HideIfEmpty(Ctl Name As Variant, ctlValue As Variant) _
>As Variant
>>
>Dim ctl As Control
>>
>Set ctl = CtlName
>HideIfEmpty = ctlValue
>ctl.Visible = Iif(Nz(ctlValue ,"") = "", False, True)
>>
>End Function
>'--------------
>>
>I think that my main problem is in passing the CtlName value.

>
>OK, I've got it half working. I was agonizing over the problem in the
>shower (as you do) and it came to me that I shouldn't be passing the
>control NAME as a Variant, but the control itself as a control object.
>Changed the function header to the following:
>
>Public Function HideIfEmpty(Ctl Name As Control, ctlValue As Variant) _
>As Variant
>
>The situation now in the continous subform (where the control in
>question is located), is that if the text box is blank in all rows, then
>the text box will be not Visible in all rows. However, if the text box in
>ANY row contains non-blank data, it will be visible in ALL rows.
>
>I had thought that any Expression Builder expression for a control was
>resolved on a row-by-row basis. Apparently this is not always the case.
>
>So now my question has changed to: how can I do this on a row-by-row basis
>(rather than the all-or-nothing result I am getting thus far).

The point you are missing is that there is only **one** text
box. This means that there is only one set of properties
for you to manipulate so when you set the Visible property,
it applies to all the copies if the control.

The only things that vary from row to row in a continuous
form is the value of the control and how it's formatted via
the Format property and Conditional Formatting.

Since you can not get the desired effect by setting the
Visible property (or any other property), you should
consider simulating it by using Condiftional Formatting.
Perhaps by setting the back color to the same color as the
form's back color.

--
Marsh

[Lyn]

Re: Conditional formatting problem

On Sun, 10 Jun 2007 10:39:20 -0500, Marshall Barton wrote:

>
The point you are missing is that there is only **one** text
box. This means that there is only one set of properties
for you to manipulate so when you set the Visible property,
it applies to all the copies if the control.

This much had become obvious. I had a similar problem once before when I
was trying to list thumbnail pictures in an Image control -- it can't be
done for the same reason.

The only things that vary from row to row in a continuous
form is the value of the control and how it's formatted via
the Format property and Conditional Formatting.

It was because you can format data on a row-by-row basis that I was hoping
it might be possible to do other things this way. My hope was in vain!
(But worth trying :-)

Since you can not get the desired effect by setting the
Visible property (or any other property), you should
consider simulating it by using Condiftional Formatting.
Perhaps by setting the back color to the same color as the
form's back color.

This was always my fallback position. But before resorting to that, I
thought I would see if there were other solutions. Apparently not. Pity
that Conditional Formatting is so restrictive -- you can toggle the Enable
property but not the Lock or Visible property -- and the range of available
colours is quite small.

Anyway, thanks for your response. You have confirmed what I suspected!

Cheers,
Lyn.

[Marshall Barton]

Re: Conditional formatting problem

Lyn wrote:

>On Sun, 10 Jun 2007 10:39:20 -0500, Marshall Barton wrote:
>

>>
>The point you are missing is that there is only **one** text
>box. This means that there is only one set of properties
>for you to manipulate so when you set the Visible property,
>it applies to all the copies if the control.

>This much had become obvious. I had a similar problem once before when I
>was trying to list thumbnail pictures in an Image control -- it can't be
>done for the same reason.
>

>The only things that vary from row to row in a continuous
>form is the value of the control and how it's formatted via
>the Format property and Conditional Formatting.

>It was because you can format data on a row-by-row basis that I was hoping
>it might be possible to do other things this way. My hope was in vain!
>(But worth trying :-)
>

>Since you can not get the desired effect by setting the
>Visible property (or any other property), you should
>consider simulating it by using Condiftional Formatting.
>Perhaps by setting the back color to the same color as the
>form's back color.

>This was always my fallback position. But before resorting to that, I
>thought I would see if there were other solutions. Apparently not. Pity
>that Conditional Formatting is so restrictive -- you can toggle the Enable
>property but not the Lock or Visible property -- and the range of available
>colours is quite small.

The range of colors you can pick from the CF color pickers
is limited, but you can use an event procedure (Open, Load)
to set any color you want:

Me.textbox.Form atConditions(1) .BackColor = RGB(255,240,240 )

--
Marsh

[bobh]

Re: Conditional formatting problem

Hi,

I don't know if this will help but I thought I'd offer it anyway and
is how I've done something similar in Access97

on the continuous form for the field you are wanting to change/work
with

1. put the control(field) on the continuous form where you want it and
make it's visible property 'false'
2. make 1 unbound textbox the same size as the real field and delete
the labelbox part of it
3. move the unbound textbox on top of the field box
4. add the code to the control source of the unbound textbox
ex: IIF(IsNull(fiel dbox),"",fieldb ox)

hope it helps
bobh.

On Jun 9, 10:36 pm, Lyn <l.hanc...@iiNe t.net.auwrote:

Hi,
I would like to make a bound text box not visible if it is empty (not just
disable it). This option is not available from the standard conditional
formatting feature (at least, not that I can find).
>
I thought of calling a class module function via Expression Builder for the
control. I have written a basic function, but don't seem to be able to
pass the relevant arguments correctly. It seems to me that I need to pass
two arguments: the name of the control, and the value of the database
field. The function is intended to set the control's .Visible property to
FALSE (if it is empty) and to return the value to display (blank or some
other value). And I would like to make the function generic so that I can
use it in future for any control on any form.
>
This is what I have so far for the function:
>
'---------------
Public Function HideIfEmpty(Ctl Name As Variant, ctlValue As Variant) _
As Variant
>
Dim ctl As Control
>
Set ctl = CtlName
HideIfEmpty = ctlValue
ctl.Visible = Iif(Nz(ctlValue ,"") = "", False, True)
>
End Function
'--------------
>
I think that my main problem is in passing the CtlName value. This would
need to be a fully qualified pathname if the function is to be generic, but
selecting the control using Expression Builder, only the local controlname
is inserted. Thus the Expression Builder result looks like:
>
=HideIfEmpty([txtControlname],[FieldValue])
>
When running the form, the function gives an error 2427 "You entered an
expression that has no value."
>
Obvioulsy I am not doing this correctly. Any pointers would be much
appreciated!
>
Cheers,
Lyn.

[Lyn]

Re: Conditional formatting problem

On Mon, 11 Jun 2007 08:25:44 -0500, Marshall Barton wrote:

The range of colors you can pick from the CF color pickers
is limited, but you can use an event procedure (Open, Load)
to set any color you want:
>
Me.textbox.Form atConditions(1) .BackColor = RGB(255,240,240 )

Thanks, I didn't know you could do that. It looks like a useful tip!

Cheers,
Lyn.

[Lyn]

Re: Conditional formatting problem

On Mon, 11 Jun 2007 12:54:18 -0700, bobh wrote:

Hi,
>
I don't know if this will help but I thought I'd offer it anyway and
is how I've done something similar in Access97
>
on the continuous form for the field you are wanting to change/work
with
>
1. put the control(field) on the continuous form where you want it and
make it's visible property 'false'
2. make 1 unbound textbox the same size as the real field and delete
the labelbox part of it
3. move the unbound textbox on top of the field box
4. add the code to the control source of the unbound textbox
ex: IIF(IsNull(fiel dbox),"",fieldb ox)
>
hope it helps
bobh.

Bob, thanks for the advice. I am not sure I fully understand it, so let me
recap your points:

1. Create a bound textbox control which is not visible. Its label will
therefore also not be visible. Does this textbox remain invisible?

2. If the unbound textbox has its label deleted, then there will be no
label at all for this field?

3. What we have visible now is an unbound textbox and no label.

4. The example control source code for the visible textbox basically
causes it to display what is in the invisible bound control. How is this
different from just having the original bound textbox visible (other than
that there is now no visible label)?

I am obviously missing something important here. Could you please be
patient with me and explain further?

Many thanks,
Lyn.

[bobh]

Re: Conditional formatting problem

Hi Lyn,
Lets be sure I understand first... I had the impression that you were
working with a continuous form, correct? if so then you normally
delete the labels from the textboxes in the detail section (at least I
do) then I add a labelbox to the header section

1. yes
2. you create a labelbox in the header section for it
3. correct
4. for your need it may not be any different, I offered this up as a
possibility in assisting you either directly or by causing some
creative thinking as this does allow you to use the properities of the
textboxes(font color/size/type, borders,etc..) and I was keying off of
your first sectence in your original note. "I would like to make a
bound text box not visible if it is empty (not just disable it)."
bobh.


On Jun 11, 7:13 pm, Lyn <l.hanc...@iiNe t.net.auwrote:

On Mon, 11 Jun 2007 12:54:18 -0700,bobhwrote:

Hi,

>

I don't know if this will help but I thought I'd offer it anyway and
is how I've done something similar in Access97

>

on the continuous form for the field you are wanting to change/work
with

>

1. put the control(field) on the continuous form where you want it and
make it's visible property 'false'
2. make 1 unbound textbox the same size as the real field and delete
the labelbox part of it
3. move the unbound textbox on top of the field box
4. add the code to the control source of the unbound textbox
ex: IIF(IsNull(fiel dbox),"",fieldb ox)

>

hope it helps
bobh.

>
Bob, thanks for the advice. I am not sure I fully understand it, so let me
recap your points:
>
1. Create a bound textbox control which is not visible. Its label will
therefore also not be visible. Does this textbox remain invisible?
>
2. If the unbound textbox has its label deleted, then there will be no
label at all for this field?
>
3. What we have visible now is an unbound textbox and no label.
>
4. The example control source code for the visible textbox basically
causes it to display what is in the invisible bound control. How is this
different from just having the original bound textbox visible (other than
that there is now no visible label)?
>
I am obviously missing something important here. Could you please be
patient with me and explain further?
>
Many thanks,
Lyn.- Hide quoted text -
>
- Show quoted text -

[Lyn]

Re: Conditional formatting problem

On Tue, 12 Jun 2007 08:15:01 -0700, bobh wrote:

Hi Lyn,
Lets be sure I understand first... I had the impression that you were
working with a continuous form, correct? if so then you normally
delete the labels from the textboxes in the detail section (at least I
do) then I add a labelbox to the header section
>
1. yes
2. you create a labelbox in the header section for it
3. correct
4. for your need it may not be any different, I offered this up as a
possibility in assisting you either directly or by causing some
creative thinking as this does allow you to use the properities of the
textboxes(font color/size/type, borders,etc..) and I was keying off of
your first sectence in your original note. "I would like to make a
bound text box not visible if it is empty (not just disable it)."
bobh.
>

Hi Bob,
It seems that were at cross-purposes -- my fault. I don't have a datasheet
type list (or even a tabular list) with column headers. For various
reasons, including a couple of lengthy fields, the relevant fields will not
fit on one line. So the fields for each row are spread over two lines with
a separator line between records. Consequently, I use side labels rather
than column labels. The fields in the two lines don't line up with each
other, so I can't even have two-line column headers. This means there
would be an issue with labelling using your suggestion that I would have to
find a solution for (which may be possible).

However, I am still unsure about your point 4. I want the text box when
empty to be hidden (.Visible = False) to cut out some of the "noise" from
the row when the field contains no data. So I am looking for a way to
control the visibility of that section of the form for that row. I am
probably being dense, but I still can't see if your suggestion is what I
need.

I am about ready to move one. Another suggestion was made concerning
Conditional Formatting which may give me a compromise solution.

Thanks for your help.

Cheers,
Lyn.

[Marshall Barton]

Re: Conditional formatting problem

Lyn wrote:

>It seems that were at cross-purposes -- my fault. I don't have a datasheet
>type list (or even a tabular list) with column headers. For various
>reasons, including a couple of lengthy fields, the relevant fields will not
>fit on one line. So the fields for each row are spread over two lines with
>a separator line between records. Consequently, I use side labels rather
>than column labels. The fields in the two lines don't line up with each
>other, so I can't even have two-line column headers. This means there
>would be an issue with labelling using your suggestion that I would have to
>find a solution for (which may be possible).
>
>However, I am still unsure about your point 4. I want the text box when
>empty to be hidden (.Visible = False) to cut out some of the "noise" from
>the row when the field contains no data. So I am looking for a way to
>control the visibility of that section of the form for that row. I am
>probably being dense, but I still can't see if your suggestion is what I
>need.
>
>I am about ready to move one. Another suggestion was made concerning
>Conditional Formatting which may give me a compromise solution.

So, the problem is not to just make an empty text box
invisible, but to also make it's lable invisible. The CF
approach can be also be used for the labels by changing them
to text boxes with a control source expression like:
=IIf([real text box] Is Null, Null, "the label caption")

--
Marsh