Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 904
  • Last Modified:

ADO Filtered Recordset to Crystal Reports Viewer

I am trying to filter a recordset and pass it to Crystal Reports.  However, Crystal still gets the full recordset...

Say rsCopy contains 100 records, then I apply a filter which takes rsCopy back to 25 records.  If I pass the recordset to Crystal, it has all 100 records.  How frustrating!  This seems like it should be so easy.

rsCopy.Filter = "sdtext LIKE '*" & strFilter & "*'"
CrxReport.Database.SetDataSource rsCopy

I have even tried making a new recordset based on the returned filter, such as:

Dim rsTemp as adodb.Recordset
rsCopy.Filter = "sdtext LIKE '*" & strFilter & "*'"
Set rsTemp = rsCopy
CrxReport.Database.SetDataSource rsTemp

And it still returns the unfiltered recordset...  UGH!

What am I doing wrong?

VB6 SP5, CR10, ADO 2.5

Thanks.
0
JayCrowe
Asked:
JayCrowe
1 Solution
 
TimCotteeHead of Software ServicesCommented:
Hi JayCrowe,

Nothing, however the .filter doesn't get passed to CR in any fashion. You either have to apply the filter as part of your where clause on generating the recordset or specify a filter within crystal seperately.

Tim Cottee
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
JayCroweAuthor Commented:
Maybe I could create a new recordset in VB memory and only ADD the records from the filtered recordset.  Tedious, but it may work.  I'll give that a try.

I am aware I can enter the criteria up-front during my SQL SELECT, but it's a little more complicated than that... Long story, but my select statement is longer than my arm already, plus the fact what I am really trying to do is run a LIKE string search against an Oracle 8i LONG field, which is not supported directly.  It is a long-description field (like a memo field) for our Maximo CMMS system.

Not pretty no matter how I look at it.

I could pass the criteria field to Crystal, but I would like to have the filtered recordset in VB memory so I could export, print, etc, outside of Crystal.  I guess I could still run the filter in VB, as well as inside the CR, I'm just trying to find the simplest answer.

Thanks for the suggestions.
0
 
JayCroweAuthor Commented:
Here is a solution...   This allows me to make a clone of the filtered recordset which contains OLNY the filtered recordset items:

Public Function FilterField(rsTemp As ADODB.Recordset, strFilter As String) As Boolean

If strFilter <> "" Then
    rsTemp.Filter = "sdtext LIKE '*" & strFilter & "*' OR ldtext LIKE '*" & strFilter & "*'"
    If Not rsTemp.EOF Then
        Dim oStream As ADODB.Stream
        Dim rsClone As ADODB.Recordset
        Set oStream = New ADODB.Stream
        rsTemp.Save oStream
        Set rsClone = New ADODB.Recordset
        rsClone.Open oStream, , , adLockUnspecified
        Set rsCopy = rsClone
        Set oStream = Nothing
        Set rsClone = Nothing
        FilterField = True
    End If
Else
    FilterField = True
End If

End Function

I return True/False so I can decide if any records are returned.

Here is how I call it:

If FilterField(rsCopy, txtContains.Text) = False Then GoTo NoResults

CrxReport.Database.SetDataSource rsCopy



How do I accept my own answer as the solution?

:o)

Jay
0
 
mlmccCommented:
0
 
CetusMODCommented:
PAQed, with points refunded (125)

CetusMOD
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now