Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Access 2007 - Passing Where Condition to SubForm on a Report is not working

Posted on 2010-09-23
Medium Priority
Last Modified: 2013-11-28
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.
Question by:bgartonsr
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
LVL 85
ID: 33742298
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").SomeFormField
End Sub

Or Filter the Report, if that's more appropriate:

Me.Filter = "SomeField=" & Forms("MyForm").SomeField
Me.FilterOn = True
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33745206
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!YourCriteriaControlName


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33745482
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.

Industry Leaders: We Want Your Opinion!

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!


Author Comment

ID: 33746506
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33746833
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'")


Author Comment

ID: 33748384
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.
LVL 31

Expert Comment

ID: 33759846
"Here is an example of it not working as I expected."
show us what you expect!

Author Comment

ID: 33766932
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.
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 33767396
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


Author Comment

ID: 33768180
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("Project_Snapshot", "")
            Set rst = CurrentDb.OpenRecordset(strSQL)
            Set Me.frmPMODashboard_05_Project_Snapshot.Form.Recordset = rst
            DoCmd.OpenReport "rptPMODashboard_05_Project_Snapshot"....
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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33770126
OK great


Featured Post

How to Use the Help Bell

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…

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.

Join & Ask a Question