Solved

Bound Reports with VB and Crystal?

Posted on 1999-01-15
9
344 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 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 10 start screen issues 9 55
Hide vba in gp 7 94
clicking a shape in a frame array vb6 3 45
Error with a code discussed on this page 5 14
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…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…

773 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