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

JoséBob

New member
Local time
Today, 22:47
Joined
Sep 19, 2023
Messages
23
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

I’m here to help
Staff member
Local time
Today, 13:47
Joined
Oct 29, 2018
Messages
21,296
Not sure exactly what you're doing, so perhaps consider posting a sample db to demonstrate the issue?
 

Minty

AWF VIP
Local time
Today, 21:47
Joined
Jul 26, 2013
Messages
10,330
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

New member
Local time
Today, 22:47
Joined
Sep 19, 2023
Messages
23
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

You've got your good things, and you've got mine.
Local time
Today, 16:47
Joined
May 21, 2018
Messages
8,419
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

New member
Local time
Today, 22:47
Joined
Sep 19, 2023
Messages
23
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've got your good things, and you've got mine.
Local time
Today, 16:47
Joined
May 21, 2018
Messages
8,419
You can also act on the subreport directly from the calling form instead of passing it through the openargs (unless you open it ACDIALOG)


Code:
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

New member
Local time
Today, 22:47
Joined
Sep 19, 2023
Messages
23
You can also act on the subreport directly from the calling form instead of passing it through the openargs (unless you open it ACDIALOG)


Code:
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

You've got your good things, and you've got mine.
Local time
Today, 16:47
Joined
May 21, 2018
Messages
8,419
Need to set acVeiwPreview. The default is to print. It is the second argument of the docmd.openreport.
 

JoséBob

New member
Local time
Today, 22:47
Joined
Sep 19, 2023
Messages
23
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.
 

Users who are viewing this thread

Top Bottom