Pivot Table: Asked Again

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to edit data in an Access 2003 pivot table? If not directly,
are there any workarounds?

I was seeing if I could e.g. do something via a click event in a cell, but
it does not fire. It appears there are some events for the pivot table
object, but I cannot find any info on this.

thanks for any help,

TomT
 
No, it's not possible. That's because the values in a pivot table represent
an arithmetic operation on an unknown number of fields. If the cells
represents the avg, for instance, what row(s) in the table would you update?
 
Doug,

Thanks for your reply. I'm just experimenting, with one based on a single
table. The cells just represent actual values, not calculations.

Is there any way you know of to get an event to fire off, e.g. if one of the
cells is double-clicked?

Tom
 
Tom

Instead of using automation with an Excel Pivot Table on an Access Form, you
might consider building a form based on a Access Crosstab query. This does
not immediately solve you problem of being able to edit the fields (as
Crosstab queries are NEVER updatable), but you do get more control over what
the user is doing with an access form rather than an Excel Pivot table.

If you absolutely must have the ability to edit the results of a crosstab
then you will have to "Roll Your Own" by:

Inserting the results of a crosstab query to a temp table
Binding a form to the temp table
Then at an appropriate time (when the form closes), write all changes back
to your normalized data structure using custom code that you will have to
write.

There are lots of gotcha's in this process, and it does require at minimum
an intermediate level of VBA programming expertise, but is absolutely
doable. You may want to re-think your needs before you set out on this
road.

Ron W
 
Ron, thanks for your reply. I have a table, Budget, which has e.g., columns
for amount, account, and period (month).

My client wants to view this data (as well as edit it) with the column
headings being each month, the rows the accounts, and the values the amount.
E.g.:

Account January February March
12345 500 1000 750
45678 650 120 400

etc

If I can't edit the values directly, I was hoping to be able to fire off a
double-click event on the amount value, which I could then use to give them a
way to provide a new value via a pop up form (for example). I could use the
account and period of the current record to write the new value to the table.

A form based on a crosstab might work, since at least I could work with the
events for the controls. I thought I read something about a pivot table being
in "edit mode", somewhere in the help files, but that might not have been
referring to the Access implementation of the pivot table - there were a
bunch of properties mentioned not available in the screen I was working in.

Thanks again,

Tom
 
Doug,

Thanks very much for your time on this. If you like, I explained a bit more
of what I'm trying to do in my response to Ron.

Thanks again for your help,

Tom
 
Hi Tom,

As MVP Doug Steele said, it is not possible to edit data in an Access Pivot
Table, which is totaling data like a totals query where access cannot
determine which record to change so that data cannot be edited in a pivot
table in Access.

Additionally, in Access 2002, several new events have been added to the
Access 10.0 Object Library to give developers even more control over
PivotTable and PivotChart views. This article gives a brief introduction to
these new events and how they might be used.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/ht
ml/odc_accessowc.asp



Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top