Crystal Report Access from VBA

[zahid999]

Dear All,

I developed Crystal Reprot using Great Plains Database and i would like to access from Great Plains could some one provide the details how can i access report.

Best Regards

zahid

 

[bradth]

So what you want to do is view the crystal report on a form in GP? What you need to do is edit the form and create a button on the form. Then use VBA to connect the button to the form. If you need any help doing this, just post again, i've done this many times before.

Brad

 

[zahid999]

Hi Bradth,

Thanks very much for your support, this is what exactly i am looking forward to get the result.

I would appreciate if you provide me the details with code.

Kind Regards,
zahid

 

[bradth]

Alright, here is the code that you will need. First create a button on the GP form (Through Tools > Customize > Modify current window). Once that button is created, make sure you go into the advanced security and set up the form so that you are using the modified form that you have just created, and not the original. Now go into VBA and use the following code:

Place in Declarations

'Variables to use to connect to database
Dim cn As ADODB.Connection
Dim userinfo As New RetrieveGlobals.retrieveuserinfo 'new object of RetrieveGlobals.retrieveuserinfo type
Dim interCompanyId As String
Dim userId As String
Dim sqlPassword As String
Dim sqlDataSourceName As String
Dim constring As String

Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String

'Public CRViewer1 As New CRViewer

Public crxApplication As New CRAXDRT.Application
Public crxReport As New CRAXDRT.Report

Place in your button-click

Private Sub btnPrint_Click()
If crwApplication Is Nothing Then
    Set crwApplication = CreateObject("Crystal.CRPE.Application")
End If

    'Open the SQL connection
    Set cn = New ADODB.Connection
    'Concatenate string to connect to the SQL database
    constring = "Provider=MSDASQL" & _
            ";Data Source=" & sqlDataSourceName & _
            ";User ID=" & userId & _
            ";Password=" & sqlPassword & _
            ";Initial Catalog=" & interCompanyId

    With cn
        .ConnectionString = constring
        .CursorLocation = adUseNone
        .Open
    End With

    Set rst = New ADODB.Recordset
    strWCPN = VBA.Trim$(Me.ItemNumber)
    strSQL = "execute dbo.GPD_Select_ItemMPN" & " '" & strWCPN & "'"
    rst.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    
    
    Set crxReport = crxApplication.OpenReport("d:\MPNReport.rpt", 0)
    crxReport.ParameterFields(1).SetCurrentValue (Me.ItemNumber)
    crxReport.Database.SetDataSource rst, 3, 1
    
    crxReport.DiscardSavedData 'chuck out old data
    crxReport.ReadRecords 'reads the record without actually calling the printer
    'crxReport.PrintOut 'calls the printer
    
    CrystalReportViewer.CRViewer1.ReportSource = crxReport ' set crystal report source
    'set dimension of the form where the crsytal report control is placed
    CrystalReportViewer.Height = 550
    CrystalReportViewer.Width = 750

    'set dimension for crystal report viewer
    CrystalReportViewer.CRViewer1.Top = 0
    CrystalReportViewer.CRViewer1.Left = 0
    CrystalReportViewer.CRViewer1.Height = 500
    CrystalReportViewer.CRViewer1.Width = 700
    CrystalReportViewer.CRViewer1.EnableGroupTree = False 'disable group tree button
    CrystalReportViewer.CRViewer1.EnableRefreshButton = False ' disable refresh button
    
    CrystalReportViewer.CRViewer1.ViewReport 'brings up crystal report in preview mode
    CrystalReportViewer.Show 'show the form where the crystal report control is placed

    rst.Close
    cn.Close
    Set rst = Nothing
    Set cn = Nothing
End Sub

Place in Userform-Initialize

 'RetrieveGlobals
    sqlDataSourceName = userinfo.sql_datasourcename()
    userId = userinfo.retrieve_user()
    sqlPassword = userinfo.sql_password()
    interCompanyId = userinfo.intercompany_id()

That should work for you. Of course you'll have to tailor a few parts to you form, but this code works. Any questions just post again.

 

[zahid999]

Hi Bradth,

Thanks very Much.

I will fillup the code as per your instruction and let you know the status.

Once again thanks for your valuable support.

Kind Regards,

Zahid

 

[zahid999]

Hi Bradth,

As per the code provide by you, I am facing little difficulty, will you please look at it and help to solve the problem.

I added following reference to VBA before proceeding to Crystal Report printing.

RetrieveGlobals , Microsoft ActiveX Dataobject 2.7 Library , Crystal Report Viewer control 9, Crystal Reports Common Object Model Library 2.0

Now On Vendor Maintenance window I added button called
“Report Print” and added the modified window to VBA, Also added the field “Report Print” to VBA.

Now Under VBA Declaration I added the code provided by you.
Also At field “Report Print” Change Event, added the code as per your instruction.

Only changes I have made are to SQL. I have simple stored procedure name VENDPRNT.
Defined as “ Select * from PM00200 “

