MsChart and recordset

I have a populated recordset and want to 'display' the recordset in the form of a chart, however nothing appears on the chart when I set the datasource property. This is the code I am using:


Dim con As ADODB.Connection, _
rst As ADODB.Recordset, _
strsql As String

Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

strsql = "SELECT m.material, m.name, " & _
"SUM(fupo.quantity) as amount, " & _
"cast(((SUM(fupo.quantity) / ((SELECT sum(weight) " & _
"From IJ_WDSTK_PROD_DATA_MART.dbo.prseregs " & _
"WHERE prperiodo = 197775)) - (SELECT IsNull(sum(weight),0) " & _
"From outsorted_fish " & _
"WHERE prperiod = 197775)) * 100) as decimal(10,2)) as actualpercentage, " & _
"fupd.distributionpercentage as standardpercentage " & _
"FROM process_period_standard_set ppss, " & _
"fish_util_plan_header fuph, " & _
"fish_util_plan_output fupo, " & _
"fish_util_plan_detail fupd, " & _
"materials m " & _
"Where ppss.prperiod = 197775 " & _
"AND ppss.standardset = fuph.id " & _
"AND fupo.fishutilplanheader = fuph.id " & _
"AND fupd.fishutilplanheader = fuph.id " & _
"AND fupo.productid not in(101, 102, 103, 104) " & _
"AND m.material = fupo.productid " & _
"AND m.material = fupd.productid " & _
"AND fupo.quantity > 0 " & _
"GROUP BY m.material, m.name, m.category, " & _
"fupd.distributionpercentage"

con.ConnectionString = ConnectionString

con.Open

rst.Open strsql, con

If Not rst.EOF Then

With chtefficiency

Set .DataSource = rst
.ShowLegend = True
.chartType = VtChChartType2dLine

End With

End If

Set con = Nothing
Set rst = Nothing


Any help with making this chart appear will be appreciated. I am sure this is just a simple property that has to be set (which one though?)
Thanks

I have found the reason why nothing appears on the chart. Right at the very end of my code I set the recordset and connection object to Nothing. Like this:


Set con = Nothing
Set rst = Nothing


This is why nothing appears on the chart, because the recordset feeding the datasource property is set to Nothing just after the query is run. :o
Cheers