How do I Stop Users Saving Over a Form Design

[garfieldsevilla]

** Mod ** This post was split away from Access 2003/ VBA form filter problem as it's a new question.

NeoPa, I've come up against the same problem again, though this time I think a user was using CTL S each time they viewed a filtered form to "save the result" and thus created a problem it took some time to find. (the form appeared blank on several occasions when there was data to show because of the filter)

I would really like to find a way to clear a form filter in VBA. Toggling "Me.FilterOn" does not change the filter saved with the form, the only way I know of to do this is clearing manually the filter and saving the form again. This clearly requires me to go to site..

Does anyone know of a way to remove the filter in VBA?

Is there a better way to call a form with a subset of data?

[NeoPa]

The first, and most obvious, answer is to release an MDE or ACCDE version for your users to use. Providing them with the ability to save changes to your database design is not good thinking.

Alternatively though, you could try putting some code in the Form_Close() event procedure that clears the .Filter property and saves the form. That way it would always be virgin next time it's used.

[ADezii]

DoCmd.RunCommand acCmdRemoveFilterSort

[NeoPa]

That's certainly one way to remove the filter before saving the form ADezii, but it may be easier simply to clear the properties :

With Me
    .Filter = Null
    .FilterOn = No
End With

[ADezii]

1. DoCmd.RunCommand acCmdRemoveFilterSort will Reset the FilterOn Property to False.
2. Won't Me.Filter = Null generate an Error, since it must be a String Value?

[NeoPa]

Quite right ADezii. I should have suggested :

With Me
    .Filter = ""
    .FilterOn = False
End With

As for the use of RunCommand, it may do part of the job, quite adequately, but personally I prefer to reset the .Filter property as well as the .FilterOn one. There's less likelihood of anyone getting confused by seeing a filter string there which isn't filtering.