Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


setting the source data of a chart

Posted on 1999-06-22
Medium Priority
Last Modified: 2010-05-18
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
Question by:SE081398
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Author Comment

ID: 1997052
Edited text of question.

Accepted Solution

perove earned 210 total points
ID: 1997053
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.



Author Comment

ID: 1997054
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.

Expert Comment

ID: 1997055
..if you can't beat them, join them...


Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question