By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

phantom querydef

P: n/a
I have a system which allows a user to create a list of predefined
reports to print. Each report has a chart object, all the chart
objects have rowsource set to "ChartQuery".

As I loop through the list, printing the selected reports, I build the
"ChartQuery" querydef by replacing certain key words in an SQL
statement associated with the selected report with variable data set
as the user selects the reports. Specifically, the user can specify
start and end dates and a shift id to filter by.

Here is the code for replacing the key words in the SQL statement,
deleting the querydef if it already exists, and creating a new
"ChartQuery" querydef.

'Do string replaces on "PlotStartDate", "PlotEndDate", and "PlotShift"
in Query Field
strChartSQL = Replace(rst.Fields("Query"), "PlotStartDate", "#" &
rst.Fields("PlotStartDate") & "#")
strChartSQL = Replace(strChartSQL, "PlotEndDate", "#" &
rst.Fields("PlotEndDate") & "#")
strChartSQL = Replace(strChartSQL, "PlotShift", "'" &
rst.Fields("PlotShift") & "'")
'Save the Query under the name used in the Chart properties -
"ChartsQuery"
On Error Resume Next
db.QueryDefs.Delete "ReportQuery"
db.QueryDefs.Delete "ChartQuery"
On Error GoTo Err_PrintChartsButton_Click
Set ReportQuery = db.CreateQueryDef("ReportQuery", strReportSQL)
Set ChartQuery = db.CreateQueryDef("ChartQuery", strChartSQL)
DoCmd.OpenReport rst.Fields("ReportName"), acViewNormal
rst.MoveNext

The problem that I am having is that sometimes a chart seems to plot
the data from the dataset from the previous report. I don't understand
why this is happening since I am deleting and recreating "ChartQuery"
for each report. I open the report after creating the Querydef. I've
watched the querydef sql in debug mode, and it is built correctly. But
the chart seems to be using a phantom dataset.

Can anyone offer me a clue?

TIA, Sharon
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.