data access page + pivotTable (yeah I know, I know...)

[Atomsk]

...it seems like putting "DAP" and "pivotTable" in a subject automatically guarantees a thread with zero replies, but I'll ask anyway!

-------------------------------------------------------------------------------------------

In Access 2000, I have a data access page. It has a pivotTable currently displaying all of the fields and records in my table (technically, there are no rows or columns, I just dragged my entire table into the "Totals" field).

This isn't conventional pivotTable usage in the sense that I'm not doing any calculations; I just need a way to quickly summarize the current database. It was not done in the pivotTable wizard because it requires you to have at least 1 row or column.

You'll also notice a dropdown list in the lower left. The values that you can see are all queries that I've defined. Here's what I want to do:

I'd like to make it so that when an option is selected out of the dropdown list, the pivotTable will filter and display the records by its' corresponding query. Thanks for any help, and for reading this post.

(P.S. If anyone knows of a control equivalent to a pivotTable that works in forms, I'd like to hear it, as that's the only reason I'm working with DAPs.)

 

[Atomsk]

BuMp!

Progress has been made!

<SCRIPT language=vbscript event=onclick for=DropdownList0>
<!--
If DropdownList0.selectedIndex = 0 Then

Set pview = PivotTable5.ActiveView
Set c = PivotTable5.Constants
Set fld = pview.FieldSets("Request ID").Fields(0)
fld.FilterFunction = c.plFilterFunctionInclude
fld.FilterMembers = Array("1","2")
-->
</SCRIPT>


This will filter the table for whichever rows I enter for the array, but it's still not automated yet. Are there any ways to access queries from here?

 

[Atomsk]

...and maybe it doesen't....

/sigh

With multiple dropdown indexes, the line

Set pview = PivotTable5.ActiveView

screws everything up since, after the view is modified, the table has no way to revert back to what it was before, and ends up working off of those values instead of resetting/replacing.

 

[Atomsk]

But wait!

<SCRIPT language=vbscript event=onclick for=DropdownList0>
<!--

Set pview = PivotTable5.ActiveView
Set c = PivotTable5.Constants
Set fld = pview.FieldSets("Request ID").Fields(0)
fld.FilterFunction = c.plFilterFunctionInclude

If DropdownList0.selectedIndex = 0 Then
fld.FilterMembers = Array("*")

Elseif DropdownList0.selectedIndex = 1 Then
fld.FilterMembers = Array("1","2","3")

Elseif DropdownList0.selectedIndex = 2 Then
fld.FilterMembers = Array("7","8","10")

-->
</SCRIPT>


Doing it this way fixes the constrained results, though it limits me to using only one field (Request ID). But if I can just figure out how to access the [Request ID] fields for my queries, I'll be set!


(...who am I talking to?)