Open a report in subform with a WHERE

[steve1111]

Hello all,

I have a report i want to filter and then open in a subform. I can

docmd.openreport "reportname: , acReportView,,strWhere
just fine for a pop up report

I can also use a command button to set sourceobject of my subform to the report but that method i am falling short on adding the WHERE before it loads in the subform.

I have tried setting the filter on the actual report to the textbox that holds my strWhere string, but no luck that way either.

thanks

 

[pbaldy]

If the report in the subform container is in report view, you may be able to set the source object and then set the filter property of the report with strWhere. That's completely untested. You can certainly filter the report from within, using either a query criteria or this type of thing:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

setting the filter from the open event.

 

[steve1111]

Thanks pbaldy for the help, but the source object then filter did not work. One of the issues i am having is with changing the recordsource on open, because i cannot use a Where statement in the query itself. I have a standard frmFilters that loads the appropriate multi-value list box for the report i am working with.

Because they are multi-value list boxes i cannot set the query to those criteria. I have to build the query as all inclusive and pass the WHERE to the report. One additional component is that on the frmFilters i build the string after each AfterUpdate to each of the list boxes. I know there is probably a better way to do in VBA but everything i researched fell short with the multi-valued properties.

so essentially i need to pass the frmFilters!txtStrWhere to the report when opening, but me.filter and me.filterOn are not producing the results in the subform. Again the pop up report works great, i just want to keep from the pop up and see it in the subform on my form.

thank you.

 

[pbaldy]

Did you try the code from the link in the report's open event? I've used that many times, though I've never had a report as a subform.

 

[steve1111]

I did try that and i found my error was in putting it in the parent report rather than the Open of the subform report. It is not filtering correctly under:

me.filter = Nz(path to Where string,"")
me.filterOn = True

had a nasty side effect though where it clears the selections on my listbox on my other frmFormFilter subform. i will have to look into that one some more.

thanks for pointing me in that direction!

 

[pbaldy]

Happy to help! I wouldn't expect that to have any effect on the listbox.