Solved

Calling a crystal report with a parameter field

Posted on 2004-09-28
20
3,165 Views
Last Modified: 2012-06-27
I am using VB 6 to create an application that opens a crystal report that has a parameter field called OrderNum.  I have two forms, the starting form has a text field and two command buttons.  I place the value of the text field in a public variable so that I can pass it to the crystal reports form.  Here is the code for the starting form:

Private Sub cmdClose_Click()
 End
End Sub

Private Sub cmdView_Click()

OrderN = txtOrderNum.Text
frmReportView.Show vbModal

End Sub

Private Sub Form_Unload(Cancel As Integer)

Set CrxSubreport = Nothing
Set CrxReport = Nothing
Set crxApplication = Nothing

End Sub

I based this heavily from the example project they offer from the Crystal Reports website, here is the second form's code:

Private Sub cmdExit_Click()
frmReportView.Hide
Form1.Show
End Sub

Private Sub Form_Load()

Dim OrderNM As Integer

OrderNM = OrderN

Screen.MousePointer = vbHourglass

Set CrxReport = crxApplication.OpenReport(App.Path & "\Order-Detail.rpt")

CrxReport.DiscardSavedData

CrxReport.ParameterFields.GetItemByName("@OrderNum").AddCurrentValue (OrderN)

CRViewer1.ReportSource = CrxReport
CRViewer1.ViewReport

frmReportView.WindowState = vbMaximized

CRViewer1.Zoom 100
Screen.MousePointer = vbDefault

End Sub

Now, no matter what I seem to do to get it to work I'm getting errors on this line of code:

CrxReport.ParameterFields.GetItemByName("@OrderNum").AddCurrentValue (OrderN)

VB highlights .GetItemByName and says method or data member not found.  Any help would be greatly appreciated.  I'm using CR 8.0

Timmy
0
Comment
Question by:Rikol
20 Comments
 
LVL 1

Author Comment

by:Rikol
ID: 12168796
Sorry, take the @ symbol out.  I was trying something out.

CrxReport.ParameterFields.GetItemByName("OrderNum").AddCurrentValue (OrderN)

Timmy
0
 
LVL 10

Expert Comment

by:anv
ID: 12168877
use CrxReport.ReportSelectionFormula = "OrderNum=" & OrderN instead of using Parameters..
then

CRViewer1.ReportSource = CrxReport
CRViewer1.ViewReport
0
 
LVL 1

Author Comment

by:Rikol
ID: 12168923
Private Sub cmdExit_Click()
frmReportView.Hide
Form1.Show
End Sub

Private Sub Form_Load()

Dim OrderNM As Integer
Dim APath As String

OrderNM = OrderN

Screen.MousePointer = vbHourglass

Set CrxReport = crxApplication.OpenReport(App.Path & "\Order-Detail.rpt")

CrxReport.DiscardSavedData

'CrxReport.ParameterField.GetItemByName("OrderNum").AddCurrentValue (OrderNM)

CrxReport.ReportSelectionFormula = "OrderNum=" & OrderN

CRViewer1.ReportSource = CrxReport
CRViewer1.ViewReport

frmReportView.WindowState = vbMaximized

CRViewer1.Zoom 100
Screen.MousePointer = vbDefault

End Sub

With the code like that I get an error on the line I added.

My module looks like this:

Option Explicit

Global crxApplication As New CRAXDRT.Application
Global CrxReport As CRAXDRT.Report
Public OrderN As Integer
0
 
LVL 1

Author Comment

by:Rikol
ID: 12168939
Error is Object does not support this property or method.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12170450
Try
CrxReport.RecordSelectionFormula = "{Table.OrderNum}=" & OrderN

Change {Table.OrderNum} to the appropriate table and field name.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12170466
If you wish to go back to the parameter method

CrxReport.ParameterFields.GetItemByName("{?OrderNum}").AddCurrentValue (OrderN)

The name includes the { } and the ?

mlmcc
0
 
LVL 1

Author Comment

by:Rikol
ID: 12170785
Tried both.  Same error.  Hmmm....do you think it has anything to do with the fact that I'm using an ODBC driver to pull the data?

Timmy
0
 
LVL 1

Author Comment

by:Rikol
ID: 12171242
Hmmm..new error.  Took out the ? and now getting type mismatch.  Will keep trying.

Timmy
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12174770
Do you have the developer edition of Crystal 8.0?

Have you installed the latest hotfix?

mlmcc
0
 
LVL 1

Author Comment

by:Rikol
ID: 12178726
Pretty sure it is not developers edition, it came bundled with a DB software that we've got.  I'll get the latest hotfix though.


Timmy
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Rikol
ID: 12180235
NM.  Can't get any hotfixes.  Since it is an OEM version it's not compatable with the hotfixes from Seagate.  I'll be downloading an updated version of the software this afternoon that will bump my CR up to 8.5.  So maybe that will clear this up.

Timmy
0
 
LVL 1

Author Comment

by:Rikol
ID: 12180291
Meant to update the points.  :)
0
 
LVL 1

Author Comment

by:Rikol
ID: 12192890
Ok, CR now at 8.5.  Now getting Invalid name and it points to this line

CrxReport.ParameterFields.GetItemByName("{?OrderNum}").AddCurrentValue (OrderNM)

I've tried it without the ? and without the {} marks.  Still getting the same error.

Timmy
0
 
LVL 1

Author Comment

by:Rikol
ID: 12193246
After fiddling with it and commenting that line out and adding this one :

CrxReport.ParameterFields(1).Value = OrderNM

I now get an object required error.  But I know that I have that parameter field set up.  I've checked the report 3 times now.

Timmy
0
 

