Solved

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

Posted on 2015-02-19
8
75 Views
Last Modified: 2015-03-06
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....
0
Comment
Question by:baytowel
  • 4
  • 3
8 Comments
 

Author Comment

by:baytowel
ID: 40618609
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
 

Author Comment

by:baytowel
ID: 40618619
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40618646
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
 

Author Comment

by:baytowel
ID: 40618693
Now I get the error on this line:

Set qdt = db.QueryDefs("qry_EmployeeFilteredFinal")

same error.....
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40618718
Then you miss

Set db = CurrentDb

or "qry_EmployeeFilteredFinal" doesn't exist.

/gustav
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40618772
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
 

Author Comment

by:baytowel
ID: 40618773
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40618792
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now