msaccess 2003 - how do i use a querydef as rowsource for a graph

I have a graph that is based on a parameterized crosstab query. I need to use this query as the rowsource for a graph on a form. I am getting the runtime 91 error: "Object variable or with block variable not set". We are using Access 2003 and the data is from SQL Server 2008. Here is my code:

 With qdf
            .Parameters("pSdate").Value = Me.txtSdate
                Debug.Print qdf.Parameters("pSdate").Value

            .Parameters("pEdate").Value = Me.txtEdate
                Debug.Print qdf.Parameters("pEdate").Value
           
            .Parameters("pItems").Value = Me.lstItems.Column(0)
                Debug.Print qdf.Parameters("pItems").Value
           
            .Parameters("pShift").Value = Me.fraShift
                Debug.Print qdf.Parameters("pShift").Value
        End With
        Debug.Print qdf.SQL
       db.QueryDefs("qry_EmployeeFilteredFinal").SQL = qdf.SQL    -- this is the line access doesn't like
       Me.gph_WeeklyEfficiency.Requery

my data shows in the immediate window as:
8/21/2014
2/17/2015
 430
 1
PARAMETERS pSDate DateTime, pEDate DateTime, pItems Long, pShift Long;
TRANSFORM Avg(qry_EmployeeFiltered_vjb.[Average Efficiency]) AS [AvgOfAverage Efficiency]
SELECT qry_EmployeeFiltered_vjb.Week
FROM qry_EmployeeFiltered_vjb
GROUP BY qry_EmployeeFiltered_vjb.Week
ORDER BY qry_EmployeeFiltered_vjb.Week DESC
PIVOT qry_EmployeeFiltered_vjb.EmpName;


Can you explain how to use a parameterized crosstab query as a source for my graph?
Thank you in advance....
baytowelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

baytowelAuthor Commented:
The reason for the parameters is that this query is based on another query, based on another query, based on another.... etc. I believe it goes 4 queries deep. SQL for queries as shown below:
qry_EmployeeFiltered_vjb

PARAMETERS pItems Long, pShift Long, pEdate DateTime, pSdate DateTime;
SELECT tblWeeks.Week, qry_EmployeeDataWeekly_vjb.[Average Efficiency], qry_EmployeeDataWeekly_vjb.EName AS EmpName
FROM tblWeeks LEFT JOIN qry_EmployeeDataWeekly_vjb ON tblWeeks.Week = qry_EmployeeDataWeekly_vjb.[Week Ending Date]
WHERE (((tblWeeks.Week) Between [pSdate] And [pEdate]) AND ((qry_EmployeeDataWeekly_vjb.item)=[pItems]))
ORDER BY tblWeeks.Week DESC;


qry_EmployeeDataWeekly_vjb