Expert Comment

by:fiSBoN3
ID: 12196176
Hi... Rikol... maybe this would help.

This is your code from your first message above.....

Private Sub Form_Load()

Dim OrderNM As Integer

OrderNM = OrderN

Screen.MousePointer = vbHourglass

Set CrxReport = crxApplication.OpenReport(App.Path & "\Order-Detail.rpt")

CrxReport.DiscardSavedData
'==========================================================
'   This was your old code
'   CrxReport.ParameterFields.GetItemByName("@OrderNum").AddCurrentValue (OrderN)
'==========================================================
' change it with the following....

crxReport.ParameterFields(1).AddCurrentValue (OrderN)

' The number represents the parameter field's order in your crystal report.
' If there are many parameter fields in your report, just place its order accordingly...
' e.g. crxReport.ParameterFields(1) , crxReport.ParameterFields(2),. ... etc.

CRViewer1.ReportSource = CrxReport
CRViewer1.ViewReport

frmReportView.WindowState = vbMaximized

CRViewer1.Zoom 100
Screen.MousePointer = vbDefault

End Sub
0
 
LVL 1

Author Comment

by:Rikol
ID: 12199142
No errors when I switch it to that, but coming up with a blank report again.

Keeps doing that, I've got a feeling that it's not connecting the ODBC to the server.  That's the only way I know to put it.

Timmy
0
 
LVL 1

Author Comment

by:Rikol
ID: 12199174
This is my code as it now stands.  I have commented out various stuff that I've been trying to get it to work with.  I'm still working on getting the log on server line correct.

Hehe.  For security purposes I've replaced my username with Myusr and my password with Mypswrd.  Just a silly little precaution.  :)

Private Sub Form_Load()

Dim OrderNM As String
Dim APath As String
Dim FieldName As String
Dim CheckData As String

OrderNM = Str(OrderN)

APath = "C:\Custom Reports\VB Stuff\Order-Detail.rpt"

Screen.MousePointer = vbHourglass

Set CrxReport = crxApplication.OpenReport(APath)

CrxReport.DiscardSavedData

CrxReport.EnableParameterPrompting = False

FieldName = CrxReport.ParameterFields(1).Name

'crxReport.Database.LogOnServer "invenodbc.dll", "PROGRESS", "INVEN", "myuser", "mypswrd"

'CrxReport.ParameterFields.GetItemByName("OrderNum").AddCurrentValue (OrderNM)

'CrxReport.ParameterFields(1).Value = OrderNM

CrxReport.ParameterFields(1).AddCurrentValue (OrderNM)

CheckData = CrxReport.ParameterFields(1).Value


CRViewer1.ReportSource = CrxReport
CRViewer1.ViewReport

frmReportView.WindowState = vbMaximized

CRViewer1.Zoom 100
Screen.MousePointer = vbDefault

End Sub
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 400 total points
ID: 12205941
Look at this tutorial.  In particular page 10.

http://support.businessobjects.com/communityCS/TechnicalPapers/rdc_tutorial.pdf

mlmcc
0
 
LVL 1

Author Comment

by:Rikol
ID: 12221019
I've tried that out and still no go.  Just using the parameter prompt from CR to get the data.  Will try again another time to pass data to parameter fields from VB.  Thanks for all the help though.  :)

Timmy
0
 

Expert Comment

by:VX70
ID: 12472297
Try this ...



Public Function CrystalExport(sDocPath As String, sFilePath As String, lUTBDAT As Long, sPNR As String, sEmailAdd As String, sBatch As String) As Boolean
Dim lCount As Long
Dim lMax As Long
Dim sSelectString As String
Dim c_Mail As cMail
Dim fs As FileSystemObject
Dim strFile As String

On Error GoTo Err

'add the reference to Crystal Report Viewer Control and the Crystal report 8 ActiveX Designer Run Time Lib


If IsObject(crRpt) Then
   Set crRpt = Nothing
End If

Set crApp = New CRAXDRT.Application
Set crRpt = crApp.OpenReport(sDocPath, 1)

    With crRpt
        ' Logon do the Log on
        .Database.LogOnServer "pdsodbc.dll", "RESPONS", "RESPONS", "SYSADM", "sysadm"
        lCount = 1
        lMax = .Database.Tables.Count
        'For some stupid reason, you have to log on all the tables
        Do While lCount <= lMax
             .Database.Tables(lCount).SetLogOnInfo "RESPONS", "RESPONS", "SYSADM", "SYSADM"
             lCount = lCount + 1
        Loop
        'Get rid of saved data
        .DiscardSavedData
    End With

    With crRpt
        'Do the parameters
        'We will only check for these 2 parameters
        For lCount = 1 To crRpt.ParameterFields.Count

            If UCase(.ParameterFields(lCount).Name) = "{?DATE}" Then
                .ParameterFields(lCount).EnableNullValue = True
                .ParameterFields(lCount).AddCurrentValue (CLng(lUTBDAT))
            ElseIf UCase(.ParameterFields(lCount).Name) = "{?EMP NO}" Then
                .ParameterFields(lCount).EnableNullValue = True
                .ParameterFields(lCount).AddCurrentValue sPNR
            Else

            End If

        Next lCount
           
        'Do the Export to HTML
        .ExportOptions.FormatType = crEFTHTML40
        .ExportOptions.DestinationType = crEDTDiskFile
        .ExportOptions.DiskFileName = "c:\test.htm"
        .ExportOptions.HTMLFileName = "c:\test.htm"
        .Export False
       
    End With

    CrystalExport = True

Exit Function
Err:
    MsgBox Err.Description, vbInformation
    CrystalExport = False
End Function


Hope it works

VX
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now