Alvin,
The problem occurs whe an Access form is viewed as a PivotTable.
When a user double-clicks a detail cell, the form_DblClick event fires.
When code is run to capture the form_DblClick and process the
me.pivottable.selection object, one of two objects (for detail cells) is
found:
1) a PivotDetailRange object (when the double-clicked "detail" cell is bound
to a record found in the form's underlying recordset)
2) a PivotRange object (when the double-clicked "detail" cell does not yet
exist in the underlying form's recordset)
This much is fine and as expected.
When intercepting these objects (with code still executing within the
form_DblClick event handler), if one generates SQL and executes a recordset
UPDATE (in the case where the record existed already), the PivotTable behaves
as expected and visually updates the form's pivottable view after the
me.pivottabel.refresh method is called.
All this is not a problem and is behavior as expected.
However, if instead one generates a new record via an INSERT INTO query and
appends a new record to the form's underlying recordset (still all within the
Form_DblClick event handler), NOTHING will cause the new record's data to
appear immediately upon exiting the Form_DblClick event handler.
I have tried everything I can think of, including calling: me.requery or
screen.activedatasheet.requery (as the above referenced KB article says DOES
work) prior to the form.pivottable.refresh call. (I even tried putting in
DBEngine.Idle dbForceOSFlush and/or DBEngine.Idle dbRefreshCache to be SURE
the data got written before calling the form.requery or
screen.activedatasheet.requery!)
These steps do NOT caluse the newly appended (successfully - I check by
using an unnamed querydef object for the sql execution and using
oQd.RecordsAffected thereafter to acertain the success of the INSERT INTO
operation) record's bound field value to appear in the pivottable view
immediately after the Form_DblClick event hendler code exits. Even after
execution control returns to the form's UI, nothing causes the newly added
record value to be displayed (including pressing Shift-F9).
Interestingly, if the user re-double-clicks the same cell as the newly
appended record a second time, the form's PivotTable suddenly DOES recognize
and display the newly added record value *BEFORE* the Form_DblClick event
gets fired for the second time (although the Form_DblClick still receives a
PivotRange object instead of a PivotDetailRange object as one might expect -
this changes for subsequent 3rd-4th+ Double-clicks on the same detail cell).
This either has got to be a bug of some sort, or the KB article is just
wrong, or it's a really bad design defect, or some combination of all the
above.
(or, I know, I know, I'm stupid to hope that I could actually use a
PivotTable view for something useful like updating data this way - even if it
is a perfect UI for the task...)
The solution should be that what the article says really DOES work (or even
better, that some new me.pivottable.requery method does the
me.pivottable.refresh method one better and actually does account for
added/deleted records in only ONE call...)
Thanks for listening & responding!
If you want code snippets, and sample data, I can oblige.
Regards,
Mark Burns
Alvin Bruney - ASP.NET MVP said:
I'll start it on my end, just describe the problem and what you think should
be the solution.
--
Regards,
Alvin Bruney [MVP ASP.NET]
[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @
www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------
Mark Burns said:
Alvin,
Ok, I'd be glad to do that - How do I start an error ticket?
:
write up an error ticket and i'll see that the article gets corrected.
--
Regards,
Alvin Bruney [MVP ASP.NET]
[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @
www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------
Kb Article entitled: ACC2002: PivotTable or PivotChart Refresh Command
Does
Not Display New and Deleted Records
Please see my comments/question in:
microsoft.public.access.forms -> "Force Requery of a pivot table through
code"
for details on how ths KB article's "resolution" resolves nothing.
any advice/insight/assistance greatly appreciated.
