Trouble getting Textbox as Checkbox to function

[JMongi]

See this thread by @CJ_London for reference:

use a textbox as a resizeable and colourful checkbox

For whatever reason, checkbox controls cannot be resized. This suggestion addresses that issue and also enables the use of colour. To create, put the following in the format property of a textbox and set the control source to a boolean value 1. set format property -...

www.access-programmers.co.uk

The formatting doesn't appear to be working as intended.
Clicking on the form control still tries to enter text in the field even when locked.

 

[JMongi]

I have this in the Format portion of the text box property:
[Blue]\R;[Green]\R;[Red]\S;©

I have Font Name: Wingdings 2
I have Border Style: Transparent
I have Enabled: Yes
I have Locked: Yes
I have Tab Stop: No
I have Default: True
I have Source: Running (a Yes/No field)

I have the following in the Click event of the textbox and I do have a textbox called txtFocus on my form:

Private Sub txtRunning_Click()

txtFocus.SetFocus
If Not txtRunning.Locked Then
    txtRunning = Not Nz(txtRunning, False)
End If

End Sub

The look of the checkmark is not a checkmark. So I presume some mapping of characters to symbols is off.
Also, double clicking on the control puts the cursor in there still.

 

[CJ_London]

hmm, surprised no one has mentioned it before -try the wingdings font, you are looking for chr 253 and 254

with regards

Also, double clicking on the control puts the cursor in there still.

think you will need to put the same code into the double click event

 

[JMongi]

So, I might be missing something obvous here.
Am I supposed to set the text value via VBA on a click? Is that supposed to happen automatically?
I'm not sure what your format string is doing so it makes it a little tougher for me to take educated guesses to try and troubleshoot.

 

[Pat Hartman]

I can't read the format property without the documentation in front of me but it breaks down to positive/negative/null and provides a formatting option for each of those three situations.

0 = positive
-1 = negative
null is null

 

[CJ_London]

It’s not a text value but a Boolean value- and the code changes it from true to false or false to true

it’s positive;negative;zero;null for number type data types (booleans and dates are also number types)

 

[JMongi]

To muddy the waters more...here is the MS post on this idea:

Format a Yes/No data type - Microsoft Support

support.microsoft.com

 

[Pat Hartman]

Thanks CJ, I forgot the fourth (third) option of 0.

Actually @JMongi the description is saying the first option has no meaning (because the value can only be -1 or 0) so it would never apply;the second option (-1) is for true, the third option (0) is for false, and the fourth (null) does not apply either so they don't mention it.

But like many "help" entries, they leave much to the imagination and don't include necessary details.

 

[JMongi]

So, I broke some things down to troubleshoot. We can worry about wingdings later. I focused on having the textbox display a standard text based on the MS help file above and @Pat Hartman first comment and setting the default value to True/False/Null. I arrived at the following conclusion.

For formatting it goes ;"Text for True"[Color];"Text for False"[Color];"Text for Null"[Color]
This gets converted by Access to ;\Text for True[Color];\Text for False[Color];\Text for Null[Color]

 

[Uncle Gizmo]

I adapted some code I made for creating an option group out of command buttons to work as a checkbox. (A command Button as a checkbox)

​

Nifty Check Box - Over View 1​

 

[CJ_London]

I've corrected my original thread (not sure what went wrong with the format string, it is almost as if the characters have changed since I can't find the character for null anymore) and attached an example. Also attached here,

see this link for the fonts as per the original post, showing the original values for P etc

Wingdings & Webdings Font Icon Character Map (Printable Cheat Sheet)

Excel MVP Chris Newman releases his Webdings & Wingdings font cheat sheet translator to easily find the icon you are looking for within these symbol fonts.

www.thespreadsheetguru.com

 

[strive4peace]

@JMongi, I also made a video to use a unicode character in a textbox for YesNo here that might give you some ideas:

Big Checkbox in Access with Conditional Formatting

 

[The_Doc_Man]

Using Win10 & set Character Map utility for "WingDings 2", the checkmark is 0x50, checkmark-in-a-box is 0x52. X-mark is 0x4F, x-in-a-box is 0x51. These would be upper-case O(=0x4F), P, Q, R(=0x52) as codes go.

Win10, set Character Map to "WingDings" (no 2), checkmark is 0xFC, check-in-a-box is 0xFE, X-mark is 0xFB, x-in-a-box is 0xFD. These characters have no corresponding characters in standard ASCII, which stops at 0x7F, but CHR$(0xFC) would work correctly.

Therefore, the check-mark and x-mark appear in both fonts but in different places. Those characters do not appear (that I can see) in Webdings or in WindDings 3.

 

[strive4peace]

its not necessary to change the font if you use Unicode characters

 

[Gasman]

Crystal uses CHRW() ?

 

[strive4peace]

indeed!

decimal Unicode character for ChrW

9744 ☐ BALLOT BOX Checkbox open
9745 ☑ BALLOT BOX WITH CHECK Checkbox checked
9746 ☒ BALLOT BOX WITH X Checkbox X

 

[CJ_London]

The issue is the char allocation appears to have changed, at least for the OP and myself - I’ve started another thread on the subject. Two responses indicate it does not affect everyone

 

[Gasman]

Well I am on 2007, so pretty ancient?

 

[strive4peace]

actually, you don't even have to do all that ... if the Data Type is Integer instead of YesNo, you can set Format to something like this (the Format property supports Unicode):

☑[Magenta];☑[Red];☐[Blue];--

btw, it seems that Yes/No is actually stored as an integer -- but capabilities have been crippled. Read this from Allen Browne:

Why I stopped using Yes/No fields, by Allen Browne

Microsoft Access tips: Code accompanying article - Has the record been printed?

A workaround for a bug where Microsoft Access crashes or fails to execute queries with Yes/No fields.

allenbrowne.com

 

[Pat Hartman]

I also made the choice to eschew Yes/No fields early on. In my case it was prompted because of the differences between Jet and SQL Server.

In any event. Most of the time, I set the default for the "yes/no" integers to Null because I like No to have meaning. Once the user has made a choice, either yes or no, there is no way for the user to take the value back to null by using the checkbox. And that is usually OK. If I want the user to be able to choose Null, then I either use a combo or an option group when I have room.

To make this work to force the user to check either yes or no, is a little annoying for the user because he has to check twice. Once for yes and a second time for no. If your users can't get past this, then use a combo.

This picture shows the null state for an integer bound to a checkbox. I was using the buttons to see what would happen if I set the Yes/No field to null using code. No error but it stayed blank.