A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 20th, 2010, 07:46 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Pivot table/chart in subform - can't open Field List

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?
  #2  
Old January 21st, 2010, 04:28 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Pivot table/chart in subform - can't open Field List

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

frm!PivotTableSubForm.Form.PivotTable.DisplayField List = 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" wrote:

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?

  #3  
Old January 26th, 2010, 05:26 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Pivot table/chart in subform - can't open Field List

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.DisplayField List = 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?
  #4  
Old January 26th, 2010, 06:52 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Pivot table/chart in subform - can't open Field List

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/d...displaylang=en
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.