[AccessD] : Ad hoc filter on a Form

[Stuart McLachlan]

My usual solution is to give the users a compiled accde and keep the source accdb in a 
"development" area that they can't see.



On 26 Nov 2020 at 0:44, Borge Hansen 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-on
> ly-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
>