Two subreports

[lotwal01]

I use CR 8.5, VB 6 and SQL server. I have a report with a subreport in it.I use TTX files in the report and use VB to fill and send the recordsets to the report. I have two recordsets.

it works just fine but the problem is that I have to add one more subreport and I don't know how to change the code because it was an example code I found on the Internet. How can I change my existing code so it will work with a third recordset too? I have tried to put the two "subreport recordsets" together but that doesn't work the way I want it to. Here is my code, is there someone who can help me??

Dim objConn As New ADODB.Connection
Dim CRXApplication As New CRAXDRT.Application

Private Sub Command1_Click()
Dim CRXDatabase As CRAXDRT.Database
Dim CRXDatabaseTables As CRAXDRT.DatabaseTables
Dim CRXDatabaseTable As CRAXDRT.DatabaseTable
Dim CRXSections As CRAXDRT.Sections
Dim CRXSection As CRAXDRT.Section
Dim CRXReportObjs As CRAXDRT.ReportObjects
Dim CRXSubReportObj As CRAXDRT.SubreportObject
Dim CRXSubreport As CRAXDRT.Report
Dim CRXReport As CRAXDRT.Report

Dim intNo As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim x As Integer
Dim y As Integer
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset


intNo = 9

'Open the report.
Set CRXReport = CRXApplication.OpenReport("\report.rpt", 1)

strSQL = "SELECT data FROM table WHERE something = '" & intNo & "'"

strSQL2 = "SELECT data2 FROM table2 WHERE something = '" & intNo & "'"

strConnString = "Provider=SQLOLEDB;Data Source=***;Initial Catalog=***; User Id=***;Password=***"
objConn.Open strConnString

Set rs = objConn.Execute(strSQL)
Set rs2 = objConn.Execute(strSQL2)

'Set the database, database tables and database table for the main report by using the SetDataSource method.
Set CRXDatabase = CRXReport.Database
Set CrXDatabaseTables = CRXDatabase.Tables
Set CrXDatabaseTable = CrXDatabaseTables.Item(1)
CrXDatabaseTable.SetDataSource rs, 3
'Loop through each section and reportobject. When a subreport object is found, set the subreport object to a reportobject.
Set CrXSections = CRXReport.Sections
For x = 1 To CrXSections.Count
Set CrXSection = CrXSections.Item(x)
Set CrXReportObjs = CrXSection.ReportObjects

For y = 1 To CrXReportObjs.Count
If CrXReportObjs.Item.Kind = crSubreportObject Then
Set CRXSubReportObj = CrXReportObjs.Item
Set CrXSubreport = CRXSubReportObj.OpenSubreport
'Set the database, database tables and database table for the subreport by using the SetDataSource method
Set CRXDatabase = CrXSubreport.Database
Set CrXDatabaseTables = CRXDatabase.Tables
Set CrXDatabaseTable = CrXDatabaseTables.Item(1)
CrXDatabaseTable.SetDataSource rs2, 3
End If
Next
Next

CRXReport.ExportOptions.DiskFileName = "report.pdf"
CRXReport.ExportOptions.DestinationType = crEDTDiskFile
CRXReport.ExportOptions.FormatType = crEFTPortableDocFormat
CRXReport.Export False

Set rs = Nothing
Set rs2 = Nothing

Set objConn = Nothing

End Sub