report with chart, strWhere

[Misiek]

Hello,

I have been trying to find a best way to open a report based on form filtered data, with additional chart on it. The form and report works perfectly fine. Then I created second query and added a chart based on this query to the existing report. It does work well but unfortunately I don't know how to pass filter from form to this second query.

first query is just basic query with data from table. The second query is based on first one and has calculation fields which are row source for my chart.

In the main form user can just open report with all data, or can apply filter by selecting values in combo boxes, this then passes strWhere string to report.

Second query:

SELECT Q_ActionPlanner.userName AS Owner, Sum(IIf(tRunDown="D",tExecuteTime,0)) AS ExecTimeD, Sum(IIf(tRunDown="R",tExecuteTime,0)) AS ExecTimeR
FROM Q_ActionPlanner
GROUP BY Q_ActionPlanner.userName
ORDER BY Q_ActionPlanner.userName;

report with chart only:

Private Sub cboAPeffort_Click()
Dim strWhere As String
    If Me.Dirty Then Me.Dirty = False 'save any edits
    If Me.FilterOn Then strWhere = Me.Filter
    If strWhere <> "" Then
    DoCmd.OpenReport "R_APeffort", acViewReport, , strWhere & " AND tTaskDueDate <= Date()"
    Else
    DoCmd.OpenReport "R_APeffort", acViewReport, , "tTaskDueDate <= Date()"
    End If
End Sub

report with data and chart

Private Sub cboAPprintarea_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter

If cboAPduedate = "Today" Then
    If strWhere <> "" Then
    DoCmd.OpenReport "R_APdailyplan2", acViewReport, , strWhere & " AND tTaskDueDate <= date()"
    Else
    DoCmd.OpenReport "R_APdailyplan2", acViewReport, , "tTaskDueDate <= Date()"
    End If
Else
MsgBox ("Please Select Day Before Printing.")
End If
End Sub

Once again, I don't know how to pass strWhere from main form to report with chart as chart has separate query.

I tried

DoCmd.OpenQuery "Q_APchart", acViewPivotChart, acReadOnly ', strWhere & " AND tTaskDueDate <= Date()"

- but this gives me an error


Can anyone advice please?

 

[llkhoutx]

Your SQL is wrong in syntax:

Try

DoCmd.OpenReport "R_APdailyplan2", acViewReport, , "tTaskDueDate <=#" & Date() & "#"

Adjust the other SQL strings accordingly. Think about what the why with what I've displayed.

 

[Misiek]

your message is off topic, only need to use # if you use specific date like #24/11/2015#
here, date() is a function so don't need to use #

I can create a calculated field in report footer, but I can't based chart on it....?

 

[Misiek]

Hello again,

I probably didn’t specify my problem clear enough?

I have a form with a filter, user can apply filter - this works ok.

There is a button on the form that opens report and this report print correctly filtered data using strWhere:

Private Sub cboAPeffort_Click()
Call cmdAPfilter_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "R_APdailyplan2", acViewReport, , strWhere, , strWhere
End Sub

I found this, explains exactly same problem I have.
I followed those instructions but when report opens gives me error:

http://www.accessforums.net/reports/button-open-report-only-filtered-data-42486.html

Runtime error 2191, 
You can’t change row source property in print preview.

This is my report vba:

Option Compare Database
Option Explicit

Dim strWhere As String

Private Sub Report_Load()
strWhere = Me.OpenArgs
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Dim strSQL As String
strSQL = "SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD FROM Q_ActionPlanner GROUP BY userName HAVING " & strWhere
Debug.Print strSQL
Me![Chart1].RowSource = strSQL
End Sub

I also noticed, If I change
acViewPreview to acViewReport
the report will open and filter correctly, but chart loads all data and doesn’t filter.

Can someone advice please?

 

[JHB]

What happen if you place all the code in the report Load or Open event?
Could you post a sample database?

 

[Misiek]

What exactly do you mean by "place all the code"?

I already have SQL in OnLoad event on my report.

Could post DB, but will take me few hours to make it internet friendly, so just hope for directions instead :/

 

[JHB]

What exactly do you mean by "place all the code"?

I already have SQL in OnLoad event on my report.

No you haven't some is placed in the PageHeaderSection_Format event.
But never mind it wouldn't work either.
One solution is to open the report in design view (hidden) from VBA code and change the RowSource, after it is change show the report in Preview.
Another solution is to base the RowSource on a query, and set the criteria in the query before the report is open.

 

[Misiek]

changed button to

Private Sub cboAPeffort_Click()
Dim strWhere As String
Call cmdAPfilter_Click
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter

