[AccessD] : Ad hoc filter on a Form

[Borge Hansen]

Hello everyone,
Using Access 365 / Access 2019 but on an app that was born in Access 97
How do you handle ad hoc filtering on a continuous Form or single form
where you want the end user not to be able to place the Form in design
view?
The control source is a query to a linked table; separate backend (SQL
Server database).
Recordset: Dynaset or Snapshot

Our Access app is configured such that User cannot access the design
area; the built in ribbons, the navigation pane or the  "File"
command.

If we on the Form set the Shortcut Menu property to Yes we have good
filtering options on all of the text controls: text filter, number
filter and date filter depending on the data type of the text
control's underlying column.
The immediate default filtering options based on the value of the text
control with focus:
for text: equals, does not equal, contains, does not contain
for number: equals, does not equal, less than, greater than
for date: equals, does not equal, on or before, on or after

For text, number and date filters where you enter your own filter
criteria you have additional operators for example for text:
begins with
does not begin with
ends with
does not end with

for number:
between

for date:
between
and many more like next, this last ...[ week | month | quarter | year]

This is all well and good, but the moment you right click outside of a
text control you get the option to put the form in Design view: BAD!

How do you prevent that?

1) roll your own short cut menus for the Form  and text controls by
way of VBA functions that you call on the "Shortcut Menu Bar" property
for the Form, text controls, etc - in which case you would have to (I
think) create a separate hand rolled short cut menu for text, number
and data text controls... plus a separate short cut menu for the Form
which disallows the default options including the switching to Form
design view.... messy ...
The vba code requires knowledge of the internal ID for a particular
shortcut item, see for an example
https://stackoverflow.com/questions/42583230/enabling-shortcut-menu-only-on-a-specific-form-control-textbox
And using this approach we haven't been able to find the internal ID
for all the filter operators...


2) Or you can set the "Shortcut Menu" property for the Form to false
to prevent User for switching to Design View; and bring up the filter
operators by running the command
DoCmd.RunCommand acCmdFilterMenu
on for example the double click event of a text control.
You get filter operators as per the underlying data type of the text
control, and you prevent the End User for accessing the Form in Design
view....


We started off with rolling our own shortcut menus, but now we are
leaning towards 2).
We have a separate commandbutton on the form that fires the
DoCmd.RunCommand acCmdFilterMenu
after having first set focus back on the text control that last had focus.
The command then brings up the data type specific FilterMenu on the
text control.
(On the lost focus event of the text control we set the name of the
text control on the Form's tag property, so when we fire the adhoc
filter command button it knows what text control to put focus back on
before executing the acCmdFilterMenu)
Next to the command button we have an unbound text control where we
display the applied filter - read from the Form's Filter property. The
Form opens with no adhoc filter set (Filter On Load form property set
to false). Using the FilterMenu on the text control you can un-filter
(if a filter has been applied on the text control). In case we filter
to the extent that no records are shown on the form we need to have a
"clear filter" command button that will clear the Form's filter
property.

We are interested to know if there are ways of setting up ad hoc
filtering using what could be called the default built in shortcut
menu option for a Form's text controls but somehow disallowing End
User switching the Form to Design View.

Regards,
/borge