By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Conditional formatting problem

P: n/a
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(CtlName 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.
Jun 10 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Lyn
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(CtlName 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(CtlName 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.
Jun 10 '07 #2

P: n/a
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(CtlName 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(CtlName 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
Jun 10 '07 #3

P: n/a
Lyn
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.
Jun 11 '07 #4

P: n/a
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.FormatConditions(1).BackColor = RGB(255,240,240)

--
Marsh
Jun 11 '07 #5

P: n/a
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(fieldbox),"",fieldbox)

hope it helps
bobh.

On Jun 9, 10:36 pm, Lyn <l.hanc...@iiNet.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(CtlName 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.

Jun 11 '07 #6

P: n/a
Lyn
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.FormatConditions(1).BackColor = RGB(255,240,240)
Thanks, I didn't know you could do that. It looks like a useful tip!

Cheers,
Lyn.
Jun 11 '07 #7

P: n/a
Lyn
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(fieldbox),"",fieldbox)

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.
Jun 11 '07 #8

P: n/a
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...@iiNet.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(fieldbox),"",fieldbox)
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 -

Jun 12 '07 #9

P: n/a
Lyn
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.
Jun 13 '07 #10

P: n/a
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
Jun 13 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.