More Pivot syntax (2003 SP3)

Topic

New Reply

Is there syntax that would allow me to programmatically reset the included members of a field to “all of them”?

I want my pivot form to show all the members of a field on open. There’s lots of references showing how to include and exclude members, but I cannot find anywhere how to reset the form to include all of them. What I want to achieve is to wipe the pivot table’s memory clean from the settings that were left when it was last used. I’m thinking something like this (even though I know this doesn’t work).

frm1.PivotTable.ActiveView.RowAxis.FieldSets(“NewOrRet”).Fields(“NewOrRet”).IncludedMembers = all

Reply | Quote

Replies

I’ve never tried it but what about assigning an Array() function with the name of all the members, e.g.
….”).IncludedMembers = Array(“member1”, “member2”, etc…)

Reply | Quote

I thought about that. That is fine for some of the fields. The problem is that one of them is a meeting date field with hundreds of dates. I find the whole pivot table object very difficult to deal with. Many of the properties seem to encourage circular references where a property can simultaneously refer to the child and the parent. On top of the that, the various help files are not very helpful!

It seems a little odd that something as complex as the pivot table object doesn’t have a simple command to show everything for a given field when it has such robust commands to filter things. It reminds me of the word processing software I had for my British Acorn computer 10 years ago that had no “undo” command. Really. I called tech support because I couldn’t find it and they told me they hadn’t programmed it because they couldn’t see the need…

Reply | Quote

Just a followup. I have solved the problem with an insanely simple idea that made me realize it can get you into trouble to be too in love with code.

Rather than all the nonsense of trying to have code reset my pivot table to scratch after each use, it occurred to me that I could have a copy of the form that I keep pristine and don’t actually use. I have a button on a second form whose only job is to delete the working copy of the pivot form if it exists and copy the pristine one to a new form. All very simple using the Docmd.Delete and Docmd.Copy methods.

FrmA does the copying
FrmB is never used (pristine copy)
FrmC is the working copy. It gets deleted and recreated from FrmB everytime that the button is pressed on FrmA
I suppose I can say that the 5 or 6 hours I spent trying to fathom the pivot table object was a learning experience.

Reply | Quote