[RESOLVED] Crystal Report 11 subreport code....

[intellogo]

Hi crystalians,

With reference to the Crystal Report 11 product, I have created 2 sub-reports from 2 unrelated tables: A and B. Then inserted them onto one blank master report in the Report Header section.

I need to pass SQL statements from within Visual Basic 6.0 to the sub-report so it will correctly query the records. I know how to do this with normal reports...but not with subreports. I usually use the ".SQLQueryString" property to process SQL statements in VB code.

Now, with reference to Visual Basic 6.0, can anyone enlighten me on how to pass the sql statements to query the records from VB6 onto a subreport, correctly.

Awaiting urgent reply....

[intellogo]

Anyone...yooohooooo ...

[intellogo]

Anyone for heaven sake's pull me out of my last pothole within this Crystal Report subreport issue....and VB6.

Awaiting reply very urgently....

[brucevde]

A SubReport is really no different than a "main report", so you can use the SQLQueryString as well. All you need is a reference to the subreport.

Dim oMainReport as Report
Dim oSubReport as Report

'code to open main report

Set oSubReport = oMainReport.OpenSubReport(Name of SubReport)
oSubReport.SQLQueryString = "..."

[intellogo]

Hi Brucevde buddy,
Thanks, for once again sharing ur umbrella with me.

Progress is there but slightly, and now i am able to atleast display the subreports...but the sql statement for both the subreports are not being processed, instead all the records of individual subreports are being dumbly retrieved.

This is what i have done in code view. Please correct me and let me know which part am I falterring in. I have also attached images. Please see the images while ur reading my explanation below, so that u'll be able to link it and correct me wherever i am falterring.
=======================================================

What have i missed up above....? Where am i going wrong ?...why is it dumbly showing all records of both the subreports...??

Awaiting ur special response....

[brucevde]

Part 1 is fine. The Main report is blank so there is nothing to link, but that is not a requirement. You only need to use linking when you want to pass parameters to a subreport from the main report.

Part 2. I never use the "Designers", preferring the method of keeping reports out of the VB project.

It is hard to tell but it looks like you are modifying the individual subreport files rather than the subreports embedded in the Main report. Subreports in a Main Report are basically copies of the original. Modifying the original may not neccessarily affect the subreport embedded in the main report.

You will need to post all the relevant Form_Load code in order for me to help further.

[intellogo]

A SubReport is really no different than a "main report", so you can use the SQLQueryString as well. All you need is a reference to the subreport.

Dim oMainReport as Report
Dim oSubReport as Report

'code to open main report

Set oSubReport = oMainReport.OpenSubReport(Name of SubReport)
oSubReport.SQLQueryString = "..."

Hello, Brucevde...thanks a lot for ur keybreaking help thoughout, since the time i joined this forum...

Ur above qoute's code is working fine the .RPT way. I am trying my best to convert it to the .DSR way ...but just in case if i am not able to, i will stick with ur way of methods/props for situations for subreports.

Only one issue, its giving me the Database connection manager again for the enterring the correct password...becoz my .MDB file is protected via a password. Ur code is working fine for loading reports from a secured .mdb db.....but not for the case of teh subreports being accessed from a secured db....

I am sending u my code part of that particular section, please tell me where should i enter the password/".Connection.Item" property related code for the subreports thats placed on the master one.
I tried various loopholes but am not able to get pass it...

See below...
========================================================
Set rpt = appn.OpenReport(App.Path & "\Market_Competetor.rpt")
Set LftSubrpt = rpt.OpenSubreport("SubrptLFT_Market_Competetor.rpt")
Set RgtSubrpt = rpt.OpenSubreport("SubrptRGT_Market_Competetor.rpt")

Set tbls = rpt.Database.Tables
For Each tbl In tbls
tbl.Location = App.Path & "\MMMM.mdb"
tbl.ConnectionProperties.Item("Database Password") = "PPPP"
Next
========================================================
Shouldn't this code specify the password: "PPPP" for each table in the report and subreports...?? but its just not happenning when subreports are the case.

Awaiting status urgently....

[brucevde]

Shouldn't this code specify the password: "PPPP" for each table in the report and subreports...??

No, run the For Each statement on the SubReport database tables as well.

[intellogo]

hello Brucevde,

As u stated before, in order for me to run the For Each statement on the subReport database tables also. Do i also have to make 2 new objects of the .DatabaseTable and .DatabaseTables reference libraries which will be usable by the 2 subreports ??

So that, in the case of the subreports, the Set statment will point to the newer 2 table objects just created exclusively for the subreports and even the 2nd and 3rd For Each statements will follow suit. Is this what i should do.

Awaiting ur expert replies....

[brucevde]

Set rpt = appn.OpenReport(App.Path & "\Market_Competetor.rpt")
Set LftSubrpt = rpt.OpenSubreport("SubrptLFT_Market_Competetor.rpt")
Set RgtSubrpt = rpt.OpenSubreport("SubrptRGT_Market_Competetor.rpt")

Set tbls = rpt.Database.Tables
For Each tbl In tbls
  tbl.Location = App.Path & "\MMMM.mdb"
  tbl.ConnectionProperties.Item("Database Password") = "PPPP"
Next

Set tbls = LftSubrpt.Database.Tables
For Each tbl In tbls
  tbl.Location = App.Path & "\MMMM.mdb"
  tbl.ConnectionProperties.Item("Database Password") = "PPPP"
Next

Set tbls = RgtSubrpt.Database.Tables
For Each tbl In tbls
  tbl.Location = App.Path & "\MMMM.mdb"
  tbl.ConnectionProperties.Item("Database Password") = "PPPP"
Next

[intellogo]

Thanks a lot Brucevde for ur expert help.

I was fiddling with the code earlier but was just not getting it right.
Then after u replied i found my mistake.

You have stated the 3 "SET" statements before each "FOR EACH" block.
What i did was stated the 3 "SET" statementsbefore the first "FOR EACH" block....which was my mistake.

Thanks once again to help me when i really needed it.