Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Bound Reports with VB and Crystal?

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
Rog D
Asked:
Rog D
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Rog DManager Inforamtion SystemsAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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!

 
caraf_gCommented:
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
 
caraf_gCommented:
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
 
InteqamCommented:
?
0
 
Rog DManager Inforamtion SystemsAuthor Commented:
More information was needed.  Thanks for all the comments though.
0
 
arcusdCommented:
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
 
Rog DManager Inforamtion SystemsAuthor Commented:
Thanks...

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now