Replacing a Filter Query in a Module

[PRD]

I created a Form which I want to open with one of four different filters (depending on how I want the data to be sorted). I also created a generic filter (called ‘FilterForm‘) which I will always use to open the Form.


I want to create a Module to replace FilterForm with one of the four sort filters (FilterSortA, FilterSortB, etc.) depending on a parameter I pass to the Module.


My problem is I cannot seem to find a Module statement which will allow me to copy/overlay FilterForm with FilterSortA. In Macro language there is a DeleteObj and CopyObj command but I have to hardcode the FilterSort names which I want to avoid.


So my question is, is there a statement(s) which I can code in a Module which will allow me to replace FilterForm with FilterSortA, FilterSortB, etc.? Thank you for your time.

 

[CBrighton]

Presumably you are using some sort of interface that I am unaware of (or which was added to a later version of Access than 2003) to create and save filters?

Personally I'd do it by creating an SQL string in VBA and passing that as the filter variant in the DoCmd.OpenForm command. The VBA can do a simple check of a form control (look at a combobox and see which pre-defined filter to apply) or a more complex check (create form controls for each field you want the user to be able to filter on and let them create their own filter) and dynamically create a filter to apply to the form.

 

[PRD]

CBrighton -

Thanks for the tip, I will give it a try. I am not doing anything fancy, all of the filters (queries) have already been created, its just a question of which one I want to open the Form with depending on a passed parameter. I can use the 'Macro language' method I explained before but then I would have to create a separate Form for each filter (because of the hardcoding) which gets kind of ugly. The DoCmd.OpenForm command looks promising, I'll let you know if it works. Thanks again for your help.

 

[CBrighton]

If you intend to use the same form controls regardless of which query is loaded then the fields in each query must be the same.

In which case I would have a basic query which just gets the fields from the tables which I want without criteria (unless there is criteria which is used in all the current queries) and set the rest via the filter. That way only 1 query is needed and the VBA does the rest.

 

[PRD]

CBrighton -

Sorry for taking so long to get back to you but I just wanted to tell you that DoCmd.OpenForm statement working perfectly!

I pass a 'SelectBy' and a 'SortBy' parameter to a Module and then depending on the combination of these variables I open the Form with the appropriate filter. This saves me from having to create a separate (duplicate) Form for each filter. Thanks again for the tip!