Error setting Rowsource using VBA for Chart

[Laurie Loves Automation]

I have a report that contains an unbound Microsoft Graph Chart. I want the
chart's row source to be set based on a query plus some user input from a
previous screen. I've tried putting the code in the Activate() Event, Open()
Event, and Page() event and I get errors on all of them. The Activate and
Open events don't even seem to see my report and I'm thinking it's because
it's not technically "open" at the time they run...? The Page event gives me
a different error that I can't set the Rowsource property after "printing"
has started (I'm not printing... does this mean something different?).
Anyhow, I really need to get this to work. Any suggestions?

Here is my code:

Reports!rptRebuildByCategory!OLERebuildByCategory.RowSource = "SELECT
qryProgramLoadRebuildInfoFull.Category,
Count(qryProgramLoadRebuildInfoFull.Category) AS CountOfCategory,
qryProgramLoadRebuildInfoFull.Rebuild,
qryProgramLoadRebuildInfoFull.SetupCheckInDate FROM
qryProgramLoadRebuildInfoFull GROUP BY
qryProgramLoadRebuildInfoFull.Category,
qryProgramLoadRebuildInfoFull.Rebuild,
qryProgramLoadRebuildInfoFull.SetupCheckInDate HAVING
(((qryProgramLoadRebuildInfoFull.Category)<>"" And
(qryProgramLoadRebuildInfoFull.Category)<>'N/A') AND
((qryProgramLoadRebuildInfoFull.Rebuild)=True) AND
((qryProgramLoadRebuildInfoFull.SetupCheckInDate) Like '1/*2007*'))"

Thanks,

Laurie

 

[Duane Hookom]

I try to avoid messing with Row Sources of charts any time after a report has
been opened. You might want to base the chart on a saved query. Then use some
DAO code to change the SQL property of the query prior to opening the report.

strSQL ="SELECT Category, Count(Category) AS CountOfCategory, " & _
"Rebuild, SetupCheckInDate " & _
"FROM qryProgramLoadRebuildInfoFull " & _
"WHERE Category & ''<>'' And Category<>'N/A' AND " & _
"Rebuild=True AND SetupCheckInDate Like '1/*2007*' " & _
"GROUP BY Category, Rebuild, SetupCheckInDate "
CurrentDb.QueryDefs("qselForChart").SQL = strSQL

If SetupCheckInDate is really a date field, I would not use a string
comparison.

 

[Laurie Loves Automation]

Thank-you so much! This works beautifully and now the knots on my head can
heal from me banging my head on the wall.

Have a great day!

Laurie

 

[Lydia]

It works for me too. Thanks.

Lydia

 

[Ting]

Excellent Duane Hookom, solve my problem too