Solved

ADO Filtered Recordset to Crystal Reports Viewer

Posted on 2004-04-07
6
870 Views
Last Modified: 2013-12-25
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
Comment
Question by:JayCrowe
6 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 10774855
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
 

Author Comment

by:JayCrowe
ID: 10775037
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
 

Author Comment

by:JayCrowe
ID: 10775337
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 10810405
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 11350318
PAQed, with points refunded (125)

CetusMOD
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now