[AccessD] : Ad hoc filter on a Form

[Rocky Smolin]

I have always collected the criteria for filtering from controls on the
form and then built a string Based on those filters and set it in a
me.filter = statement. I have actually used this technique to Allow the
user to build very elaborate filters. I am out of town for a couple of days
but if you don't get a good answer and need more explanation or a code snip
let me know.

R

On Wed, Nov 25, 2020 at 6:45 AM Borge Hansen <pcs.accessd at gmail.com> wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>