PARAMETERS pItems Long, pShift Long;
SELECT lastdayofweek([date]) AS [Week Ending Date], qry_EmployeeDataPre.Shift, [Namefirst] & " " & [namelast] AS EName, qry_EmployeeDataPre.EmployeeID AS item, Sum(qry_EmployeeDataPre.Hours) AS SumOfHours, Sum(qry_EmployeeDataPre.[# of Pieces]) AS [Total Pieces], Sum(qry_EmployeeDataPre.[100%of Goal]) AS TotalGoal, [Total Pieces]/[totalGoal] AS [Average Efficiency], [pShift] AS Expr1
FROM qry_EmployeeDataPre INNER JOIN Employees ON qry_EmployeeDataPre.EmployeeID = Employees.EmployeeID
WHERE (((IIf([pShift]=3,True,([qry_EmployeeDataPre].[Shift])=[pShift]))=True))
GROUP BY lastdayofweek([date]), qry_EmployeeDataPre.Shift, [Namefirst] & " " & [namelast], qry_EmployeeDataPre.EmployeeID, [pShift]
HAVING (((qry_EmployeeDataPre.EmployeeID)=[pItems]))
ORDER BY lastdayofweek([date]) DESC;

qry_EmployeeDataPre

SELECT qry_A01ProdDataConverted2Times.Date, qry_A01ProdDataConverted2Times.Shift, Employees.EmployeeID, qry_A01ProdDataConverted2Times.ProductID, Sum(nz([Pieces])) AS [# of Pieces], Sum((DateDiff("n",[ConvertedStart],[ConvertedEnd]))/60)-(Min([marks])*2.5/60) AS Hours, First(Products.ProductionGoal) AS FirstOfProductionGoal, (Sum((DateDiff("n",[ConvertedStart],[ConvertedEnd]))/60)-(Min([marks])*2.5/60))*[FirstofProductionGoal] AS [100%of Goal]
FROM (Products INNER JOIN qry_A01ProdDataConverted2Times ON Products.ProductID = qry_A01ProdDataConverted2Times.ProductID) INNER JOIN Employees ON qry_A01ProdDataConverted2Times.EmployeeID = Employees.EmployeeID
GROUP BY qry_A01ProdDataConverted2Times.Date, qry_A01ProdDataConverted2Times.Shift, Employees.EmployeeID, qry_A01ProdDataConverted2Times.ProductID;
0
baytowelAuthor Commented:
Additionally......
When I open a recordset based on the parameterized querydef, I get these results, which are correct:

2/14/2015  0.796175575836593
2/7/2015  0.807805233883469
1/31/2015  0.806128648791441
1/24/2015  0.697469576975082
1/17/2015  0.842042657916325
1/10/2015  0.832451359948709
1/3/2015  0.809528691614148
12/27/2014  0.906938626843926
12/20/2014  0.624939937469146
12/13/2014  0.838375925854112
12/6/2014  0.606074478387182
11/8/2014  0.856204379562044

So, Can I set the recordset as the rowsource for the graph?
Again, thank you in advance.....
Vicki
0
Gustav BrockCIOCommented:
You have to be more specific:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdt As DAO.QueryDef
<snip>
With qdf
             .Parameters("pSdate").Value = Me.txtSdate
                 Debug.Print qdf.Parameters("pSdate").Value

             .Parameters("pEdate").Value = Me.txtEdate
                 Debug.Print qdf.Parameters("pEdate").Value
             
             .Parameters("pItems").Value = Me.lstItems.Column(0)
                 Debug.Print qdf.Parameters("pItems").Value
             
             .Parameters("pShift").Value = Me.fraShift
                 Debug.Print qdf.Parameters("pShift").Value
         End With
         Debug.Print qdf.SQL

    Set qdt = db.QueryDefs("qry_EmployeeFilteredFinal")
    qdt.SQL = qdf.SQL  

<continue>

/gustav
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

baytowelAuthor Commented:
Now I get the error on this line:

Set qdt = db.QueryDefs("qry_EmployeeFilteredFinal")

same error.....
0
Gustav BrockCIOCommented:
Then you miss

Set db = CurrentDb

or "qry_EmployeeFilteredFinal" doesn't exist.

/gustav
0
Dale FyeOwner, Developing Solutions LLCCommented:
I think your best bet in this instance is to create a temp table to use as the data source for your graph.  You will probably need to rewrite the query and make it a MakeTable query instead of a select query, but you should be able to use the same technique of defining the querydef, setting the parameters, and then, instead of opening the recordset, use the

qdf.Execute dbfailonerror

command to create the temp table.  You will probably need to preface that with a couple of lines to delete the temp table if it already exists.

if DCOUNT("ID", "mSysObjects", "[Name] = 'temptablename'") = 1 then
    docmd.deleteobject acTable, "tempTableName"
endif
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baytowelAuthor Commented:
You were correct, I was missing Set db = CurrentDb

Now....
I don't error but I get no data to my graph.

This harder to trouble-shoot. Do I still need my :
Me.gph_WeeklyEfficiency.Requery

Put it back in and got no data, no error either.Graph-Props.bmp
I have included a pic of my graph properties, am I missing something here?
0
Gustav BrockCIOCommented:
Wouldn't you need to specify property RowSource for the graph?

I don't work with graphs in Access, so I have no more than common sense to use here ...

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.