RE: Error setting Rowsource using VBA for Chart

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.
--
Duane Hookom
Microsoft Access MVP


"Laurie Loves Automation" wrote:

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

.

Relevant Pages

  • Re: Jet Database does not recognize Forms........................
    ... What are the SQL views of the Row Sources of the charts? ... > Calls the Result of the Lookup Table as a label for the Report. ... > Creates 2 Charts using results of Query 3. ... >> Duane Hookom ...
    (microsoft.public.access.reports)
  • charts based on parameter queries
    ... I have a report that displays several charts of data over time (data on y ... axis, date on x axis) on each page, one page per site. ... query of all data on all dates at all sites. ...
    (microsoft.public.access.reports)
  • Re: Repeated Parameter Requests
    ... The only way I know is to use a form to pass the parameter to the query. ... Don't assign a record source to the form and don't assign a control source to ... Make the button open the report with the charts. ...
    (microsoft.public.access.reports)
  • Subreport Charts displaying no data
    ... The main file the report is based on is a master file, 1 record/row per production line and shift in our plant. ... The option is run from a form, where a beginning and ending date is keyed, to be used for a filter on the charts described below, and for headings on the report. ... Macro runs a make-table query to select the subset of data and summarize Downtime minutes by Line/Shift/ReasonCode. ...
    (microsoft.public.access.reports)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
Loading