ed_creed

I know this has been answered many times on this form, but I can't seem to find an example like mine...and maybe that's because I'm going about it all wrong...you guys tell me.

The crystal report that I'm displaying is a stand alone crystal report based on one summary table (hitting oracle through odbc dsn). I want to replace the table that the report is based on with a sql statement (onclick of my report button) and then have the report viewer refresh the report link so that the most current version of the data is displayed in the viewer.

I can get the report to refresh at run time when clicking on the little yellow lightning bolt on the viewer...however, I would like for the viewer to refresh the report on the initial load of the report.

I've tried crviewer1.refresh and I get an error message that says "method'refresh' of object 'Icrystalreportviewer3' failed? However when I hit debug and then just hit run again from design mode, it does refresh the report.



thanks here's my code

Dim NETADCREATEQRY As String
Dim NETADDROPQRY As String
Dim crysApp As New CRAXDRT.Application
Dim selectedreport As String
Dim myRpt As CRAXDRT.Report

NETADDROPQRY = "DROP TABLE NETWORK_ADEQUACY_RPT;"
NETADCREATEQRY = "CREATE TABLE NETWORK_ADEQUACY_RPT AS SELECT DESCRIPTION, SUM((CASE SUBSTR(C.CCHP_RESTRICTIONS,1,1) WHEN '1' THEN 1 ELSE 0 END)) OPEN_PCPS, SUM((CASE SUBSTR(C.CCHP_RESTRICTIONS,1,1) WHEN '2' THEN 1 WHEN '3' THEN 1 ELSE 0 END)) CLOSED_PCPS, (CASE C.IPA_GROUP_NAME WHEN 'CCPN' THEN 'YES' Else 'NO' END) CCPN, COUNT(D.PROV_NO) TOTAL, SUM((CASE SUBSTR(C.CCHP_RESTRICTIONS,1,1) WHEN '1' THEN 1 ELSE 0 END))/COUNT(D.PROV_NO) PERCENT_OPEN FROM PHYDEMOGRAPHIC D, PHYSPECIALTY A, PHYADDRESS B, PHYLOB C, COUNTIES E WHERE D.PROV_NO = A.PROVNO (+) AND D.PROV_NO = B.PROVNO (+) AND D.PROV_NO = C.PROVNO (+) AND B.CTY_CODE = E.CODE (+) AND A.PRVTYP = 'PCP' GROUP BY (CASE C.IPA_GROUP_NAME WHEN 'CCPN' THEN 'YES' Else 'NO' END), E.Description ORDER BY (CASE C.IPA_GROUP_NAME WHEN 'CCPN' THEN 'YES' Else 'NO' END) DESC;"

' DROP AND RECREATE THE NETWORK ADEQUACY REPORT TABLE
With cmd
Set .ActiveConnection = con
.CommandType = adCmdText
.CommandText = NETADDROPQRY
End With

With rs
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.Open cmd
End With
With cmd
Set .ActiveConnection = con
.CommandType = adCmdText
.CommandText = NETADCREATEQRY
End With

With rs
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.Open cmd
End With

selectedreport = "J:\Reports\Gen30\Completed_Reports\REPORTING DIRECTORY\NETADEQ.rpt"
Set myRpt = crysApp.OpenReport(selectedreport)
Call myRpt.Database.Tables(1).SetLogOnInfo (mngp, , "mylogon", "mypwd")

With RPTS
.Show
Screen.MousePointer = vbHourglass
.CRViewer1.ReportSource = myRpt
.CRViewer1.ViewReport
.CRViewer1.Zoom 100
.CRViewer1.Refresh
Screen.MousePointer = vbDefault
End With

MKoslof

Where are you posting this code, I typically put my Crystal Code in the form load event (the form with the CRViewer on it) this typically will enforce any recordset changes.

I see where you are updating your connection and recordset...and I see where you are using the .setlogoninfo method to verify your report connection. However, where do you bind the recordset to the report. Such as Report.database.setdatasource rs (the currently open and active recordset).

Another trick I typically use is, when the code first initializes, I set my report object = nothing which clears any potential memory. Then with the code I rebuild the report object with the code.


And where do you define "With RPTS"..I see a With statement but I do not see where the "RPTS" object instantiated.

ed_creed

Quote:

---

Originally Posted by MKoslof
Where are you posting this code, I typically put my Crystal Code in the form load event (the form with the CRViewer on it) this typically will enforce any recordset changes.

I see where you are updating your connection and recordset...and I see where you are using the .setlogoninfo method to verify your report connection. However, where do you bind the recordset to the report. Such as Report.database.setdatasource rs (the currently open and active recordset).

Another trick I typically use is, when the code first initializes, I set my report object = nothing which clears any potential memory. Then with the code I rebuild the report object with the code.

Code:

Public Sub form_load()

'all my dims

'the first set
 Set report = Nothing

'continue with the rest of the code to populate and display

And where do you define "With RPTS"..I see a With statement but I do not see where the "RPTS" object instantiated.

---

MKoslof, thanks for your help. I only have about 5 reports for this app and I thought that I would put command buttons on the form that has the viewer on it, then run the code with the click property of the command buttons.

As for binding the recordset to the report, I assumed that since the report is already bound to the table through odbc, that I could just replace the table with the sql statement and then refresh the viewer...and that works if I manually click on the
refresh button at run time, but I want it to refresh before it shows the report. I tried setting my report to nothing, but it gave "invalid use of object" and it highlighted on the word nothing.

RPTS is the form that the viewer is on.

thanks again.
Ed

bvmantos

Try this one
=======================================
Private Sub Print_Click()
Dim report As CRAXDRT.report
Dim app As CRAXDRT.Application

Me.MousePointer = vbHourglass

Set report = New report
Set app = New Application
Set report = app.OpenReport("PAth&FileName")
'Pass your SQLQuery here or SelectionFormula like
report.SQLQueryString = "Select * from v_pr_detail where pr_number='PR000029'"
'OR
report.RecordSelectionFormula = "{table/query.fieldname}= '" & Trim (Combo1.Text) & "'" OR

cr.ReportSource = report
cr.ViewReport
Me.MousePointer = vbDefault
End Sub

========================================

Hope this helps