Force requery of a pivot table through code

  • Thread starter Thread starter bdtmike
  • Start date Start date
B

bdtmike

Strange one here...

Got a form that's opened as a pivottable. It is based on queries that
get their parameters from yet another form. When I change the
parameters I want to do a form.requery on the pivot table form and have
the data requeried. Turns out that I can fire off the requery method
of the form....the OnQuery event triggers....but the underlying queries
are not rerun.

Very little about this on google or Microsoft. Anyone know how to
solve this problem (other than Shift <F9>)?
 
Mike,

I have a very similar problem:
I have a PivotTable form which is based on a very simple table in crosstab
query view. By trapping form_double-clicks on pivottable detail item cells, I
am building an update query to the data table and toggling the field value
that the detail cell is bound to.
This works perfectly, and a full requery of the form recordset is not
required as pivottable.refresh works fine for this.
However, when the user double-clicks on a detail cell for which no record
exists (which I can trap similarly), I create a new record in the table, and
set the field of interest to a non-null value. I can verify that the record
is indeed created, but thereafter, no amount of FORM.REQUERYing or
PIVOTTABLE.REFRESHing I do (including doing Shift_F9 manually) will show that
newly appended record from the table (even with DBEngine.idle dbForceOSFlush
and DBEngine.Idle dbRefreshCache tossed in for good measure). What's
interesting is that if the user Double-clicks that (previously empty) cell
again, the new record value is *instantly* found and displayed, _even before
the form_Doubleclick event fires_...???
WTF???
How do I force the form.pivottable control (which is apparently a different
interface from the owc11.pivottable object itself) to recognise and display
the newly added record post-INSERT without the necessity of any further
end-user double-clicking???
(Access 2003 SP2 11.6566.6568)

....or do I somehow have to do the new record append operation from the
form's own recordset object...?
 
All,

I found this MSDN KB Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;299825
which discusses this problem, but the "resolution" DOES NOT HELP HERE!
No form.requery or screen.activedatasheet.requery calls PRIOR to
form.pivottable.refresh seem to resolve the problem (to say nothing of the
performance issues)!!!

* IF THIS BEHAVIOR IS BY DESIGN, THEN THE DESIGN S____, erm... NEEDS SERIOUS
IMPROVEMENT!! *

To sumarize the problem again:
user double-clicks a blank detail cell on an access for in a pivottable view.
form_dblclick fires
code traps the me.pivottable.selection object, and checks for the pivotrange
object type. If the range object is 1 cell-by-1 cell, we build a sql string
for an appropriate INSERT INTO query, create a temp (unnamed) querydef object
and execute the query (successfully) to append the desired new record.

*** After which, NO AMOUNT OF:
dbengine.idle dbforceflush
dbengine.idle dbrefreshcache
form.requery
screen.activedatasheet.requery (<--as the KB article says will work)

*** PRIOR TO DOING A
Me.PivotTable.Refresh

*** ...will result in the newly created record being displayed in the
pivottable view of the form!!

Any help appreciated!
 
Back
Top