Pivot table/chart in subform - can't open Field List

[Boggle]

I have an MDB (developed in Access 2003) with forms which include subforms on
a tab control. Some of the subforms are in PivotTable or PivotChart view
(with a SQL recordsource). I want the users to be able to change the layout
of these views. I am now testing in Access 2007.

The usual drag and drop of existing field buttons works fine. On right
click the usual context menu pops up, but Field List is greyed out, so the
user cannot add any new items from the field list.

If the same subform is opened directly as a form then the context menu is as
normal.

Oddly if another (simple) form is open in PivotTable or PivotChart view you
can get the Field List up. Then when you switch focus to the subform within
the main form, the Field List remains, and its contents change to match the
record source of the subform, and everything now works. However it isn't
much use as a workaround for a user - unless I can launch Field List from VBA?

 

[Boggle]

OK, looks like I've got a workaround for pivot tables. If you do:

frm!PivotTableSubForm.Form.PivotTable.DisplayFieldList = True

then the field list pops up. So I shall probably put that on a button on
the main form (and disable the button when the user isn't looking at a pivot
table).

However, it doesn't work for pivot charts. Note that the pivot chart object
is still referenced via the PivotTable property of the subform. You can
query the Version so it really is there. But the DisplayFieldList property
can be set True without making any difference. You can get the Field List up
by going to Change Chart Type, which gives you the Properties dialog, then
the Show/Hide tab, then Show by default: Field list. But it doesn't remember
the setting (even in a stand-alone pivot chart form).

Any ideas? Does anybody have complete documentation for the object model
because I've looked around and found only bits and pieces. The Version
property shows up as 12.0.0.6423, so it isn't using OWC10.dll or OWC11.dll (I
can't find either on my machine anyway) and I don't even know which DLL I
should be referencing to use Object Viewer or Intellisense.

Not sure why MS treats this powerful functionality as if it were some kind
of embarrasing mistake they don't want to talk about.

 

[Boggle]

Just in case anyone else is looking for an answer ...

I was wrong about the pivot chart being accessed via the PivotTable property
of the form. I guess the form has the stub of a pivot table even when you've
never set one up or used that view. Instead you need to use the
(inconsistently named) ChartSpace property:

frm!PivotChartSubForm.Form.ChartSpace.DisplayFieldList = True

Which would have been really easy to find out if we had a published object
model, or some information about which DLL to reference from the VBA project.

Hello Microsoft! Fancy helping out with that?

 

[Boggle]

And finally - I found a download of a compiled help file for Access 2003
Office Web Components (OWC11), owcvba11.exe which is a self-extracting
archive of owcvba11.chm. So far the properties seem to have been preserved
in the 2007 version, but I've only checked the display options I needed.

http://www.microsoft.com/downloads/...a9-cf5c-4dd7-8505-8074745a6d11&displaylang=en