baytowel
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").Valu e = Me.txtSdate
Debug.Print qdf.Parameters("pSdate").V alue
.Parameters("pEdate").Valu e = Me.txtEdate
Debug.Print qdf.Parameters("pEdate").V alue
.Parameters("pItems").Valu e = Me.lstItems.Column(0)
Debug.Print qdf.Parameters("pItems").V alue
.Parameters("pShift").Valu e = Me.fraShift
Debug.Print qdf.Parameters("pShift").V alue
End With
Debug.Print qdf.SQL
db.QueryDefs("qry_Employee FilteredFi nal").SQL = qdf.SQL -- this is the line access doesn't like
Me.gph_WeeklyEfficiency.Re query
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_v jb.[Averag e Efficiency]) AS [AvgOfAverage Efficiency]
SELECT qry_EmployeeFiltered_vjb.W eek
FROM qry_EmployeeFiltered_vjb
GROUP BY qry_EmployeeFiltered_vjb.W eek
ORDER BY qry_EmployeeFiltered_vjb.W eek DESC
PIVOT qry_EmployeeFiltered_vjb.E mpName;
Can you explain how to use a parameterized crosstab query as a source for my graph?
Thank you in advance....
With qdf
.Parameters("pSdate").Valu
Debug.Print qdf.Parameters("pSdate").V
.Parameters("pEdate").Valu
Debug.Print qdf.Parameters("pEdate").V
.Parameters("pItems").Valu
Debug.Print qdf.Parameters("pItems").V
.Parameters("pShift").Valu
Debug.Print qdf.Parameters("pShift").V
End With
Debug.Print qdf.SQL
db.QueryDefs("qry_Employee
Me.gph_WeeklyEfficiency.Re
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_v
SELECT qry_EmployeeFiltered_vjb.W
FROM qry_EmployeeFiltered_vjb
GROUP BY qry_EmployeeFiltered_vjb.W
ORDER BY qry_EmployeeFiltered_vjb.W
PIVOT qry_EmployeeFiltered_vjb.E
Can you explain how to use a parameterized crosstab query as a source for my graph?
Thank you in advance....
ASKER
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
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
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").Valu e = Me.txtSdate
Debug.Print qdf.Parameters("pSdate").V alue
.Parameters("pEdate").Valu e = Me.txtEdate
Debug.Print qdf.Parameters("pEdate").V alue
.Parameters("pItems").Valu e = Me.lstItems.Column(0)
Debug.Print qdf.Parameters("pItems").V alue
.Parameters("pShift").Valu e = Me.fraShift
Debug.Print qdf.Parameters("pShift").V alue
End With
Debug.Print qdf.SQL
Set qdt = db.QueryDefs("qry_Employee FilteredFi nal")
qdt.SQL = qdf.SQL
<continue>
/gustav
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdt As DAO.QueryDef
<snip>
With qdf
.Parameters("pSdate").Valu
Debug.Print qdf.Parameters("pSdate").V
.Parameters("pEdate").Valu
Debug.Print qdf.Parameters("pEdate").V
.Parameters("pItems").Valu
Debug.Print qdf.Parameters("pItems").V
.Parameters("pShift").Valu
Debug.Print qdf.Parameters("pShift").V
End With
Debug.Print qdf.SQL
Set qdt = db.QueryDefs("qry_Employee
qdt.SQL = qdf.SQL
<continue>
/gustav
ASKER
Now I get the error on this line:
Set qdt = db.QueryDefs("qry_Employee FilteredFi nal")
same error.....
Set qdt = db.QueryDefs("qry_Employee
same error.....
Then you miss
Set db = CurrentDb
or "qry_EmployeeFilteredFinal " doesn't exist.
/gustav
Set db = CurrentDb
or "qry_EmployeeFilteredFinal
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Re query
Put it back in and got no data, no error either.
I have included a pic of my graph properties, am I missing something here?
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.Re
Put it back in and got no data, no error either.
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
I don't work with graphs in Access, so I have no more than common sense to use here ...
/gustav
ASKER
qry_EmployeeFiltered_vjb
PARAMETERS pItems Long, pShift Long, pEdate DateTime, pSdate DateTime;
SELECT tblWeeks.Week, qry_EmployeeDataWeekly_vjb
FROM tblWeeks LEFT JOIN qry_EmployeeDataWeekly_vjb
WHERE (((tblWeeks.Week) Between [pSdate] And [pEdate]) AND ((qry_EmployeeDataWeekly_v
ORDER BY tblWeeks.Week DESC;
qry_EmployeeDataWeekly_vjb
PARAMETERS pItems Long, pShift Long;
SELECT lastdayofweek([date]) AS [Week Ending Date], qry_EmployeeDataPre.Shift,
FROM qry_EmployeeDataPre INNER JOIN Employees ON qry_EmployeeDataPre.Employ
WHERE (((IIf([pShift]=3,True,([q
GROUP BY lastdayofweek([date]), qry_EmployeeDataPre.Shift,
HAVING (((qry_EmployeeDataPre.Emp
ORDER BY lastdayofweek([date]) DESC;
qry_EmployeeDataPre
SELECT qry_A01ProdDataConverted2T
FROM (Products INNER JOIN qry_A01ProdDataConverted2T
GROUP BY qry_A01ProdDataConverted2T