Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Pivot syntax (2003 SP3)

    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("NewO rRet").Fields("NewOrRet").IncludedMembers = all

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Pivot syntax (2003 SP3)

    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... <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Pivot syntax (2003 SP3)

    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...)

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Pivot syntax (2003 SP3)

    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. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •