Graph RowSource, Can't Set in Code in Access 2000

[rwel]

Product: Access 2000

I need to set the RowSource property in code and have seen in MS Knowledgebase Article 209993 sample code for doing so:

"Reports!ReportName!ChartName.RowSource = " SELECT Blah Blah etc ;"

When I run this code I get "Run-time error '2455', You entered an expression that has an invalid reference to the property RowSource" .

What should be a simple and routine assignmentma just does not work.

Help Please.


rwel

 

[dhookom]

What is your full code or macro?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Crowley16]

I have had the exact same problem, thread703-981840, with the addition that if I go into debug mode, and continue the code, then it does run...

my code is just:
gphNewPatients.RowSource = sqlStr
and sqlStr contains a valid sql string.

--------------------
Procrastinate Now!

 

[Crowley16]

Apparently the problem occurs because the chart object hasn't been initialised in the onOpen event of the report.

However you can't change the rowsource after you've started the print view, so the statement has to go somewhere in between...

I'll keep you guys posted...

--------------------
Procrastinate Now!

 

[Crowley16]

Ok, found a solution... well, sort of...

to get around the problem of not being able to set rowsource onPrintView, in the code for the button that calls the report:

open the report in design view
reset the rowsource of the chart in the report
save the report
open the report normally

not very nice at all :-(

--------------------
Procrastinate Now!

 

[rwel]

From rwel, 2/3/2005, 0848 EST

I have coded the following "work around".

The inability to set the rowsource for a graph stems from the fact that MSAccess 2000 (and likly other versions ?) only supports setting the rowsource in "acDesign" mode.

The workaround is to:
1. silently (echo off) put the report into acDesign mode
2. make the rowsource assignment(s)
3. save the report
4. turn echo back on
5. continue all other processing which eventually leads to
opening the report in acPrintPriview

A very ugly solution with the following limitations:

1. cannont be used in an .mde database since the report cannot be open in acDesign mode in an .mde databas

2. Must give users design privileges sionce they are , via code, opening the report in acDesign mode.

The key to this understanding was contained in MSKnowledgebase article 209993 but was subtly understated
so that it required very careful reading to get the salient point, namely that the rowsource can be set unless you are in acDesign mode.

Hope MS fixes this limitation in future releases of ACCESS.

rwel



BELOW is rwel's code which sets rowsource for 8 graphs:

'------------------------------------------------------------
' Mac209993
'
'------------------------------------------------------------
Function Mac209993()



On Error GoTo Mac209993_Err

DoCmd.Echo False, ""
DoCmd.OpenReport "rptLaraEnergies", acDesign, "", ""

Reports!rptLaraEnergies!GphRG1.RowSource = "SELECT SR_LOG_NUM, [RG1ENERGY]*1000000 AS Scaled_RG1ENERGY FROM tblMasterDriver" & CurrentUser() & "; "

Reports!rptLaraEnergies!GphRG2.RowSource = "SELECT SR_LOG_NUM, IIf(PSL_PCM_ID=""812"",""41"",IIf(PSL_PCM_ID=""811"",""61"",IIf(PSL_PCM_ID=""813"",""84"",""""))) AS LSL, " & _
" IIf(PSL_PCM_ID=""812"",""43"",IIf(PSL_PCM_ID=""811"",""63"",IIf(PSL_PCM_ID=""813"",""86"",""""))) AS USL, [RG2ENERGY]*1000000 AS RE2, [UCL]*1000000 AS UpCL, [LCL]*1000000 AS LowCL, [Mean]*1000000 AS Average " & _
" FROM tblRE2StatsUCLLCLFullUnion" & CurrentUser() & "; "

Reports!rptLaraEnergies!GphLARA.RowSource = " SELECT SR_LOG_NUM, ENERGY AS ['LARA']FROM tblMasterDriver" & CurrentUser() & "; "

Reports!rptLaraEnergies!GphDriver.RowSource = " SELECT SR_LOG_NUM, DOENERGY, Mean, UCL, LCL FROM tblGraph64StatsFullUnion" & CurrentUser() & "; "

Reports!rptLaraEnergies!GphLARAGain.RowSource = " SELECT SR_LOG_NUM, [ENERGY]/[RG2ENERGY] AS ['LARA Gain'] FROM tblMasterDriver" & CurrentUser() & "; "


Reports!rptLaraEnergies!GphDriverGain.RowSource = " SELECT SR_LOG_NUM, [DOENERGY]/[ENERGY] AS ['Driver Gain']FROM tblMasterDriver" & CurrentUser() & "; "

Reports!rptLaraEnergies!GphFilmEllip.RowSource = " SELECT RG1SR_DATE, FilmEllipticity, Limit FROM tblOMEGA_ELLIPTICITY_Film" & CurrentUser() & ";"


Reports!rptLaraEnergies!GphCCDEllip.RowSource = " SELECT RG1SR_DATE, CCD_Ellipticity, Limit FROM tblOMEGA_OMA_ELLIP_CCD" & CurrentUser() & ";"



DoCmd.RunCommand acCmdSave
DoCmd.Echo True, ""


Mac209993_Exit:
Exit Function

Mac209993_Err:
MsgBox Error$
Resume Mac209993_Exit

End Function

 

[Crowley16]

I've also read that it's possible to set up events for embeded objects, which involves creating a custom class module that has events, and then creating a new object of that class which contains the chart...

I've not managed to get that to work and haven't really got time to work on it at the moment, but this way should get past the limitations rwel mentioned.

I'll try to get back to this if I get time, which is unlikely...

--------------------
Procrastinate Now!

 

[dhookom]

rwel's problem stems from an application that has data (user's names) imbedded in object names. I expect that the issue would go away if all of the user's records were kept in the same table/union query.

Another solution is to use code to change the SQL property of saved queries prior to opening the report. This can be done in an MDE. For instance, you could have code like:

Currentdb.QueryDefs("qselLaraGain").SQL = _
 "SELECT SR_LOG_NUM, [ENERGY]/[RG2ENERGY] AS ['LARA Gain'] FROM tblMasterDriver" & CurrentUser() & "; "

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]