Solved

Bound Reports with VB and Crystal?

Posted on 1999-01-15
9
338 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Expert Comment

by:caraf_g
Comment Utility
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
Comment Utility
?
0
 
LVL 8

Author Comment

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

Accepted Solution

by:
arcusd earned 200 total points
Comment Utility
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
Comment Utility
Thanks...

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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.
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…
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…

743 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

13 Experts available now in Live!

Get 1:1 Help Now