Solved

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

Posted on 2015-02-19
8
83 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

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

Set qdt = db.QueryDefs("qry_EmployeeFilteredFinal")

same error.....
0
 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

828 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