How to access a field-name on a sub-report from a form?

[Amit]

Hi,

I'm unable to figure out how to access a query field on a
sub-report from a form, and have checked the MVPS.org and
the Access Help section without any success.

I have a report based on a query. The query has ProgramID,
ProgramName, OrgID and OrgName.

A Program has many-to-many relationship with Org.

To run the report on Orgs for a particular Program, I open
the report from a form after selecting the Program name
from a combo-box control. The code is:
=========================================================
stDocName = "rptOrg"
stWhereCondition = "[qselProgramOrg].[ProgramID] = " &
[Forms]![frmReportType]![cboProgramID]
DoCmd.OpenReport stDocName, acPreview, , stWhereCondition
==========================================================

This works fine.

But, I also have Individual partners related to a Program,
the same way as an Org. So, I would like to display both
Orgs and Individuals for a Program by opening a single
report and including the rptOrg and rptInd as sub-reports.

On the form, I have an option group to select:
-Org
-Individual
-Both
and based on the selection, make one or both of the sub-
reports visible in the onOpen event of the report.

This works fine too.

The problem I'm having is to filter the sub-reports based
on the combo-box value selected in the form for ProgramID.
I need some help with the "where" condition statement used
in the form to open the report (rptAll).

Here's what I tried:
=======================================================
stDocName = "rptAll"
stWhereCondition = "[rptAll]![srptProgramOrg]!
[qselProgramOrg].[ProgramID] = " & [Forms]![frmReportType]!
[cboProgramID]
=======================================================
And, that didn't work. "srptProgramOrg" is the sub-report
control on rptAll for the report "rptOrg". "rptAll"
has "rptOrg" and "rptInd" as embedded sub-reports.

I'll appreciate any help on this, as I've been staring at
the code for a while, tried different combinations and
come up with nothing. I'm not even sure if it is possible
to refer to the query-field this way.

Thanks much!!

-Amit

 

[Guest]

-----Original Message-----
Hi,

I'm unable to figure out how to access a query field on a
sub-report from a form, and have checked the MVPS.org and
the Access Help section without any success.

I have a report based on a query. The query has ProgramID,
ProgramName, OrgID and OrgName.

A Program has many-to-many relationship with Org.

To run the report on Orgs for a particular Program, I open
the report from a form after selecting the Program name
from a combo-box control. The code is:
=========================================================
stDocName = "rptOrg"
stWhereCondition = "[qselProgramOrg].[ProgramID] = " &
[Forms]![frmReportType]![cboProgramID]
DoCmd.OpenReport stDocName, acPreview, , stWhereCondition

=========================================================

=

This works fine.

But, I also have Individual partners related to a Program,
the same way as an Org. So, I would like to display both
Orgs and Individuals for a Program by opening a single
report and including the rptOrg and rptInd as sub- reports.

On the form, I have an option group to select:
-Org
-Individual
-Both
and based on the selection, make one or both of the sub-
reports visible in the onOpen event of the report.

This works fine too.

The problem I'm having is to filter the sub-reports based
on the combo-box value selected in the form for ProgramID.
I need some help with the "where" condition statement used
in the form to open the report (rptAll).

Here's what I tried:
=======================================================
stDocName = "rptAll"
stWhereCondition = "[rptAll]![srptProgramOrg]!
[qselProgramOrg].[ProgramID] = " & [Forms]! [frmReportType]!
[cboProgramID]
=======================================================
And, that didn't work. "srptProgramOrg" is the sub- report
control on rptAll for the report "rptOrg". "rptAll"
has "rptOrg" and "rptInd" as embedded sub-reports.

I'll appreciate any help on this, as I've been staring at
the code for a while, tried different combinations and
come up with nothing. I'm not even sure if it is possible
to refer to the query-field this way.

Thanks much!!

-Amit


.

Try creating the reference in a query using the Build
wizard and compare that to what you have. You'll
probably have to make some adjustment with the quotes.
David S.