Update Delay when user clicks Checkbox

Milewskp
Milewskp used Ask the Experts™
on
I have a form with two checkbox controls: YN and YN1

The control source for YN is table1.YN (a non-required boolean field), and YN1 is a calculated field: =Nz([YN],False) .

The mousedown event for YN1 is:
   Private Sub YN1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
       Me.YN = Not Me.YN
   End Sub

The result of all of this is that YN and YN1 always have the same value, except that when YN is null, YN1 shows up as False.

Here's the question:
When the user mouses down on YN1,
 - If YN is True, then YN1 goes false immediately when the user releases the mouse.
 - If YN is False, then YN1 goes True about a second after the user releases the mouse. This delay is really irritating because it leaves you wondering if you actually clicked it properly.

Why is there a difference, and how can I make it so that the value of YN1 always changes immediately when the user releases the mouse?


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use the OnClick event of your YN field .. it will change immediately the value of YN1.

You might avoiding using a Y/N field, see below article on why, and the use of a 'fake' Y/N instead:
http://allenbrowne.com/NoYesNo.html

HTH,
Daniel

Author

Commented:
Hi danish,
Onlick doesn't work with calculated value controls.
What I mean is on the OnClick event of the Bound Field ... not the Calculated Field.

You might use the Current event of your Form, or Dirty when data changes.

HTH,
Daniel
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
The default Display control for a Yes/No field in Access is the option button.
It's the default Display control for a reason:
It is typically the best choice for Boolean fields because there are only two states: True or False (Yes/No, -1,0)

All of the issues you are having do not exist with option buttons because the default behavior is to toggle the state from Yes (True, -1) to No (False, 0)

Using comboboxes for this makes this more difficult, as you are experiencing.
1. Comboboxes have a Heavier relative "Performance weight" than a option button.
2. Comboboxes are slower for Data entry because a user must select the dropdown control itself, then select a value.
However, ComboBoxes are useful over Option Buttons if:
You have more than two selections.
Your values are text, (Not Boolean)
...etc

Trying to "Force" the functionality designed for a particular control onto a different control always leads to these types of questions where the workaround is convoluted and never quite perfect.

This is why most Access developers typically don't "Fight" Access' built in functionality.


JeffCoachman

 
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Ignore my above post, it was intended for another question (one that involved comboboxes)

Author

Commented:
Hi danish,
<What I mean is on the OnClick event of the Bound Field ... not the Calculated Field.>
The bound control is purposely hidden.
Most Valuable Expert 2014

Commented:
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 textboxes 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.
Null-Boolean.mdb

Author

Commented:
Hi Nick,
I appreciaet the effort, but I think your solution may be a case of exchanging one problem for another; to wit, while the mouse is held down, the normally invisible textbox becomes visible, obscuring the checkbox (which will likely prompt a wtf from users). Also, if you click the textbox at it's top edge, you can get it to stay visible even after you release the mouse and move the cursor away (double wtf).

Author

Commented:
Inspired by the allen browne link provided by danish, I think I'm going to scrap the checkboxes and replace them with a text field whose click event changes the value from Null-Y-N-Y-N-...

Full points to danish. Long live allen browne!
Most Valuable Expert 2014

Commented:
Throw in an GotFocus event with the identical code to the click event, and set TabStop to No and it works perfectly.
Even with the mouse held down.
But its your call
Null-Boolean.mdb

Author

Commented:
Hi Nick,

This works too:

replace the textbox with a transparent command button with this click event:
Private Sub cmdButton_Click()
   If Nz(Me.YesNoValue, 0) = 0 Then
       Me.YesNoValue = True
   Else
       Me.YesNoValue = False
   End If

End Sub

I prefer this because you only need the one event rpocedure, but more importantly, the cursor remains as an arrow instead changing to the textbox bar cursor.

Author

Commented:
Of course, these solutions only work for form-view forms. If you have a datasheet view form (which I do) you're out of luck.
Most Valuable Expert 2014

Commented:
That's workable too!
I never really considered making a command but transparent.
I'll have to remember that; it's quite elegant

Sorry about the datasheet view thing.
The original question didn't mention that wrinkle.

Mind you, that's why I never use the datasheet!

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