473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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(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
10 8539
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS process stays active and that process can only be...
4
by: Bradley | last post by:
I have an A2000 database in which I have a continuous form with a tick box. There is also a text box with a conditional format that is based on the expression , if it's true then change the...
2
by: Megan | last post by:
Can you write conditional VBA code that affects only one or two records on a continuous subform? I have a form with a subform on it. The parent/ child field that links the forms is CaseID. The...
8
by: Dimitri Furman | last post by:
Given: Access 2002/2003 A subform in datasheet or continuous view, placed on a tab page (this last may or may not matter) Conditional formatting applied to some controls on the subform - format...
2
by: Von Bailey | last post by:
I have a form where the conditional formatting is set on some fields to bold if certain conditions are met. However, when the conditions are met some of the data that is to bold is either not...
2
by: Sara | last post by:
The problem: Conditional formatting bold, red when field Value < date() sets the field background to white - always - whether condition is met or not. I want the field unfilled and just red/bold...
1
by: GGerard | last post by:
Hello Is there a way to use a variable in the Conditional Formatting of a Textbox? Example : I want the background of a textbox in a continuous form to change color when the value of...
8
by: Typehigh | last post by:
I have many text fields with conditional formatting applied, specifically when the condition is "Field Has Focus". Without any events associated with the fields the conditional formatting works...
2
by: Filips Benoit | last post by:
Dear All, Access 2003 adp on SQL_server 2005 A continious form showing 1 month based on table 'CALENDAR_MONTH_GRID' and fill with a SP. Fields: Companyname, Day1, day2, etc. The value in the...
2
by: robertng90025 | last post by:
I'm having a problem with MS Access 2003 and its conditional formatting. I have textboxes on a continuous form whose left and right margins are set to 0.03 inches. Based on each textbox's...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.