?
Solved

ADO Filtered Recordset to Crystal Reports Viewer

Posted on 2004-04-07
6
Medium Priority
?
886 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 101

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month9 days, 9 hours left to enroll

762 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