Criteria to open a subreport

[Khalil Handal]

Hi to all,

A command button on a form opens a main report with a subreports.

DoCmd.OpenReport "rptReportName", view:=acViewPreview, wherecondition:=strCriteria

This main report has a subreport with record source (qryNameOfQuery).
I need to open the subreport with condition that one of the field values is False (MarkValue = False). What code should I put in the on Load Event of the subreport?


Khalil

[Micron]

IIRC, you cannot apply a filter to a subreport using the load or open events. If your T/F field isn't or cannot be on the main report also (thus allowing you to use link master & child field properties) then I'd say you must either
- modify the querydef object for the sub report query and then open the main report. The sub report query will then be filtered accordingly. Or
- construct the sub report sql in code and try setting the sub report record source property to that. Or
- you could have a control (e.g. button) in main form header that will apply your filter

[Khalil Handal]

Thank you for the reply,
My field is not on the main form. I will try the options suggested.

[Khalil Handal]

Hi,

I have the following partial code:

Dim db As dao.Database
Dim strSQL As String

Set db = CurrentDb
 strSQL = "Select * from qryQueryNameSubReport WHERE MarkDeleted = -1"

rptMAinReportName.cntContainerName.Report.RecordSource = strSQL      
DoCmd.OpenReport "rptReportName", view:=acViewPreview, wherecondition:=strCriteriaDim db As dao.Database
Dim strSQL As String

Set db = CurrentDb
 strSQL = "Select * from qryQueryNameSubReport WHERE MarkDeleted = -1"

rptSubReportReportName.cntCollected.Report.RecordSource = strSQL      
DoCmd.OpenReport "rptTReportName", view:=acViewPreview, wherecondition:=strCriteria

I am receiving the following error
Error 91: Object variable or With block variable not set

Any ideas?

[Welshgasman]

If we knew what line, that would help
Why is the code repeated?

[Khalil Handal]

Hi,
The repetition of the code is by mistake.
The error is in the following line:

rptMainReportName.cntCollected.Report.RecordSource = strSQL

The above code is supposed to determine the Record Source of the Sub Report. I am using the name of the Container and not the name of the Sub Report.

Khalil

[Welshgasman]

Hi,
The repetition of the code is by mistake.
The error is in the following line:

rptMainReportName.cntCollected.Report.RecordSource = strSQL

The above code is supposed to determine the Record Source of the Sub Report. I am using the name of the Container and not the name of the Sub Report.

Khalil

And the main report is rptMainReportName , literally ?
If that is the object holding the main report, where is that set?

[Micron]

I would say it's because if you're going to refer to the report by name that you must begin with the collection it belongs to - as in Forms! or Reports! in this case. I'm not positive as I've never coded that way. If I don't use Reports! or Forms! I use Me.

[Khalil Handal]

Hi,
I think I don't know where to put the SET, And how to select the subReport control source.

[Micron]

I'm saying not rptSubReportReportName.cntCollected.Report.RecordS ource = strSQL
but
Me.cntCollected.Report.RecordSource = strSQL
or
Reports!cntCollected.Report.RecordSource = strSQL

I don't see why you're setting anything because you don't need it based on what you posted. If you think you do and need assistance, post the whole procedure.

[Khalil Handal]

Hi,
Thank you for the suggestions.
I was able to do it by: modifying the querydef object for the sub report query and then open the main report.
Thank you again
Khalil