Link to home
Start Free TrialLog in
Avatar of baytowel
baytowelFlag for United States of America

asked on

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....
Avatar of baytowel
baytowel
Flag of United States of America image

ASKER

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;
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
Avatar of Gustav Brock
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
Now I get the error on this line:

Set qdt = db.QueryDefs("qry_EmployeeFilteredFinal")

same error.....
Then you miss

Set db = CurrentDb

or "qry_EmployeeFilteredFinal" doesn't exist.

/gustav
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.User generated image
I have included a pic of my graph properties, am I missing something here?
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