• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

setting the source data of a chart

I have a function in a form that creats an sql based on the option btn selected.  each option builds a unique querydef.

Now I have a report with 10 charts on it, each pointing to  the querydef object.  I want to set the recordsource/control source/rowsource of all the charts to nothing. Then in a select case, the recordsource or control source or rowsource is set to the querydef or sql created in the form, and visible is T.  How do I get this to work??  I don't want to create a report for every chart.  I there was a way I could disable all the charts exept the one I want. because all the chgarts are based on the same querydef object, the recordsource of the report is set to that querydef and the rowsource of each chart is different based on the querydef created.  Right now each chart is causing errors looking for fields that don't exist in the rowsource exept for the one that matches.
The lines with ERROR are the ones causing trouble.
(form with the optionbtns)
  Select Case graphoption
        Case 1  'daily sheets
            graphSQL = "SELECT ....
            graphtitle = "Daily Sheet Percentage of 3/8"
end select
Set graphQD = .CreateQueryDef("Green End graph", graphSQL)
DoCmd.OpenReport "Green End Graph", acViewPreview

(report with charts)
For i = 1 To 4
    Me("greenend" & i).Visible = False
    Me("greenend" & i).ControlSource = ""   ERROR
Select Case graphtitle
    Case "Daily sheet percentage of 3/8"
        Me("greenend" & 1).ControlSource = green end graph   ERROR
        Me("greenend" & 1).Visible = True
End Select
  • 2
  • 2
1 Solution
SE081398Author Commented:
Edited text of question.
In a report you cannot change the control source for a graph, if this was a form you could but not in a report.

Take a look at:
There are a workaround suggested by microsoft.

I have another workaround (inveted by me):

Instead of changing the rowsource of the graph, I change the query, this method will also work on MDE (microsoft's don't!)

I have this function:
Function ChangeQDef(FromQ As String)
'Copy a query to the query StandardReportQ

DoCmd.SetWarnings False
    DoCmd.CopyObject , "StandardReportQ", A_QUERY, FraQ
DoCmd.SetWarnings True
End Function

Then all I have to do is call this function, before the graph is displayed, you will of course have to have the queries created before you can copy. Then create the graph with the query StandardReportQ as control source.


SE081398Author Commented:
Thanks I never thought to create it in a form and display it then copy it to a report and print it.  Not the most efficient process but it works.
..if you can't beat them, join them...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now