Re: Pivot Table: Asked Again
From: TomT (tomt_at_tomt.com)
Date: 02/15/05
- Next message: Wayne Morgan: "Re: If Statement Referencing Sub-Forms"
- Previous message: TomT: "Re: Pivot Table: Asked Again"
- In reply to: Ron Weiner: "Re: Pivot Table: Asked Again"
- Next in thread: TomT: "Re: Pivot Table: Asked Again"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Feb 2005 15:27:04 -0800
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
"Ron Weiner" wrote:
> 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
>
> "TomT" <tomt@tomt.com> wrote in message
> news:7ED1200B-4F76-4F8D-B07D-1E533E0CC027@microsoft.com...
> > 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
> >
> > "Douglas J. Steele" wrote:
> >
> > > 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 Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > >
> > > "TomT" <tomt@tomt.com> wrote in message
> > > news:C649977E-58E7-461D-8851-203D8CB54278@microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>
- Next message: Wayne Morgan: "Re: If Statement Referencing Sub-Forms"
- Previous message: TomT: "Re: Pivot Table: Asked Again"
- In reply to: Ron Weiner: "Re: Pivot Table: Asked Again"
- Next in thread: TomT: "Re: Pivot Table: Asked Again"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
