Main and SubReport based upon parametized stored procedure

[mamartin]

I have a slight problem with my CR8.0 report. I am using VB 6.0 and the RDC against a SQL Server 7.0 DB. I have a main report and a subreport, and both use the same stored procedure. The sp has 4 parameters that are passed via VB code. So far, so good. The user submission form allows the user to execute the report for a specific item (this is an inventory management database/application), a specific rollup description (a group of like items) or for all items. The Main report returns detail information and the subreport is only used if the user selects by rollup or all items. The subreport will then produce summary totals. When I run the report for a specific rollup, no problem. It takes maybe 10 seconds to run. But, when I select all items, the report goes off to never-neverland and will not complete. Is there something wrong with the main/subreport linking that I need to set in the VB code? I am including the code from the CR form down below. In the Main report, the subreport is linked back via the roll_desc field that is common to both Main and Sub.

Code from report form:

Dim Report As New cr_resc_summary
Dim crxTables As CRAXDRT.DatabaseTables
Dim crxTable As CRAXDRT.DatabaseTable
Dim crxSections As CRAXDRT.Sections
Dim crxSection As CRAXDRT.Section
Dim crxSubreportObj As CRAXDRT.SubreportObject
Dim crxSubreport As CRAXDRT.Report
Dim crxReportObjects As CRAXDRT.ReportObjects
Dim ReportObject As Object


Private Sub Form_Load()

Dim strTempHeader As String
Dim strStkNbr As String
Dim strRollDesc As String
Dim strUIC As String
Dim strCRDate As String

Screen.MousePointer = vbHourglass

Report.DiscardSavedData

With frm_mnu_summ_rpt

If .opt_nsn = True Then
strStkNbr = Trim(.cmb_stknbr.Text)
strTempHeader = "Stock Number: " + strStkNbr
Report.FormulaFields(1).Text = "'StkNbr'"
Report.ParameterFields(1).AddCurrentValue (strStkNbr)
Report.ParameterFields(2).AddCurrentValue (strNull)
ElseIf .opt_roll = True Then
strRollDesc = Trim(.cmb_rollup.Text)
strTempHeader = "Rollup Description: " + strRollDesc
Report.FormulaFields(1).Text = "'Rollup'"
Report.ParameterFields(1).AddCurrentValue (strNull)
Report.ParameterFields(2).AddCurrentValue (strRollDesc)
Else
strTempHeader = "All Items"
Report.FormulaFields(1).Text = "'All'"
Report.ParameterFields(1).AddCurrentValue (strNull)
Report.ParameterFields(2).AddCurrentValue (strNull)
End If


If .opt_afh = True Then
strUIC = "AFH"
strTempHeader = "AFH Listings for: " + strTempHeader
ElseIf .opt_uph = True Then
strUIC = "UPH"
strTempHeader = "UPH Listings for: " + strTempHeader
Else
strUIC = "%"
strTempHeader = "All Listings for: " + strTempHeader
End If

End With

Report.ParameterFields(3).AddCurrentValue (strNull)

Report.ParameterFields(4).AddCurrentValue (strUIC)

Report.RptSubHdr.SetText (strTempHeader)

'SubReport Section
Set crxSections = Report.Sections

For Each crxSection In crxSections
Set crxReportObjects = crxSection.ReportObjects
For Each ReportObject In crxReportObjects

If ReportObject.Kind = crSubreportObject Then
Set crxSubreportObj = ReportObject
Set crxSubreport = crxSubreportObj.OpenSubreport
crxSubreport.DiscardSavedData

crxSubreport.ParameterFields(1).AddCurrentValue (strNull)

If frm_mnu_summ_rpt.opt_all = True Then
crxSubreport.ParameterFields(2).AddCurrentValue (strNull)
Else
crxSubreport.ParameterFields(2).AddCurrentValue (strRollDesc)
End If

crxSubreport.ParameterFields(3).AddCurrentValue (strNull)

crxSubreport.ParameterFields(4).AddCurrentValue (strUIC)
End If
Next ReportObject
Next crxSection

With frm_mnu_MAIN
strCRDate = Format(.lbl_cr_date, "dd") + " " + Format(.lbl_cr_date, "mmm") + " " + Format(.lbl_cr_date, "yyyy")
End With
Report.txtCRDate.SetText (strCRDate)


CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Private Sub Form_Resize()
CRViewer1.Top = 0
CRViewer1.Left = 0
CRViewer1.Height = ScaleHeight
CRViewer1.Width = ScaleWidth

End Sub


If anybody can offer some insight, I would be most appreciative.

Michael A. Martin