It rather astonishes me that this is the only reliable route I have found,
but here's a solution to this that very annoyingly creates a new
disconnected recordset and clones every single value across while the filter
is applied to the original.

I'd still be interested if anyone can think up something better.

Set rsFilteredRS = New ADODB.Recordset
rsOriginal.Filter = "filter string goes here"
Append RS rsFilteredRS, rsOriginal
'do the output

Public Sub AppendRS(rsOutput As ADODB.Recordset, _
rsInput As ADODB.Recordset)
' Procedure : AppendRS
' DateTime : 4/15/2004 12:24
' Author : Robin Hammond
' Purpose : creates a recordset if it doesn't exist and adds input values
to the output recordset

Dim nFieldCounter As Integer
'if the output rs is empty then copy across the fields
With rsOutput
If .Fields.Count = 0 Then
For nFieldCounter = 0 To rsInput.Fields.Count - 1
.Fields.Append rsInput.Fields(nFieldCounter).Name, _
rsInput.Fields(nFieldCounter).Type, _
rsInput.Fields(nFieldCounter).DefinedSize, _
(rsInput.Fields(nFieldCounter).Attributes Or _
adFldUpdatable) And Not adFldUnknownUpdatable
Next nFieldCounter
.Open CursorType:=adOpenStatic
End If
rsInput.MoveFirst
Do While Not rsInput.EOF
.AddNew
For nFieldCounter = 0 To .Fields.Count - 1
.Fields(nFieldCounter).Value =
rsInput.Fields(nFieldCounter).Value
Next nFieldCounter
rsInput.MoveNext
Loop
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com


"Robin Hammond" wrote in message
...
Has anyone found a way to get a pivottable to work with a filtered
recordset?

The intent is fairly simple. Filter a complex RS, pass a column, row, and
data parameter to a generic output routine, and use a pivottable to do the
output to a worksheet. It seems that although the recordset is filtered,
the
pivottable ignores the filtering and includes all items.

I'm sure I can get around it by adding an additional column and filtering
the column, pastevalues, delete the column, etc, but would rather the damn
thing behaved as it should.

Correct:
rsWhatever.Filter = "something"
Set pcOutput.Recordset = rsWhatever

Incorrect:
probably about 50 lines of nasty code adding additional columnfield and
filtering the pivottable.
Sometimes you have to question who's thinking these things up.

There's a thread with a response from Rob Bovey here saying that it can't
be
done directly, but I'd welcome other suggestions.


http://www.google.com.hk/groups?hl=e...r%3D%26hl%3Den

Robin Hammond
www.enhanceddatasystems.com

"Robin Hammond" wrote ...

It rather astonishes me that this is the only reliable route I have found,
but here's a solution to this that very annoyingly creates a new
disconnected recordset and clones every single value across while the filter
is applied to the original.

I'd still be interested if anyone can think up something better.

Hi Robin,
You can save the filtered recordset to an ADO Stream and open a new
recordset based on stream. Here's some code:

http://www.able-consulting.com/ADO_Faq.htm#Q42

Jamie.

--

Thanks Jamie.

Blazingly simple as usual, though I'd still like to know why the filters
aren't maintained when the RS is passed to the pivot.

Robin Hammond
www.enhanceddatasystems.com


"Jamie Collins" wrote in message
om...
"Robin Hammond" wrote ...

It rather astonishes me that this is the only reliable route I have
found,
but here's a solution to this that very annoyingly creates a new
disconnected recordset and clones every single value across while the
filter
is applied to the original.

I'd still be interested if anyone can think up something better.

Hi Robin,
You can save the filtered recordset to an ADO Stream and open a new
recordset based on stream. Here's some code:

http://www.able-consulting.com/ADO_Faq.htm#Q42

Jamie.

--