Expert: Scottgem - 3/30/2009

Question
QUESTION: I want to create a Pivot table where users can filter records from the drop down but I don not want them to be able to move fields from Row area to Column area or Filter area and vice versa.

ANSWER: You can have them apply criteria to a query that will be used as the source of the Pivot. then generate the pivot so the user doesn't see the steps taken.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks for the quick response,

I think I didnt explain my question well. Let me try again. I have designed a form using Pivot Table wizard and put fields in Row area, Column area, and filter area. Now user can use the drop downs available in the fields and filter the data (for ex: Country field, users can select any country from drop down and see the relevant data in Data area) but they are also able to move the fields in column area to Row area and fields in Filter area to Data area. I want to retain the filter but restrict users from moving the fields from one area to another.

I hope my question is clear this time

Thanks in advance

Answer
I couldn't find any way of doing this. What I can suggest is that you use the On Close event or your own close button to make sure that changes aren't saved to the form. So even if a user plays around, the next time the form is loaded, its loaded as you want it to be.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA