MS Access 2007 Open Pivot Table without previous criteria

DoveTails used Ask the Experts™
I created a pivot table and saved it as a form.
The form is opened with a command button with one line of code:

DoCmd.OpenForm "frmTheForm", acFormPivotTable, , , acFormReadOnly, acWindowNormal

Works well for the users ... with one problem.

Each supervisor opens the pivot table and specifies the criteria they are interested in by using the drop down menus of the pivot table.  This usually involves looking at employees in their department or filtering to see only a certain job title.  When the pivot table is closed ... the next person who opens the pivot table sees the previous person's criteria and not all of the data.

I would like the pivot table to open without any specific criteria and simply display off of the availble data.

Any help on how to open the pivot table so all of the data is diplayed would be appreciated.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can disallow saving changes when pivot is closing:
docmd.close acForm,"MyPivotForm",acSaveNo
An addition:
you should disable any form closing buttons and add event to On Key Press for the form:
docmd.close acForm,"frmTheForm",acSaveNo

Form will be closed without save on any pressed key
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

(AFAICT) This is the nature of a Pivot Table in MS Access

There may be a way around this that I am not aware of though...
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Thanks for the comments ... I have not had any luck with the pivot table yet.
Me.undo prevents user from making any preferences while using the table and the code with acSaveNo does not error but it does not prevent the user's preferences from being saved (or stored) in Access.  I'm beginning to think boaq2000 is correct in that this is the nature of MS Access.
Have you do it with On Key Press event?
MIS Liason
Most Valuable Expert 2012
One way to work around this would be to create a "Reference" copy of the PT/Form with no filters set.
Then each time you want to open the PT/form, you make a temporary copy of this "Reference" PT/Form, and use that. (Then obviously destroy this temp copy before opening it again).

Obviously, they key here will be that you can *only* open the form with the Button code
In other words you should not open the temp Form on it's own, because it will still be filtered.
Make sense?

Like this roughly on the click event of the button that opens the Pivot table Form:

Private Sub btnOpenPTForm_Click()
    'Delete the temp Form
    DoCmd.DeleteObject acForm, "frmPivotTable"
    'Make a Temp copy of the reference form    
    DoCmd.CopyObject , "frmPivotTable", acForm, "frmPivotTableSaf"
    'Open the Temp copy    
    DoCmd.OpenForm "frmPivotTable", acFormPivotTable, , , acFormReadOnly, acWindowNormal
End Sub

Sample attached as well.



Thanks for the help with the pivot tables.

The idea for a copy as a 'backup' is most likely what I'll do but I have to test that with multiple users hitting the table at the same time.  

note: When using the on press event with 'undo': this prevented the user from making any preference changes...I need them to be able to that while they are viewing...just don't want those to be saved.

So thanks again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial