Call a report w/ sub report in vb6 with multiple filter

[ryoma_ic3]

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 :

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:

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

[Hack]

Moved to Reporting

[ryoma_ic3]

....