Solved

Bound Reports with VB and Crystal?

Posted on 1999-01-15
9
347 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
[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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 70

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 70

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
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!

 
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
 
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

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

730 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