?
Solved

visual basic code to open crystal report

Posted on 2004-11-11
11
Medium Priority
?
1,264 Views
Last Modified: 2013-11-25
Hi,

Well i have a Visual Basic 6.0 and Crystal report 8.5 installed.
Now the other user has made verious crystal report for one database.I mean like sales report annual,sales report monthly.

So i want to create a form where i can choose his crystal report and then it will show in other window may be a viewer or something.

SO it may be like .. File -> Open -> and then i browse to select a crystal report file.

and when i press ok it will show me the report or if in the report it is designed like range of month or account number then it will ask the required  field.

I think the main thing is to open different rpt files by just file-open and browse.

thanks

HARRY

0
Comment
Question by:harry_cyprus
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12553120

The File Open requirement you have can be done using the Common Dialog (you need to add it to your project's Components)

This has various capabilities for displaying Open/Save dialogs, Print dialog, Color, dialog, etc.

You can set the file type to just expect .rpt files so the users will only be browsing for those.

If the report needs variables completed (date range for example) then this is best done using Crystal's parameter functionality which can prompt the user when the report is opened.

0
 
LVL 1

Author Comment

by:harry_cyprus
ID: 12553223
hi rogue,

Well thanks for your reply.

now my point is how do i connect vb form to crystal report viewer.

i have added menu's like file -> open etc but the whole point is opening the crystal report

and showing the database.

well just tell me how can i connect the VB form to crystalreport .

Actually i made a button now if i click that button its opening the default crystal report which is c:\harry\statement.rpt

below is my code.

Form1.CrystalReport1.WindowTitle = "REPORT"
                Form1.CrystalReport1.ReportFileName = "c:\harry\statement.rpt"
        Form1.CrystalReport1.ReplaceSelectionFormula ("")
        Form1.CrystalReport1.WindowState = crptMaximized
        Form1.CrystalReport1.Destination = crptToWindow
        Form1.CrystalReport1.Action = 1

now good thing is as i am clicking that button its opening the crystal report with the parameter and its asking me account number and date etc. which is correct.

but as soon as it finishes taking all input values , and try to show the output i see an error :
run time error 20599
Cannot open sql server"

pls help where do i specify server name

thanks

harry
0
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12553300
Harry

Unfortunately I have Crystal 10 and they have changed things fairly dramatically.  I no longer have the simple CrystalReport1 viewer.

If I remember correctly you have a .Database property?

That said, you shouldn't need to change the database settings if the report is already configured to access the SQL server

Might be best to look at the report in the Crystal Report Designer, do a verify of the databases and make sure it works outside of VB

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Accepted Solution

by:
VX70 earned 300 total points
ID: 12553542
This is how Im going it.

add references to

1) Crystal Reports 8 ActiveX Desighner Run Time Library

Add the following components
1) Crystal Report Viewer Control


Private Sub ShowReport()
Dim sVal As String
Dim sVal1 As String

On Error GoTo Local_Err:

        Set Report = crxApplication.OpenReport("C:\Report.rpt", 1)
 
        Report.DiscardSavedData
 
        On Error GoTo Local_Err:
        Report.Database.LogOnServer "pdsodbc.dll", "respons", "respons", "sysadm", "sysadm"
       
        frmReportViewer.Caption = lstReport
        frmReportViewer.Show
Exit Sub

Local_Err:
        MsgBox " Err.Number:         " & Err.Number & vbLf _
             & " Error Description:  " & Err.Description & vbLf _
             & " Error Source:       " & Err.Source & vbLf _
             , vbCritical, "Error in Reporting." & Me.Name & ".ShowReport"
LogOnServer_Err_End:
End Sub


VX
0
 

Expert Comment

by:VX70
ID: 12553559
Sorry .. forgot some declarations

Dim Report As CRAXDRT.Report
Dim crxApplication As New CRAXDRT.Application
0
 
LVL 1

Author Comment

by:harry_cyprus
ID: 12553612
well well,

i am really confused .

Can somebody just give me a simple example

1.View crystal report from VB.

and open different crystal reports.

my email is h_multani@hotmail.com

Well i know its not good to give email on this site but i am really in need of clues to finish this VB crystal report story.

the above solution didnt work.
I am still getting the sql server error

thanks

harry
0
 
LVL 5

Expert Comment

by:RogueSolutions
ID: 12553692

It may sound obvious but you have to change the database names, and login info given in VX70's code (the LogOnServer bit) to whatever your database and login is.  Do NOT publish that stuff here.

--

If you open the report in the normal Crystal Reports Designer (nothing to do with VB) - does the report work?



0
 
LVL 1

Author Comment

by:harry_cyprus
ID: 12553734
BTW the report is working fine in the asp pages and other applications also.

same error

0
 

Expert Comment

by:VX70
ID: 12553765
Create an ODBC pointing to your database and use it here...

