Pass a query to and OLE chart on a report

[mguidry5]

howdy,

I've got a report called rptSummary[color] with two OLE pie charts on it. I'm opening the report from a form called frmAdminTools. I've got some code on the report's On Open event that grabs some parameters from frmAdminTools and makes a query string. I'm trying to apply the query to the Row Source for the OLE pie charts within the same report On Open event. I'm getting errors to the effect of "You entered an expression that has an invalid reference to the property form/report."

How do I set the query string that I've created as the data source for my OLE pie charts? I've included a sample of the code I'm trying to use to pass the query to the OLE object. Yes - my query works.

Me.OLEUnbound42.Report.RecordSource = "SELECT tblIncidentLocation.Location, Count(*) AS [Count] " & _ "FROM tblIncidents INNER JOIN tblIncidentLocation "  & _ "ON tblIncidents.Location = tblIncidentLocation.ID WHERE " & strWhere & " GROUP BY " & _ "tblIncidentLocation.Location;"

 

[dhookom]

I would set the Row Source of the pie chart to a saved query ie: "qryPieChartA". Then use code in your frmAdmTools like:

Dim strSQL as String
strSQL = "SELECT tblIncidentLocation.Location, Count(*) AS [Count] " & _ 
"FROM tblIncidents INNER JOIN tblIncidentLocation "  & _
"ON tblIncidents.Location = tblIncidentLocation.ID WHERE " & strWhere & _
" GROUP BY tblIncidentLocation.Location;"
CurrentDb.QueryDefs("qryPieChartA").SQL = strSQL
DoCmd.OpenReport "rptWithPieCharts", acPreview

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mguidry5]

Duane,

I'm trying your solution, and i'm getting a message when I click my command button that says "Item not found in this selection." It doesn't bring me into VBA and show we what line the error occurs on, but if I comment out the line

CurrentDb.QueryDefs("qrySHAREbyLocation").SQL = strSQL

Then the code runs through with no messages, but without that eversoimportant where statement.

Could this be a reference issue?

 

[dhookom]

You may need to set a reference to the DAO object library in Tools->References. Also check the name of the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]