Debug.Print strWhere
DoCmd.OpenReport "R_APdailyplan2", acViewDesign, , strWhere, acHidden, strWhere
DoCmd.OpenReport "R_APdailyplan2", acViewPreview, , strWhere, , strWhere
End Sub

then changed report unload to:

Option Compare Database
Option Explicit

Dim strWhere As String

Private Sub Report_Load()
strWhere = Me.OpenArgs
Dim strSQL As String
strSQL = "SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD FROM Q_ActionPlanner GROUP BY userName WHERE " & strWhere
Debug.Print strSQL
Me![Chart1].RowSource = strSQL
End Sub

but when report opens it gives me error:
runtime error: 2455
You entered expression that has invalid reference to the property source

and highlights this line:
Me![Chart1].RowSource = strSQL

 

[JHB]

The code for solution 1. is like below, (remember to remove all the code in the report).

  Dim AReport As Report
  
  DoCmd.OpenReport "R_APdailyplan2", acViewDesign, , , acHidden
  Set AReport = Reports.Item("R_APdailyplan2")
  AReport.Controls("Chart1").RowSource = "SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD FROM Q_ActionPlanner GROUP BY userName WHERE " & Me.Filter
  DoCmd.OpenReport "R_APdailyplan2", acViewPreview

Solution 2.
First create a query, call it "QueryR_APdailyplan2", change the rowsource for the graph to the query.
Code for solution 2, (remember to remove all the code in the report).

  Dim qdf As DAO.QueryDef
  Set qdf = CurrentDb.QueryDefs("QueryR_APdailyplan2")
  qdf.SQL = "SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD FROM Q_ActionPlanner GROUP BY userName WHERE " & Me.Filter
  DoCmd.OpenReport "R_APdailyplan2", acViewPreview

Code for both solutions is placed in the form from where you open the report.

 

[Misiek]

I've tried solution 1. it opens report but doesn't pass the filter on, so chart doesn't filter and the report data isn't filtered as well :/


also tried the second option with query and doesn't even open the report

run-time error 3075;
syntax error (missing operator) in query expression 'userName WHERE ([tAreaFK] = 4) AND tTaskDueDate <= date()'.

i'm still lost :/

 

[JHB]

Without exact table and some sample data it isn't easy to help you.
But I know both solutions works!
For error finding in solution 2., create a query as below, then it is easier to find the syntax error.

SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD FROM Q_ActionPlanner GROUP BY userName WHERE ([tAreaFK] = 4) AND tTaskDueDate <= date()

 

[Misiek]

Oh yeah, No this query shows error with WHERE, I'm completely lost now :/


I've stripped all data from DB, please see attached file.


Thank you

 

[arnelgp]

will this help?

 

[JHB]

Your SQL-String was wrong build, therefore it couldn't run, (therefore I gave you the advice to create a query so you could find fault).
Correct one:

"SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD " _
& "FROM Q_ActionPlanner " _
& "WHERE " & Me.Filter & " GROUP BY userName;"

Yours:

"SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD FROM Q_ActionPlanner GROUP BY userName WHERE " & Me.Filter

I've put to new button on the form, "Solution 1" and "Solution 2", try them.

 

[Misiek]

lovely!!!!! Thank you JHB

I like the solution 2 as it doesnt leave report open in design after i'm finished.

However neither of those 2 solution filter the actual report, but chart only.

I added strWhere at the end of docmd.

and this at the begining:

Call cmdAPfilter_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter

DoCmd.OpenReport "R_APdailyplan2 Solution2", acViewPreview, , strWhere

and solution 2 works but solution 1 doesnt, would like to understand why it doesnt?

Thank you

 

[JHB]

Put the line marked with red into your code for solution 1.

  Dim AReport As Report
  DoCmd.OpenReport "R_APdailyplan2", acViewDesign ', , , acHidden
  Set AReport = Reports.Item("R_APdailyplan2")
  AReport.Controls("Chart1").RowSource = "SELECT userName, Sum(IIf([tRunDown]='R',[tExecuteTime],0)) AS ExTimeR, Sum(IIf([tRunDown]='D',[tExecuteTime],0)) AS ExTimeD " _
  & "FROM Q_ActionPlanner " _
  & "WHERE " & Me.Filter & " GROUP BY userName;"
 [B][COLOR=Red] DoCmd.Close acReport, "R_APdailyplan2", acSaveYes
[/COLOR][/B]  DoCmd.OpenReport "R_APdailyplan2", acViewPreview, , [B][COLOR=Red]Me.Filter[/COLOR][/B]

 

[Misiek]

All working now. Thank you very much for your help!

 

[JHB]

You're welcome, good luck.