View a Report from filtered data of a subform

I ll like to view a report from filtered records of a subform. I used a commandbutton based on a query to filter the data and i need the report to carry all the filtered data of the form, ( filtered data, ordered by)

Private Sub CmdApplyFilter_Click()
Me![subform].form.RecordSource = "select * from Table2 where Table2.status not in ('complete','pending')"
Exit Sub
End Sub

' Previewing my filtered data

Private Sub CmdPreviewReport_Click()
Dim rpt As Access.Report
Dim stDocName As String
stDocName = "Request_Report"
DoCmd.OpenReport stDocName, acPreview, , Me.subform.form.Filter, , Me.subform.form.OrderBy
End Sub

this does not give me the filtered results i really need
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

DoCmd.OpenReport stDocName, acPreview, , "[status] not in ('complete','pending')"
peterslove53Author Commented:
i want the report to preview whatever filter the user chooses, the filter above is just an example
Rey Obrero (Capricorn1)Commented:
how are you ACTUALLY setting the filter of the subform. (don't post an example)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

peterslove53Author Commented:
i create forms from a query of the main table, the subform is created from another form which is based on a query.  Usually the above syntax works for passing any filtered data of the subform to the report. But when i try to reduce the steps that a manual filtering result by using the query above, the report refuses to carry over the above filtered data. So the main question should have been how can i adjust the query so it works like it should. Manually filtering the form works and is carried over currently

(i pass the OpenArgs to the onload event of the report to make that work)
Rey Obrero (Capricorn1)Commented:
in this line

you need to replace the "subform"  with the name of the "subform control"
peterslove53Author Commented:
thats is exactly what i did, and its working except when i use a query to filter the record
Rey Obrero (Capricorn1)Commented:
omg.. decide first which option you want to pursue this filtering.
if you change the record source of the subform, there is no filter created for the subform, unlike when you choose your filter.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peterslove53Author Commented:
i seem to get it to work using Applyfilter;

Private Sub CmcApplyFilter_Click()
Me.subform.form.Filter = "status not in ( 'Complete','Deferred','Cancelled','Delivered')"
Me.subform.form.FilterOn = True
End Sub

But what if i want to add multiple filters?
Rey Obrero (Capricorn1)Commented:
<But what if i want to add multiple filters? >

create a sub routine to build the criteria for your filter in a string variable and use the string variable as you filter.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.