Report.Database.LogOnServer "pdsodbc.dll", "YourDB", "YourDB", "UserName", "Password"
0
 
LVL 1

Author Comment

by:harry_cyprus
ID: 12554438
ok i s there any software to view crystall reports directly.
i mean like there is powerpoint  and power point viewer
is there  a way i will just double click the .rpt crystal report file and it will show me the report

thanks

harry
0
 
LVL 2

Expert Comment

by:kishore3576
ID: 12572302
Here is the code I am using

Here frmCaller is the form from which I am calling the report form. I am having a single report form to display multiple reports (named frmReport).

On the basis of the form that is calling this frmReport, I am assigning a value to the variable iReport (a public variable)

Here are the declarations
----------------------------------------------------------------------------
Dim crystal As CRAXDDRT.Application  'LOADS REPORT FROM FILE
Dim report As CRAXDDRT.report        'HOLDS REPORT
Dim crpParamDefs As CRAXDDRT.ParameterFieldDefinitions
Dim crpParamDef As CRAXDDRT.ParameterFieldDefinition
Public rs As Recordset
Dim rptName  As String
Public frmCaller As Form

This is the load of the form frmReport
*********************************************
Private Sub Form_Load()
    Me.WindowState = 2
End Sub

This is the resize event of the frmReport
*********************************************
Private Sub Form_Resize()
    crvReport.Top = 0
    crvReport.left = 0
    crvReport.Height = ScaleHeight
    crvReport.Width = ScaleWidth
    Call InitializeForm
End Sub


*********************************************
Public Sub InitializeForm()
    Set crystal = New CRAXDDRT.Application
    Set report = New CRAXDDRT.report
    Set crpParamDefs = report.ParameterFields
    crvReport.DisplayBorder = False
    crvReport.DisplayTabs = False
    crvReport.EnableDrillDown = False
    crvReport.EnableRefreshButton = False
    crvReport.EnableExportButton = True
'    Set CRXDatabase = CRXReport.Database
    Call CheckReport ' Here I am finding the report to be displayed
    Set report = crystal.OpenReport(Globals.sReportPath & rptName)
    Call AssignParameter 'here I am passing any formula to the report
   
    report.DiscardSavedData
    report.Database.SetDataSource rs

    crvReport.ReportSource = report
    crvReport.ViewReport
   
    Do While crvReport.IsBusy            'ZOOM METHOD DOES NOT WORK WHILE
       DoEvents                         'REPORT IS LOADING, SO WE MUST PAUSE
    Loop                                'WHILE REPORT LOADS.
       
    crvReport.Zoom 94
   
    'Once the report is displayed, we can then cleanup out objects:
   
    rs.Close 'ALL BELOW HERE IS CLEANUP
    Set rs = Nothing
       
    Set crystal = Nothing
    Set report = Nothing

End Sub


Here I am checking the report to be displayed
*********************************************
Public Sub CheckReport()
    Select Case iReport
        Case 1
            rptName = "ItemSold.rpt"
            Exit Sub
        Case 2
            rptName = "HBOItemCons.rpt"
            Exit Sub
        Case 3
            rptName = "HBOWiseInv-Acc.rpt"
            Exit Sub
        Case 4
            rptName = "HBOWiseInv-cons.rpt"
            Exit Sub
        Case 5
            rptName = "HBOCommission.rpt"
            Exit Sub
        Case 6
            rptName = "Achievers.rpt"
            Exit Sub
        Case 7
            rptName = "StateCityWise.rpt"
            Exit Sub
        Case 8
            rptName = "Abr.rpt"
            Exit Sub
    End Select
End Sub

Here I am passing value to the formula field of the report.
*********************************************
Public Sub AssignParameter()
    Select Case iReport
        Case 1
            report.FormulaFields(1).Text = "'" & CStr(frmCaller.txtfrmInvNo.Text) & "'"
            report.FormulaFields(2).Text = "'" & CStr(frmCaller.txtToInvNo.Text) & "'"
        Case 6
            Call MonthYear(CStr(frmCaller.cmbYrMth.Text))
            Month = Month + "-" + CStr(Mid(frmCaller.cmbYrMth.Text, 1, 4))
            report.FormulaFields(1).Text = "'" & Month & "'"
        Case 7
            report.FormulaFields(1).Text = "'" & CStr(frmCaller.txtFmDate.Text) & "'"
            report.FormulaFields(2).Text = "'" & CStr(frmCaller.txtToDate.Text) & "'"
        Case 8
            Call MonthYear(CStr(frmCaller.cmbMonth.Text))
            report.FormulaFields(7).Text = "'" & Month & " - " & CStr(frmCaller.cmbYear.Text) & "'"
'            report.FormulaFields(2).Text = "'" & CStr(frmCaller.txtToDate.Text) & "'"
    End Select
End Sub
-------------------------------------------------------------------------

Hoping that this will help U.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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 …
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…
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

809 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