Prevent Filtering and Sorting with Subform Controls?

[jbrumbau]

Hello,

I have some specification sheets with subforms I have created. I'm having a problem where if you right click and filter/sort any field in the subform (which is tied to the main form in a 1:1 relationship), then the subform only displays the first record. This ends up permanently altering the spec sheet. I notice that when this happens, text typically shows up in the "Order By" or "Filter" fields of the subform. My only resort is to delete the broken spec sheet and subform and then reimport them from a backup version of the database.

Is there some way to prevent the user from filtering/sorting fields in the subform? I tried setting the "Allow Filters" property of the subform to false but this does not fix it since users can still right click and sort. The only thing I can think of is preventing users from right clicking on controls in general, but then they can no longer right click on any of the other forms (where filtering and sorting is important) which is not preferred. Any suggestions?

For some background, here is the layout:

Form <Form Main Body>
RecordSource = qryI^^INSTRUMENT_BASE
(User navigates through records on the main form)

Form <Linked Subform>
RecordSource = qryI^^INSTRUMENT_HEADER
Link Master = INSTRUMENTID
Link Child = Instr2Body
(Not allowed to navigate through records in the subform due to 1:1 relationship)

Query <qryI^^INSTRUMENT_BASE>
[INSTR-INDEX_HEADER] Type 1 joined with [INSTR-INDEX_BASE], [Instr2Body]-[INSTRUMENTID]

Query <qryI^^INSTRUMENT_HEADER>
[INSTR-INDEX_BASE]

Table [INSTR-INDEX_HEADER]
[Instr2Body] - Primary Key, integer

Table [INSTR-INDEX_BASE]
[INSTRUMENTID] - Primary Key, integer

Macros run when the instrument (Instr2Body) is selected in the header, which simultaneously fills in a hidden control box for INSTRUMENTID then refreshes the form to create the specification sheet. All controls in the body section are disabled if the spec sheet is new to prevent creating the spec sheet in the wrong location.

Thanks in advance for the help.

[jbrumbau]

I know my question was rather long, but all I'm wanting to know is how to prevent the user from right clicking on a form and sorting or filtering the data in it.

Does anybody know how to do this or can it only be done with a global right click disable?

[jbrumbau]

Figured it out (about 10 months later :S ) Apparently if you have Access 2007, customizing right click menus (Shortcut Menu) either involves code, or you have to open it in Access 2003 and customize the menu from there. You can then pick the shortcut menu for Current Database or for Form Properties.

In Access 2003, you go to View, Toolbars, then Customize. Select the Toolbars tab, then create a new menu. Click properties, then from the Type dropdown list, pick "Popup". After that, go back to the Toolbars tab, then check "Shortcut Menus" to get the entire list of all menus. Custom menus appear on the very right hand side. You have to drag commands from the predefined trees over to your custom menu's tree.

[NeoPa]

Have you tried setting the Allow Filters property to No? It would be a sensible first step.

You state that such filtering selections change the original design of the form such that when it's re-opened the filter is already set, but this can only happen if you save the form when you close it. If you choose not to then the original, of course, is kept. If you get no such prompt then this indicates you have Warnings turned off permanently, which is never a good idea. Run the following code to fix this :

Call DoCmd.SetWarnings(True)

[jbrumbau]

Yes, I saw the Allow Filters and set that to "No", but I was still able to right click and say "Sort ascending/descending". Basically if these subforms had any field filtered or sorted, the entire record would become unlinked with the parent and would be broken. The form was kind of a Frankenstein and it basically has records created from the subform by selecting a tag within it.

Also, warnings were turned on.

[NeoPa]

JBrumbau:
Also, warnings were turned on.

In that case saving of the form would have been prompted. If you. or anyone else you allow to have write access to your database, closes the form and accepts the prompt to save then the form will be saved in what would be considered its temporary state. If this had not been done then no changes would have been saved and the form would revert each time to the originally intended design.

The Allow Filters property would not disallow sorting the data. Your other found solutions for changing the interface would be required for that. My advice would always be to do it in such a way that you make sure any such changes effect only your own specific database though. Users won't thank you for interfering with their Access application generally.