Link to home
Start Free TrialLog in
Avatar of bgartonsr
bgartonsr

asked on

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

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.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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


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.


Avatar of bgartonsr
bgartonsr

ASKER

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
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
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
"Here is an example of it not working as I expected."
show us what you expect!
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.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
OK great

;-)