469,568 Members | 1,446 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

phantom querydef

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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by memememe | last post: by
reply views Thread by Rod Billett | last post: by
9 posts views Thread by Dave | last post: by
9 posts views Thread by TB | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.