We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Access 2007 - Passing Where Condition to SubForm on a Report is not working
Posted on 2010-09-23
Have an application which allows filtering and produces 7 reports or charts (using tab control) that can be viewed as filters are applied. I also offer the ability to print or print to PDF. The format on the forms works good for the screen, but for the reports and PDF, I need to do more appropriate formatting. This works fine by creating a report and putting the form (drag/drop as a sub form) in the report.
The problem arises when I pass the Where Condition to the report. It appears that since the report is based on the form, it does not pick up the Where Condition. I tested by creating a report based on the form and doing a VBA OpenReport with the Where Condition it worked as expected. However, if I put the form into the report (drag/drop), same command the Where Condition is not picked up. Am wondering if I need to execute a focus commmand or have a different reference for the Where Condition. Stumped, would appreciate any help. Thanks.
The problem arises when I pass the Where Condition to the report. It appears that since the report is based on the form, it does not pick up the Where Condition. I tested by creating a report based on the form and doing a VBA OpenReport with the Where Condition it worked as expected. However, if I put the form into the report (drag/drop), same command the Where Condition is not picked up. Am wondering if I need to execute a focus commmand or have a different reference for the Where Condition. Stumped, would appreciate any help. Thanks.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
4
- +1
11 Comments
Active today
I'm not clear what you mean.
You've embedded a Form in a Report? If so, then (a) what's the reason for doing this and (b) this won't work for your situation.
If you need to base a Form on a Report, you can use the Open event of that Report to refer to the Form:
Sub Form_Open()
Me.Recordsource = "SELECT * FROM SomeTAble WHERE MyField=" & Forms("MyForm").SomeFormFi
End Sub
Or Filter the Report, if that's more appropriate:
Me.Filter = "SomeField=" & Forms("MyForm").SomeField
Me.FilterOn = True
You've embedded a Form in a Report? If so, then (a) what's the reason for doing this and (b) this won't work for your situation.
If you need to base a Form on a Report, you can use the Open event of that Report to refer to the Form:
Sub Form_Open()
Me.Recordsource = "SELECT * FROM SomeTAble WHERE MyField=" & Forms("MyForm").SomeFormFi
End Sub
Or Filter the Report, if that's more appropriate:
Me.Filter = "SomeField=" & Forms("MyForm").SomeField
Me.FilterOn = True
First I will say that Printing "Forms" is always tricky.
Can you explain why you are printing a report and a form?
(in case I am misunderstanding something here)
Normally a form is used to set/collect the Report Criteria, then the Report is printed/Viewed.
So again, (as LSM also asks) ...why do you need to print the form?
If you are trying to display the report criteria (from the Form) on the report, then a simple way to do this would be:
Put a textbox in the report header
Name it something like: txtReportCriteria1
Make the controlsource of the textbox: Forms!YourFormName!YourCri
JeffCoachman
Can you explain why you are printing a report and a form?
(in case I am misunderstanding something here)
Normally a form is used to set/collect the Report Criteria, then the Report is printed/Viewed.
So again, (as LSM also asks) ...why do you need to print the form?
If you are trying to display the report criteria (from the Form) on the report, then a simple way to do this would be:
Put a textbox in the report header
Name it something like: txtReportCriteria1
Make the controlsource of the textbox: Forms!YourFormName!YourCri
JeffCoachman
besides, I am still curious why you have to insert the form into the report for the report to read the criteria...?
<It appears that since the report is based on the form, it does not pick up the Where Condition.>
?
This has worked for everyone I know for years:
(code on the form:)
DoCmd.OpenReport "YourReportName", acViewPreview, , "YourField=" & Me.txtYourField
...This will open the Report with "YourField" filtered for the "txtYourField" control on the form.
(For text it would be something like this:)
DoCmd.OpenReport "YourReportName", acViewPreview, , "YourField=" & "'" & Me.txtYourField & "'"
Can you post the code you were using.
<It appears that since the report is based on the form, it does not pick up the Where Condition.>
?
This has worked for everyone I know for years:
(code on the form:)
DoCmd.OpenReport "YourReportName", acViewPreview, , "YourField=" & Me.txtYourField
...This will open the Report with "YourField" filtered for the "txtYourField" control on the form.
(For text it would be something like this:)
DoCmd.OpenReport "YourReportName", acViewPreview, , "YourField=" & "'" & Me.txtYourField & "'"
Can you post the code you were using.
Thanks for feedback, let me try to clairify.
1) Application is to display on the screen 6 data sets of which 4 are pivot charts.
2) I use a tab control on a form. The first tab is a form that allows the user to select filtering options. The subsequent tabs have the forms on them that are the data sets.
3) The user has the option to Print any of the data sets (i.e Tabs) to PDF so that they can include them ina PowerPoint presentation.
4) The data sets when displayed on the screen are formatted for the screen. If I print the dataset from the form to PDF, the formatting is not in a good format for the PowerPoint.
5) My solution was to create a report based on the form (drag/drop form onto the report) and adjust the report (size, background, titles) so that the PDF is ready for inserting into the PowerPoint.
6) (Another option is to create reports which mirror the forms, however this would involve duplicate maintenance . Another issue arose with the Charting capability in the Report module appears to be inferior to the one in the Forms module, so I had difficulty re-producing an idential Charts to the ones displayed in the Form.)
7) All that works well.
8) The problem arises when I try to pass a Where Condition to the Report which is printing a form. The report prints all of the records, not the requested filtering. (DoCmd.OpenReport "rptTestQueryFilters3", acViewPreview, , "Project_Size = 3", acWindowNormal.)
9) To test where the problem was occuring, I created a report with the same fields, same DoCmd, passing the filter and it did filter the data.
10) So I believe the problem is related to the filter not being passed to the subForm. I am searching the web and seeing other similar issues.
Appreciate your help in taking a look at this for me. Any suggestions, even a different approach for solving the application requirements, would be greatly appreciated.
Thanks
1) Application is to display on the screen 6 data sets of which 4 are pivot charts.
2) I use a tab control on a form. The first tab is a form that allows the user to select filtering options. The subsequent tabs have the forms on them that are the data sets.
3) The user has the option to Print any of the data sets (i.e Tabs) to PDF so that they can include them ina PowerPoint presentation.
4) The data sets when displayed on the screen are formatted for the screen. If I print the dataset from the form to PDF, the formatting is not in a good format for the PowerPoint.
5) My solution was to create a report based on the form (drag/drop form onto the report) and adjust the report (size, background, titles) so that the PDF is ready for inserting into the PowerPoint.
6) (Another option is to create reports which mirror the forms, however this would involve duplicate maintenance . Another issue arose with the Charting capability in the Report module appears to be inferior to the one in the Forms module, so I had difficulty re-producing an idential Charts to the ones displayed in the Form.)
7) All that works well.
8) The problem arises when I try to pass a Where Condition to the Report which is printing a form. The report prints all of the records, not the requested filtering. (DoCmd.OpenReport "rptTestQueryFilters3", acViewPreview, , "Project_Size = 3", acWindowNormal.)
9) To test where the problem was occuring, I created a report with the same fields, same DoCmd, passing the filter and it did filter the data.
10) So I believe the problem is related to the filter not being passed to the subForm. I am searching the web and seeing other similar issues.
Appreciate your help in taking a look at this for me. Any suggestions, even a different approach for solving the application requirements, would be greatly appreciated.
Thanks
I still don't see why you would need to drop the form on the report?
<My solution was to create a report based on the form (drag/drop form onto the report) >
Perhaps I am missing something here...
But in it's purest sense, opening a filtered report should be as simple as what LSM and I posted...
(are you sure your filter does not include all records?
For example, you have a table of sales in the UK and your filter is "Country='UK'")
?
JeffCoachman
<My solution was to create a report based on the form (drag/drop form onto the report) >
Perhaps I am missing something here...
But in it's purest sense, opening a filtered report should be as simple as what LSM and I posted...
(are you sure your filter does not include all records?
For example, you have a table of sales in the UK and your filter is "Country='UK'")
?
JeffCoachman
Reasons for printing Form from reports:
1) Do not want to duplicate all 6 forms as reports
2) Difficult when making changes
3) Charting in Report is weak.
Here is an example of it not working as I expected.
TestSubFromFilters.accdb
1) Do not want to duplicate all 6 forms as reports
2) Difficult when making changes
3) Charting in Report is weak.
Here is an example of it not working as I expected.
TestSubFromFilters.accdb
Thanks for staying with this.
1) rptTestQuery1 is doing the report as a report.
2) rptTEstQuery 2 is ding the report with an imbedded form in the report
3) frmTestPrint opens both reports using the same passed SQL:
DoCmd.OpenReport "rptTestQuery1", acViewPreview, , "Age = 33", acWindowNormal
DoCmd.OpenReport "rptTestQuery2", acViewPreview, , "Age = 33", acWindowNormal
4) Results:
1) rptTestQuery1 provides a report with the data filtered as per the SQL passed
2) rptTestQuery2 provides a report with the data not filtered as per the SQL passed
I provided this is in your response stating that it should work in both cases. I have provided it to demostrate to you that it does not work as expected, i.e. rptTestQuery2 is not filtered.
I was looking for assistance on how to make it work.
1) rptTestQuery1 is doing the report as a report.
2) rptTEstQuery 2 is ding the report with an imbedded form in the report
3) frmTestPrint opens both reports using the same passed SQL:
DoCmd.OpenReport "rptTestQuery1", acViewPreview, , "Age = 33", acWindowNormal
DoCmd.OpenReport "rptTestQuery2", acViewPreview, , "Age = 33", acWindowNormal
4) Results:
1) rptTestQuery1 provides a report with the data filtered as per the SQL passed
2) rptTestQuery2 provides a report with the data not filtered as per the SQL passed
I provided this is in your response stating that it should work in both cases. I have provided it to demostrate to you that it does not work as expected, i.e. rptTestQuery2 is not filtered.
I was looking for assistance on how to make it work.
The reason why the "Report" with the embedded "Form" is not filtering is that you are opening the "Report" with the Where condition.
But rptTestQuery2 has no recordSource....
;-)
If the *form* is what you want filtered, then you must pass the filter to the form.
This is why we all are wondering why you are doing this in such a non-standard way...
This is why LSM suggests building the SQL in Code.
Another factor here is that some code in a Report will not fire in Report View (The default view for both of your reports.), ...but will fire in Print Preview.
So, ...If you set the subform's Filter (via a Public variable, on the subforms Open Event) when the Report is opened in Report View, the Subform's Open Even will fire.
Thus giving you what you want.
(You will have to determine the correct event to clear the Public Variable though...)
...If, on the other hand, you try to set the Filter of the subform (via a Public variable) when the Report's Default View is Print Preview, ...the form won't filter, because in Print Preview the subform's Open Even will not fire.)
This is why your technique may become confusing at some point in the future.
I hate to sound like a broken record when it comes to all the things I have learned from LSM...
But of all the different ways to "Filter" anything, setting the recordsource is, AFAIC, buy far the most reliable...
No the easiest, mind you, ...just the most reliable.
Remember that reliability is the only thing that really matters in most cases.
;-)
Sometimes when coding things like this, it is easy to get lost in your own code.
When this happens I always revert to setting the SQL in code.
The all is right with the world...
;-)
Revised sample attached
JeffCoachman
Access-EEQ26493811FilterSubforIn.accdb
But rptTestQuery2 has no recordSource....
;-)
If the *form* is what you want filtered, then you must pass the filter to the form.
This is why we all are wondering why you are doing this in such a non-standard way...
This is why LSM suggests building the SQL in Code.
Another factor here is that some code in a Report will not fire in Report View (The default view for both of your reports.), ...but will fire in Print Preview.
So, ...If you set the subform's Filter (via a Public variable, on the subforms Open Event) when the Report is opened in Report View, the Subform's Open Even will fire.
Thus giving you what you want.
(You will have to determine the correct event to clear the Public Variable though...)
...If, on the other hand, you try to set the Filter of the subform (via a Public variable) when the Report's Default View is Print Preview, ...the form won't filter, because in Print Preview the subform's Open Even will not fire.)
This is why your technique may become confusing at some point in the future.
I hate to sound like a broken record when it comes to all the things I have learned from LSM...
But of all the different ways to "Filter" anything, setting the recordsource is, AFAIC, buy far the most reliable...
No the easiest, mind you, ...just the most reliable.
Remember that reliability is the only thing that really matters in most cases.
;-)
Sometimes when coding things like this, it is easy to get lost in your own code.
When this happens I always revert to setting the SQL in code.
The all is right with the world...
;-)
Revised sample attached
JeffCoachman
Access-EEQ26493811FilterSubforIn.accdb
Thanks for taking the time to reply, your solution is very well done.
In the early AM on Saturday (inspired by the previous comments) I came up with:
strSQL = funBuildSQLForFilterLists(
Set rst = CurrentDb.OpenRecordset(st
Set Me.frmPMODashboard_05_Proj
DoCmd.OpenReport "rptPMODashboard_05_Projec
Seems to work and very simular to your approach, I will consider both approaches as I move towards completeing the app.
The larger picture of the application is that I have 5 cascading list boxes that each allow selection of multiple values from which I currently build the SQL that is used to drive the Forms/Reports. I will see which method works best.
Thanks for the "heads up" on event firing. I will have to check that out. All report printing is currently done first through Print Preview, however I am also offering a button to output to PDF (righ now without Print Preview). I will watch that one closely.
I appreciate all of your efforts to provide a solution for this challenge.
Thank you very much.
In the early AM on Saturday (inspired by the previous comments) I came up with:
strSQL = funBuildSQLForFilterLists(
Set rst = CurrentDb.OpenRecordset(st
Set Me.frmPMODashboard_05_Proj
DoCmd.OpenReport "rptPMODashboard_05_Projec
Seems to work and very simular to your approach, I will consider both approaches as I move towards completeing the app.
The larger picture of the application is that I have 5 cascading list boxes that each allow selection of multiple values from which I currently build the SQL that is used to drive the Forms/Reports. I will see which method works best.
Thanks for the "heads up" on event firing. I will have to check that out. All report printing is currently done first through Print Preview, however I am also offering a button to output to PDF (righ now without Print Preview). I will watch that one closely.
I appreciate all of your efforts to provide a solution for this challenge.
Thank you very much.
Featured Post
Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention. Check out this how-to article for more information.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month6 days, 22 hours left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.