Solved

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

Posted on 2010-09-23
11
921 Views
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.
0
Comment
Question by:bgartonsr
11 Comments
 
LVL 84
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
0
 
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

JeffCoachman


0
 
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.


0
 

Author Comment

by:bgartonsr
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.
Thanks
0
 
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'")
?

JeffCoachman
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:bgartonsr
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.
TestSubFromFilters.accdb
0
 
LVL 30

Expert Comment

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

Author Comment

by:bgartonsr
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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


JeffCoachman
Access-EEQ26493811FilterSubforIn.accdb
0
 

Author Comment

by:bgartonsr
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33770126
OK great

;-)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now