Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-02-19
8
Medium Priority
?
88 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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

Set qdt = db.QueryDefs("qry_EmployeeFilteredFinal")

same error.....
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40618718
Then you miss

Set db = CurrentDb

or "qry_EmployeeFilteredFinal" doesn't exist.

/gustav
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 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 52

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

963 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