vbams-accessms-access-2016

Using spacebar to check a Check Box, perform a DCount(), with focus remaining on the check box?


I have a form with a datasheet subform, where one of the controls is a Check Box (Chk1).

I want it to behave such that: If the check box is checked using mouse click, a DCount(...) on the same Check Box field is performed in the VBA straight away. I have this part working, by navigating away from the record in the Check Box AfterUpdate Sub:

Public fromSpace As Boolean

Private Sub Chk1_AfterUpdate()
    If Not fromSpace Then 'mouse click, so it's necessary to fire Form_AfterUpdate by navigating away
        Form_Main.cboSelector.SetFocus
    Else 'Spacebar used with SendKeys so navigating away is not necessary
        fromSpace = False 'reset for next time
    End If
End Sub

Then the AfterUpdate of the datasheet subform has the DCount(...)

Private Sub Form_AfterUpdate()
    If DCount(...) = x Then 'This DCount looks at the yes/no (Check Box) field in question
        'Do some stuff
    End If
End Sub

However, I also want it to behave such that: If the check box is checked using the spacebar key, I would like the same DCount(...) to happen, BUT while keeping focus on the Check Box in the subform (this is so the user can continue to navigate using keyboard).

I actually have it working, but this is using SendKeys ("{F9}")

Private Sub chk1_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeySpace Then
        fromSpace = True
        SendKeys ("{F9}") 'This WORKS (DCount(...) is performed, and focus remains on the Check Box). However, I understand using SendKeys is bad practice, so looking for a different solution

        'DoCmd.RunCommand (acCmdSaveRecord) (I have also tried this, which doesn't work)
        'DoCmd.RunCommand (acCmdSave) (This doesn't work either)
        
        'also tried doing the DCount(...) here, which doesn't fire until navigating away from the record
        'EDIT: Sorry, I think it does fire, but it doesn't use the new checkbox value in the DCount
    End If
End Sub

I also found this: https://www.reddit.com/r/vba/comments/tysrn7/click_a_button_on_the_ribbon/ (which programmatically does a Ribbon button click), which I tested, and it works, BUT is very slow. There is a 0.5 - 1 second delay every time spacebar is used to check or un-check the Check Box.

Surely there's a way to do this?

Thanks for reading.

EDIT: For anyone who is interested, I have elaborated on the code a bit, to show how the SendKeys method is working properly.


Solution

  • You don't need the KeyUp event at all. Checking/unchecking the checkbox with the Space key triggers the checkbox AfterUpdate just the same as clicking it with the mouse.

    The following works for me, the calculated number is always updated immediately.

    All code is in the datasheet subform, the main form has no code.

    Option Compare Database
    Option Explicit
    
    Private Sub chkYesOrNo_AfterUpdate()
    
        ' Save the record, triggering Form_AfterUpdate() - it doesn't matter if this is done with a mouse click or the space key
        Me.Dirty = False
    
    End Sub
    
    Private Sub Form_AfterUpdate()
    
        Dim n As Long
        
        ' Calculate n
        n = DCount("*", "tblSubform", "[YesOrNo] = -1")
        Debug.Print n
        
        ' Show number in an unbound text box on the main form
        Me.Parent!txtCount.Value = n
        
    End Sub
    
    Private Sub Form_Load()
        ' Show the number on main form load
        Call Form_AfterUpdate
    End Sub
    

    In all cases, the focus stays on the checkbox.