[AccessD] Changing Rowsource for Graph (Sproc source)

Just for the record, I couldn't find a solution using parameters in 
the report that worked so I ended up creating a temporary table, and 
running an insert sproc from VBA to fill it with the correct records 
before opening the report.  The report was then based on the temporary table.

David

At 4/11/2006, you wrote:
 >A long shot ideas.
 >
 >Use CHR$(39) instead of the single quote.
 >Not sure the syntax is exactly correct or not but here is what I came up
 >with.
 >
 >"EXEC sprptPipeGateForecastMth " & [Forms]![frmPipeWeldForecast]!cboGateIDNo
 >& ", " & Chr$(39)
 >& basConvertDate(basFirstMthDay([Forms]![frmPipeWeldForecast]![t
 >xtGateMthStartMth])) & Chr$(39) & ", " & Chr$(39) &
 >"basConvertDate(basFirstMthDay([Forms]![frmPipeWeldForecast]![txtGateMthEndM
 >th])) " & Chr$(39)
 >
 >
 >-----Original Message-----
 >From: accessd-bounces at databaseadvisors.com
 >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
 >Sent: Friday, November 03, 2006 3:10 AM
 >To: 'Access Developers discussion and problem solving'
 >Subject: Re: [AccessD] Changing Rowsource for Graph (Sproc source)
 >
 >Hi David
 >I seem to recall having the same issue a long while ago. I was using queries
 >and ended up setting the graph's rowsource to a query but modifying the
 >query's SQL before opening the report. Not being an SQL kind of guy I don't
 >know how that translates when you have Sprocs, or if it can at all, but I'm
 >hoping that gives you a steer.
 >
 >-- Andy Lacey
 >http://www.minstersystems.co.uk
 >
 > > -----Original Message-----
 > > From: accessd-bounces at databaseadvisors.com
 > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
 > > David Emerson
 > > Sent: 03 November 2006 04:28
 > > To: accessd at databaseadvisors.com
 > > Subject: [AccessD] Changing Rowsource for Graph (Sproc source)
 > >
 > >
 > > Group,
 > >
 > > Access XP ADP
 > >
 > > I have a report that has a single graph on it (Unbound Object Frame:
 > > Microsoft Graph Chart).  The RowSource property is set to a sproc
 > > which has parameters.
 > >
 > > If I enter the following direct into the RowSource property the
 > > report runs fine:
 > > EXEC sprptPipeGateForecastMth 1085207198, '2006-11-01', '2007-10-01'
 > >
 > > However, I want to set the parameters based on values on a form.  The
 > > help I found on the internet said that the reports OnOpen event was
 > > the place to put it so I added:
 > >
 > > Me!graGraph.RowSource = "EXEC sprptPipeGateForecastMth " &
 > > [Forms]![frmPipeWeldForecast]!cboGateIDNo & ", '" &
 > > basConvertDate(basFirstMthDay([Forms]![frmPipeWeldForecast]![t
 > > xtGateMthStartMth]))
 > > & "', '" &
 > > basConvertDate(basFirstMthDay([Forms]![frmPipeWeldForecast]![t
 > > xtGateMthEndMth]))
 > > & "'"
 > >
 > > When the report is run I get Runtime Error 2455 - You entered an
 > > expression that has an invalid reference to the property
 > > RowSource.  I get the same error if I try in the OnOpen event of the
 > > report (this indicates that the property is not even
 > > available OnOpen):
 > >
 > > MsgBox Reports!rptPipeGateForecastMth!graGraph.RowSource
 > >
 > > I know the property exists because the following works it I put it
 > > into the report detail OnFormat event:
 > > MsgBox Me!graGraph.RowSource
 > >
 > > I have tried setting the property on the detail OnFormat event but an
 > > error comes up saying that you cant set this property after printing
 > > has started.
 > >
 > > Any guidance on what I can try?

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com