ms-access

DoCmd.RunCommand commando buttons in MS Access splitform imitating filter buttons in ribbon

I have a splitform in MS Access, where I inserted multiple Command Buttons.

From left to right named:

enter image description here

I want the buttons to perform the exact same operations as the buttons in the ribbon.

I got (A) and (C) to work with the following VB:

Private Sub cmdClearAllFilter_Click()
    DoCmd.RunCommand acCmdRemoveFilterSort
End Sub

Private Sub cmdFilterByForm_Click()
    DoCmd.RunCommand acCmdFilterByForm
End Sub

However I still have trouble with (B) and (D), I want those to behave as click on the buttons in the ribbon marked below (preferable with DoCmd.RunCommand otherwise with custom VB):

enter image description here

For (B) I tried:

Private Sub cmdFilterBySelection_Click()
    DoCmd.RunCommand acCmdFilterBySelection
End Sub

I'm not able to describe the behaviour as I don't fully understand what it does. I can only conclude it does not behave as the ribbon button (drop-down giving 4 filter options on the marked cell/field/property/value).

For (D) I tried:

Private Sub cmdToggleFilter_Click()
    DoCmd.RunCommand acCmdToggleFilter
End Sub

There are two problems with this:

  1. The button is not clickable before the Actual Toggle Filter button from the ribbon has been clicked once.
  2. If the ribbon button is unavailable (greyed out), a click on the command button will cause an error, which I assume can be solved by: On Error Resume Next.

Any help or guidance is appreciated.

Solution

  • With FilterBySelection, clicking button takes focus away from data control and Access doesn't know which parameter to use. Need to return focus to whatever control last had focus before button click. Try:

    Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFilterBySelection

    If you want to trigger the dropdown to select from:

    Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFilterMenu

    For toggling filter, I used: Me.FilterOn = Not Me.FilterOn

    However, I don't see a way to utilize FilterByForm without clicking Toggle on ribbon or right click menu > Apply Filter/Sort.