It gives me Error at declaration says user-defined type not defined.

Public crxApplication As New CRAXDRT.Application
Public crxReport As New CRAXDRT.Report

I appreciate your time and Knowledge.

Thanks,

Best Regards
zahid

 

[bradth]

Alright, all the references that I have checked are as follows:

- Crystal Report 8 ActiveX Designer Design Rime Library(internal)
- Crystal Report 8 ActiveX Designer Run Time Library
- Crystal Report Engine 8 Object Library
- Crystal Report Viewer Control
- Crystal TSLV Library 1.0
- Crystal Report Control
- RetrieveGlobals

Try selecting these references as well and see if that works. If it doesn't let me know.

Brad

 

[zahid999]

Hi Bradth,

can u check following three line in your code. from where crwapplication is coming. is it a typing mistakes ?

Private Sub btnPrint_Click()
If crwApplication Is Nothing Then
Set crwApplication = CreateObject("Crystal.CRPE.Application")
End If

because in declaration following is defined

Public crxApplication As New CRAXDRT.Application

if i correct to crxapplication after that code gives me error at following line in your code.

CrystalReportViewer.CRViewer1.ReportSource = crxReport

Looks like once CrystalReportViewer Error solved after that it should works. because it has asked me for printing no of copies option also.

waiting for your suggession.

Best Regards
zahid

 

[bradth]

You could probably entirely get rid of these lines of code:
If crwApplication Is Nothing Then
Set crwApplication = CreateObject("Crystal.CRPE.Application")
End If

For example here's the code that I have inside of my Print button:

Private Sub cmdPrint_Click()

Dim i As Integer

On Error GoTo Error_Handler

For i = 1 To mRowCount
    Debug.Print "row count" & mRowCount
    If mDataArray(i).intLabel > 0 Then

        Set crsReport1 = crxApplication.OpenReport(RPT1, 0)
        Set crsReport2 = crxApplication.OpenReport(RPT2, 0)
        
        'crsReport1.FormulaFields.Item(1).Text = Chr$(39) & mDataArray(i).strbarcode & Chr$(39)
        crsReport1.FormulaFields.Item(1).Text = Chr$(39) & "PO#: " & strPONumber & Chr$(39)
        crsReport1.FormulaFields.Item(2).Text = Chr$(39) & mDataArray(i).strbarcode & Chr$(39)
        
        'crsReport1.PrintOut False, mDataArray(i).intLabel

       crsReport2.FormulaFields.Item(1).Text = Chr$(39) & CStr(CLng(mDataArray(i).fltQty)) & Chr$(39)
       'crsReport2.PrintOut False, mDataArray(i).intLabel

    crsReport1.DiscardSavedData 'chuck out old data
    crsReport1.ReadRecords 'reads the record without actually calling the printer
    'crsReport1.PrintOut 'calls the printer
    
    CrystalReportViewer.CRViewer1.ReportSource = crsReport1 ' set crystal report source
    'set dimension of the form where the crsytal report control is placed
    CrystalReportViewer.Height = 380
    CrystalReportViewer.Width = 580
   
    'set dimension for crystal report viewer
    CrystalReportViewer.CRViewer1.Top = 0
    CrystalReportViewer.CRViewer1.Left = 0
    CrystalReportViewer.CRViewer1.Height = 400
    CrystalReportViewer.CRViewer1.Width = 600
    CrystalReportViewer.CRViewer1.EnableGroupTree = False 'disable group tree button
    CrystalReportViewer.CRViewer1.EnableRefreshButton = False ' disable refresh button
    
    CrystalReportViewer.CRViewer1.ViewReport 'brings up crystal report in preview mode
    CrystalReportViewer.Show 'show the form where the crystal report control is placed

    crsReport2.DiscardSavedData 'chuck out old data
    crsReport2.ReadRecords 'reads the record without actually calling the printer
    'crsReport2.PrintOut 'calls the printer
    
    crystalReportViewer2.CRViewer1.ReportSource = crsReport2 ' set crystal report source
    'set dimension of the form where the crsytal report control is placed
    crystalReportViewer2.Height = 380
    crystalReportViewer2.Width = 580
   
    'set dimension for crystal report viewer
    crystalReportViewer2.CRViewer1.Top = 0
    crystalReportViewer2.CRViewer1.Left = 0
    crystalReportViewer2.CRViewer1.Height = 400
    crystalReportViewer2.CRViewer1.Width = 600
    crystalReportViewer2.CRViewer1.EnableGroupTree = False 'disable group tree button
    crystalReportViewer2.CRViewer1.EnableRefreshButton = False ' disable refresh button
    
    crystalReportViewer2.CRViewer1.ViewReport 'brings up crystal report in preview mode
    crystalReportViewer2.Show 'show the form where the crystal report control is placed
       
    End If
Next

Exit Sub

End Sub

The only thing that is different is that I am declaring both reports as constants at the top with their location (RPT1 and RPT2).

Hope this helps

Brad