Solved Passing conditions to subreport with VBA. (1 Viewer)

[JoséBob]

Hello,
I have a report (ReportMain) with two unbound subreport (Sub1 and Sub2). I would like to create the report through VBA so that the user can input custom date (field Date/Time of the table Log).
when using the VBA code docmd Openreport, I can pass the condition to the whereCondition. It works on a subreport individually. That syntax doesn’t work when opening the main report and it doesn’t work either when creating/opening (and saving) the subreport with the condition and then opening the main report.
i tried to use filter, but I probably don’t have the proper syntax because I get a error message with following code “on load” on the main report (with a random text field):
Me!Sub1.Report.filter = “TextField = ‘Text’”.
Thanks for your help

 

[theDBguy]

Not sure exactly what you're doing, so perhaps consider posting a sample db to demonstrate the issue?

 

[Minty]

I think you would need to set the subreports recordsource to a saved query, then modify the query definition to make something like that work?

 

[JoséBob]

I think you would need to set the subreports recordsource to a saved query, then modify the query definition to make something like that work?

Good idea. I’ll try and let you know. I’ll post a sample in the mean time.

 

[MajP]

I would think that you should be able to pass the criteria in open args to the main report which in turn applies to the subreport . Something like

Private Sub Form_OnLoad()
if me.Openargs & "" <> "" then
Me.subRptControl.Report.filter = "SomeField = '" & me.Openargs & "'"
end if
end sub

 

[JoséBob]

I would think that you should be able to pass the criteria in open args to the main report which in turn applies to the subreport . Something like

Private Sub Form_OnLoad()
if me.Openargs & "" <> "" then
Me.subRptControl.Report.filter = "SomeField = '" & me.Openargs & "'"
end if
end sub

That another option which looks easier. I’ll try as well (or maybe at first).
I can see what I was missing at first is that the filter should be in the onload event of the subreport and not in the opening event of the main report. Which I didn’t mention in my question.
i guess that using public variables I can move the variables from one function (or sub) to another easily and get my opening argument.
I’ll let you know.

 

[MajP]

You can also act on the subreport directly from the calling form instead of passing it through the openargs (unless you open it ACDIALOG)

docmd.openreport "reportMain"
'code keeps running in the calling form if not open ACDIALOG so next line executes
reports!ReportMain.sub1.report.filter = "txtField = '" & someValue & "'

"

 

[JoséBob]

You can also act on the subreport directly from the calling form instead of passing it through the openargs (unless you open it ACDIALOG)

docmd.openreport "reportMain"
'code keeps running in the calling form if not open ACDIALOG so next line executes
reports!ReportMain.sub1.report.filter = "txtField = '" & someValue & "'

"

When executing line by line, in a module as a public function, vba doesn’t like the very first line (docmd.OpenReport “reportMain”). It pops an error message run-time error 2212.

 

[MajP]

Need to set acVeiwPreview. The default is to print. It is the second argument of the docmd.openreport.

 

[JoséBob]

Need to set acVeiwPreview. The default is to print. It is the second argument of the docmd.openreport.

Ok.

 

[JoséBob]

Thanks all for your answers and the discussion. I used a query and based both my reports on it. I put the conditions/criteria of the query in the vba code and used Select/Case to get the different situation.