473,897 Members | 3,030 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Jun 10 '07 #1
10 8692
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(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.
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(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
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.Form atConditions(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(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.

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.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.
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(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.
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...@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 -

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5949
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 terminated by pressing ++ and then terminate the process. I searched the entire internet and found out that there could be two things wrong (both of them are mentioned in the bug list on the access
4
3151
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 background colour. In A2000 it works great, but in A2003 the background doesn't always change and when it does it only changes when the record looses the focus. Any way around this? Is it a bug? Or have they "improved" it?
2
5841
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 main form has personal info on it. The subform deals with each case's Issues, Decisions, and Notices. Issues, Decisions, and Notices are all combo boxes. Not all Issues have Notices. Issues 25-50 have Notices. Issues 1-24 and 51-100 don't have...
8
12066
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 expressions are the same for all controls Under some undetermined circumstances, when such subform is displayed, the controls on the subform start to visibly flicker, the cursor in the subform stops blinking, and CPU utilization goes to 100%....
2
2976
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 showing or only part of the data in the field displays (i.e. it will display the last name but not the first name or it will display neither). When the conditional formatting is removed it displays all the data as it should be display so the data...
2
1190
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 when the date has passed. Background: I have 2 related fields that are only rarely active, so I manage them in the query for the subform: ShowBlanket: IIf(,"Blanket","") and
1
5035
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 "MyField1" is True. No problem, I just need to write "Expression is" and " = -1 "
8
8524
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 perfectly. However, I have code that runs under the ON CLICK event. The code changes the focus to other controls, which means the conditional formatting is no longer displayed. This part makes sense to me. Here's what doesn't make sense. The last...
2
3124
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 day-fields is like WK (weekend), NH (National holiday), etc. Depending on the fieldvalue the field is colored using conditional
2
3333
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 current value, I set its background color. No problem with this part. My problem is that when clicking different text boxes representing the records on the continuous form, the left and the right margin areas do not always take on the background...
0
9990
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10941
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10478
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
8032
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5873
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6074
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4698
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4293
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3300
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.