Call a report w/ sub report in vb6 with multiple filter
Hi, guys need help here. I have 2 reports one is the container and the other one is the subreport, both has 2 different queries but they have similar values which is (outletname,productcode,brand,date(from and to)) and i have a form in vb (filter form). and i dont know how to call it thru code, i dont have problems calling a single report all the filters i'm throwing are being fetched and the data are displayed correctly. but with subreport it doesn't work. i encountered error "log-on failed."
here's my code in the filter form :
Code:
Dim r_SQL As String
r_SQL = ""
If Text2.Text <> "" Then
If Text1.Text <> "" Then 'both dates are selected date
If Not IsDate(Text1.Text) And Not IsDate(Text2.Text) Then
MsgBox "Invalid Date.", vbInformation
Exit Sub
End If
Dim x3 As String, x4 As String
x3 = Text1.Text
x4 = Text2.Text
r_SQL = r_SQL & " " & r_sdate & " >= #" & x3 & "#" & " and " & r_sdate & " <= #" & x4 & "#"
' MsgBox r_SQL
End If
Else
If Text1.Text <> "" Then 'both dates are selected date
If Not IsDate(Text1.Text) Then
MsgBox "Invalid Date.", vbInformation
Exit Sub
End If
Dim x1 As String, x2 As String
x1 = Text1.Text
x2 = Text1.Text
r_SQL = r_SQL & " " & r_sdate & " >= #" & x1 & "#" & " and " & r_sdate & " <= #" & x2 & "#"
End If
End If
r_category_brand = Trim(Mid(Me.CBOLOB.Text, 1, 5))
r_product_name = Trim(Mid(Me.cboproductname.Text, 1, 10))
If getoutletid <> 0 Then
If r_SQL <> "" Then
r_SQL = r_SQL & " and outlet_id=" & getoutletid
Else
r_SQL = r_SQL & " outlet_id=" & getoutletid
End If
End If
If r_category_brand <> 0 Then
If r_SQL <> "" Then
r_SQL = r_SQL & " and brand_id=" & r_category_brand
Else
r_SQL = r_SQL & " brand_id=" & r_category_brand
End If
End If
If r_product_name <> 0 Then
If r_SQL <> "" Then
r_SQL = r_SQL & " and product_id=" & r_product_name
Else
r_SQL = r_SQL & " product_id=" & r_product_name
End If
End If
If r_SQL <> "" Then
If Not r_swhere Then
r_SQL = " where" & r_SQL
Else
r_SQL = " and" & r_SQL
End If
End If
MsgBox r_SQL
Select Case r_mod
Case 0:
MsgBox "This report is not yet implemented.", vbInformation
Case 1:
Call rpt_viewer("offtake_table", _
"select * from q_view_offtake_monthly" & r_SQL, _
"Report: Modern Trade Offtake Table")
Case 2:
Call rpt_viewer("offtake_weekly_persku", _
"select * from q_view_offtake_monthly" & r_SQL, _
"Report: Modern Trade Offtake")
Case 3:
Call rpt_viewer("offtake_monthly_persku", _
"select * from q_view_offtake_monthly" & r_SQL, _
"Report: Modern Trade Offtake")
Case 4:
Call rpt_viewer("Report2_test", _
"select * from q_view_offtake_monthly" & r_SQL, _
"Report: Modern Trade Offtake")
End Select
End If
--------------------
and here's my code for viewing the report:
Code:
Private crApp As CRAXDRT.Application
Private report As CRAXDRT.report
Private Sub Form_Load()
'Main
'Me.Top = (frmMain.ScaleHeight - Me.ScaleHeight) / 2
'Me.Left = (frmMain.ScaleWidth - Me.ScaleWidth) / 2
'On Error GoTo err_handler
CRViewer.DisplayBorder = False 'MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False 'THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False 'REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False 'ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
CRViewer.DisplayGroupTree = False
CRViewer.EnableGroupTree = False
CRViewer.EnableAnimationCtrl = False
'query
Set rs = Nothing
Set rs = New ADODB.recordset
rs.Open rpt_QUERY, conn, adOpenStatic, adLockReadOnly 'where drx_refno='" & frmSOreport.txtWDR & "'", conn, adOpenStatic, adLockReadOnly
' MsgBox r_SQL
Set crApp = New CRAXDRT.Application
Set report = crApp.OpenReport(App.Path & "\reports\" & rpt_FILE & ".rpt") '& reportFile)
report.DiscardSavedData ' this when if you want to modify the data on the report
report.Database.SetDataSource rs ' change the recordsource
report.EnableParameterPrompting = True
CRViewer.ReportSource = report
CRViewer.ViewReport
CRViewer.Zoom 100 ' zoom set to 100% u can modify by zoom 94% or ...%
Do While CRViewer.IsBusy
Screen.MousePointer = vbHourglass
DoEvents
Loop
Screen.MousePointer = vbDefault
' 'export to PDF
' Set crxExportOptions = report.ExportOptions
' crxExportOptions.DestinationType = crEDTDiskFile
' crxExportOptions.DiskFileName = App.Path & "\TMP_PDF_FLE\" & arrVARVALUE(0, x) 'MyReport.pdf"
' crxExportOptions.FormatType = crEFTPortableDocFormat
' crxExportOptions.PDFFirstPageNumber = 1
' crxExportOptions.PDFLastPageNumber = 1
' crxExportOptions.PDFExportAllPages = True
' report.Export False
'rs.Close
Set rs = Nothing
Set crApp = Nothing
Set report = Nothing
Exit Sub
err_handler:
MsgBox Err.num & "-" & Err.Description
Screen.MousePointer = vbDefault
Set rs = Nothing
Set crApp = Nothing
Set report = Nothing
End Sub
Private Sub Form_Resize() 'MAKE SURE REPORT FILLS FORM
CRViewer.Top = 0 'WHEN FORM IS RESIZED
CRViewer.Left = 0
CRViewer.Height = ScaleHeight
CRViewer.Width = ScaleWidth
End Sub
these problem took me 3 weeks already and until now i haven't found solutions to it. thanks in advance... hope someone help me thanks again
Re: Call a report w/ sub report in vb6 with multiple filter
Re: Call a report w/ sub report in vb6 with multiple filter
