Solved

Bound Reports with VB and Crystal?

Posted on 1999-01-15
9
341 Views
Last Modified: 2012-05-05
I would like to create a Crystal report that I can change the recordset for in VB.  I.E.  Use the same fields over and over but dynamically change the Recordset used on the report by changing the Where statement in the recordset and refreshing it.  Is there a way?  I seem to always get the same data back reguardless of me chainging the recordset.

Thanks,
0
Comment
Question by:Rog
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1456597
Do you change your criteria property?

Your code should look like this:
   With CrystalReport1
      .SelectionFormula = "Field = value"
      .ReportFileName = App.Path & "\Report.rpt"
      .PrintReport
   End With

0
 
LVL 8

Author Comment

by:Rog
ID: 1456598
I agree with that answer, but the problem is that I do change where the database resides, so I don't want the report to be tied to the actual place of the data.  I have different copies of the same database tables, and want to dynamically change the database as needed inside the VB application.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 1456599
You can also play with another property: Datafiles.

It is a collection.
CrystalReport1.DataFiles(1) = "C:\test1.mdb"

0
 
LVL 10

Expert Comment

by:caraf_g
ID: 1456600
1 A class clsCrystal to deal with Crystal Reports:

Option Explicit
Private m_obj_CrystalApplication As CRPEAuto.Application
Public Sub LoadReports(i_strdsName As String, _
                       i_strdbName As String, _
                       i_strUid As String, _
                       i_strPwd As String)

On Local Error GoTo Err_LoadReports

Set m_obj_CrystalApplication = New CRPEAuto.Application
m_obj_CrystalApplication.LogOnServer "P2SODBC", _
                                     i_strdsName, _
                                     i_strdbName, _
                                     i_strUid, _
                                     i_strPwd

Exit Sub
Err_LoadReports:
EndUnexpectedError "LoadReports", Err.Number, Err.Description

End Sub
Public Sub UnloadReports()

On Local Error Resume Next

Set m_obj_CrystalApplication = Nothing

End Sub
Public Property Get Report(i_strReportName As String) As CRPEAuto.Report

'Report is not already loaded. Try to open it now and add it to the
'collection of loaded reports.
On Local Error GoTo Err_Report
Set Report = m_obj_CrystalApplication.OpenReport(i_strReportName)

Exit Property
Err_Report:
EndUnexpectedError "Report (Property Get)", Err.Number, Err.Description

End Property
Public Sub SetParameter(i_objReport As CRPEAuto.Report, _
                        ByVal i_strParametername As String, _
                        i_varNewValue As Variant, _
                        Optional i_ParameterType As Variant)
                       
On Local Error GoTo Err_SetParameter

Dim objParameters As CRPEAuto.ParameterFieldDefinitions
Set objParameters = i_objReport.ParameterFields

Dim objParameter As CRPEAuto.ParameterFieldDefinition
Set objParameter = objParameters(i_strParametername)

If IsMissing(i_ParameterType) Then
    objParameter.SetCurrentValue i_varNewValue
Else
    If Not TypeOf i_ParameterType Is CRPEAuto.CRFieldValueType Then
        Exit Sub
    End If
    objParameter.SetCurrentValue i_varNewValue, i_ParameterType
End If

Exit Sub

Err_SetParameter:
If Err.Number = 9 Then
    'Expected error: this report doesn't have this parameter
    Exit Sub
End If
EndUnexpectedError "clsCrystal.SetParameter", Err.Number, Err.Description

End Sub

'EndUnexpectedError is a function I've written to show the user what went wrong where - you can replace it with something else...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Expert Comment

by:caraf_g
ID: 1456601
A possible use of clsCrystal:

(g_objCrystal is defined as an object of type clsCrystal)

Set objReport = g_objCrystal.Report(strReportFile)
g_objCrystal.SetParameter objReport, "UserName", g_str_CurrentUserID
objReport.SQLQueryString = objReport.SQLQueryString & " AND " & strSQLSelectionClause
objReport.Preview

'Look at the .SQLQueryString property. This will contain your Where Clause and you can add to it and change it.

'The SQLQueryString property contains the full query including the SELECT .. From bit and potentially and Order By bit. All these bits are separated from each other by newlines or carriage returns. I don't remember what exactly but it's reasonably easy to find out. I'll check the exact separator character if you wish but it could be a few days before I get the chance.
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 1456602
?
0
 
LVL 8

Author Comment

by:Rog
ID: 1456603
More information was needed.  Thanks for all the comments though.
0
 
LVL 4

Accepted Solution

by:
arcusd earned 200 total points
ID: 1456604
use an odbc connection in that way you can dynamically change the location of the database files. put in an ini file the location of the database (let's assume it an access
database) :
dbpath=c:\test\mydb.mdb

Then from your app create a procedure that will create an odbc dsn in which the location
of the database is set to the one in the ini. Perform this routine every time u launch the app.


Function OpenDB(ByVal mdbPath As String) As Boolean
 
   Dim strDSN, strDriver, strAttribs As String
   
   strAttribs = "Description=" + App.Title + "  DataSource" _
          + Chr$(13) + "DBQ=" + mdbPath
   
   On Error GoTo err_dbopen:
     
   rdoEngine.rdoRegisterDataSource App.Title, _
               "Microsoft Access Driver (*.mdb)", True, strAttribs
     
   Set myJet = rdoEngine.rdoEnvironments(0)
   Set mydb = myJet.OpenConnection(dsname:=App.Title, Prompt:=rdDriverNoPrompt, _
      Connect:="UID=Admin;PWD=" + ";")
     
   OpenDB = True
   
   Exit Function
   
err_dbopen:
   Call ErrLog
   OpenDB = False
     
End Function

This procedure registers an ODBC Datasource with the DSN the same as the App.Title
and pointing to the database supplied as a parameter. Then when you create the report
be sure to use the ODBC data source. So your report is not binded to the location of the
database, instead it is binded on the ODBC DSN which you can modify the location just
by editing the value i your ini file.

0
 
LVL 8

Author Comment

by:Rog
ID: 1456605
Thanks...

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 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

19 Experts available now in Live!

Get 1:1 Help Now