How to make  a null-valued checkbox appear white instead of gray

Milewskp
Milewskp used Ask the Experts™
on
When the control source of a checkbox is a field on the arrow end of an outer join between two tables, it will appear gray when its value is null, even when the checkbox's Triple State property = No.

How can I make the checkbox appear white when the value is Null?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
That is the point of it being gray ... which means that it has never been check or unchecked, either manually or via vba code.

Why is this a problem ?

mx
You can set the default value on 0, to set the checkbox standard on FALSE, then it will appear as 'white'.

Other then that its Access standard to show it like that.

I would prefer not to use a Checkbox, instead a 'fake' Checkbox with same functionality, see below article why:
http://allenbrowne.com/NoYesNo.html

HTH,
Daniel
Most Valuable Expert 2014

Commented:
Another plan of attack is to tweak your query.
Have it return an additional calculated column WhateverDisplays:Nz([Whatever],0)
Put WhateverDisplays directly on top of the other checkbox
Make your bound control invisible when it is null, and add an GotFocus event to the WhateverDisplays control to change the field from Null to True.

But I am with mx
Grey null boxes serve a purpose, they tell you that you have never tried to set the value.
White is false
Checked is true.

Unless you have some heavy reason to override that default logic, I wouldn't.
It DOES tell you something; why hide it?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Is this the same "Calculated" Yes/No Field as you have here?:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27041921.html


<How can I make the checkbox appear white when the value is Null?>
Why?
Checkboxes are normally associated with Boolean fields.

"White"=Un-Checked=No=0=False

I don't know of any interface where an empty ("White") boolean checkbox means null...

So I, like the previous experts am a bit confused by this request...

As always, please include the "Why" behind your request, in case there is an alternate approach...


JeffCoachman

Author

Commented:
Hi Folks,
The checkbox is used to indicate that the item described by that record has been selected. There are therefore only two states from teh user's point of view: selected or not, and they were confused (and complaining) about the Null. The Null is just an artifact due to the two table structure I'm using as the basis of the recordsource.

Hi danish,
<You can set the default value on 0>
This won't help in my case, since the null is due to a non-existant record in the table on the arrow side of the outer join.
Your link looks interesting; I'll have a closer look.

Hi Nick,
<Make your bound control invisible when it is null, and add an GotFocus event to the WhateverDisplays control to change the field from Null to True.>
Interseting idea, I'll paly with that and let you know how it goes.

Hi Jeff,
<Is this the same "Calculated" Yes/No Field as you have here?>
Yes.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<There are therefore only two states from teh user's point of view: selected or not, and they were confused (and complaining) about the Null. The Null is just an artifact due to the two table structure I'm using as the basis of the recordsource.>
Then there are really three states, True, False, and the "Confusing" Null...

If this is the case (and this is just my opinion here...because I don't know thw nature of the query) I would stay away from using a Boolean Display control (CheckBox, Option Button) to display this data.

(I was never crazy about the Tri-State Property because it seemed to muddy the waters as far as how a boolean field could be displayed...)

If it were me, I would use a textbox to display this data.
Have some simple If-Then-Else logic:
If YourQueryCalc=True Then
    YourTextBox="Selected"
ElseIF YourQueryCalc=False Then
    YourTextBox="Not Selected"
ElseIf Isnull(YourQueryCalc) then
    YourTextBox=""
End if

Or, as was previously mentioned, use a "Fake" control or conditional formatting to set some visible indicator of the Field's contents.


JeffCoachman
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"How can I make the checkbox appear white when the value is Null?"
The answer to this question is this:
IF ... you are going to use the Check box control, then you can't.  If you want it to be white, then either set the Default Value at the Table level to False, or set the check box value to False when the Form opens during Load.

These are your options ... IF ... you use the check box control.
"There are therefore only two states from teh user's point of view: selected or not,"

If that is the case, then I don't see the confusion.  Set the value to False when the Form loads ... to get the White check box.

mx
Most Valuable Expert 2014
Commented:
Alright,

I posted the solution to your previous question
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27041921.html.

The same technique that eliminates your update delay also works to hide the null greyness.
File attached:
Null-Boolean.mdb

A lot of legerdemain, but look at frmFinal.
If you REALLY are looking to update a checkbox bound to a calculated field, this is how it is done
Layer a transparent textbox over top of your calculated control
Handle the textbox's click event
Have the actual bound field on the form--the click event will change it
Display the calculated control
Have a slim sliver of a textbox to receive the focus after the click event.

By the way, thank mx for the slim sliver to hold the focus.
That was his idea from a different Q
It works nicely to keep the checkbox from being covered by the textbox having the focus

Author

Commented:
I'll accept Nick's solution, since it answers the question (thanks Nick), but the implementation I've decided on is to throw out checkbox controls altogether when dealing with tristate situations. (Partial points for danish's allen browne link - excellent discussion there.)

I will go one step beyond browne and use text fields and text controls using the values "Y", "N" and Null (rather than -1, 0 , Null). Sure it's not so elegant when interrogating the value of the control (must use 'If MyControl="Y" then...' rather than 'If MyControl then   ' ), but it's easier for the users to understand, there are no gray-white issues, no weird delays
(see http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27041921.html.),
and no unnecessary combo box drop down lists and arrows (ala browne).

I've spend most of the day trying to come up with a more elegant solution, but there seems to be a down side to each one I've tried, so Y, N, Null is the solution for me.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial