Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Pulling query into access Graph

    Hi,

    I have a form which pulls data form a query and displays that data into a chart ....

    When i got and hit the cmd button to get the graph.. it shows the graph with the correct data.... sometimes.. sometimes i get a East West North Graph.. and sometimes i get the correct graph...

    Is there a way to fix this.. ??

    thanks

    Heres the code behind the button

    Code:
    Private Sub Command0_Click()
    Dim sqlString As String
    sqlString = ""
    
    If Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) Then
    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "
    End If
    '...
    
    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS InspectionType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) " & sqlString & " GROUP BY Left([INSP],2);"
    
    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query3"
    CurrentDb.CreateQueryDef "query3", sqlString
    Me.Refresh
    Me.Requery
    DoCmd.OpenReport "Graph4", acViewPreview
    
    End Sub
    Last edited by TonyT; 12-02-03 at 17:53.

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    I am assuminng you are using the Micorsoft Graph chart object. Use the RowSource property of the chart and place the SQL statement into this property in the Report Open Event (use the OpenArgs property of Docmd.OpenReport to pass in the SQL you want). This should fix your problem

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    Could you explain with a little more detail of how to do that ? thanks.. I 'm new to access..

    Thanks for the reply

    =)

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by TonyT
    Could you explain with a little more detail of how to do that ? thanks.. I 'm new to access..

    Thanks for the reply

    =)
    The code behind your command button would look similar to:
    Private Sub cmdChartTest_Click()
    Dim strSQL As String
    strSQL = "SELECT * FROM MyTable"
    DoCmd.OpenForm FormName:="MyForm", View:=acNormal, OpenArgs:=strSQL
    End Sub

    On the form you would use code such as:
    Private Sub Form_Load()
    If Len(Me.OpenArgs) Then _
    Me("ocxChart").RowSource = Me.OpenArgs
    End Sub

    Thats all you need to get going. You will need to place the chart on a form and not a report to be able to use the RowSource property

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    Justin,

    If I place the chart on the form .. how will I be able to print out just the graph ?

    thanks

  6. #6
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by TonyT
    Justin,

    If I place the chart on the form .. how will I be able to print out just the graph ?

    thanks
    For printing purposes a form and a report are very similar. A report has extra features for grouping of records. A form will show as a print preview and print - just use the print buttons on the toolbar or menu bar. The chart needs to be sized to the size of the printed page as it will not grow or shrink by itself

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    Going back to the original code.. On my form I set it to instead of displaying it to the graph.. display the query that the graph comes from ... and everytime I display the query.. it pulls the correct infomation...

    I dont know why the graph does not pick up the correct data from the query that I use for my graph..

    I was thinking.. maybe you could refresh or requery then let the report pull the data from the query..

    Does that seem logically ?

    I'll attach a copy of the .mdb if you want to take a look..

    The form is..

    "form1"
    The first graph button data comes from "query3" and outputs to "graph1"
    ......second ........................................."query4". ..................."graph2"
    etc...

    This is what I'm doing to test it..

    First I leave the date fields blank hit graph a few times.. data comes up fine..

    SEcond I put in dates 09/26/03 thru 09/26/03.. hit graph a few times.. it may come up with the correct graph..

    Last, I go back and take the dates off again.. hit graph a few times.. now here I get the graph that show with the dates 09/26/03 a few times and the graph that shows no dates a few times..

    Wierd if you ask me.. I dont know why its doing this..

    Maybe you see something I don't...

    thanks for the help
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by TonyT
    Going back to the original code.. On my form I set it to instead of displaying it to the graph.. display the query that the graph comes from ... and everytime I display the query.. it pulls the correct infomation...

    I dont know why the graph does not pick up the correct data from the query that I use for my graph..

    I was thinking.. maybe you could refresh or requery then let the report pull the data from the query..

    Does that seem logically ?

    I'll attach a copy of the .mdb if you want to take a look..

    The form is..

    "form1"
    The first graph button data comes from "query3" and outputs to "graph1"
    ......second ........................................."query4". ..................."graph2"
    etc...

    This is what I'm doing to test it..

    First I leave the date fields blank hit graph a few times.. data comes up fine..

    SEcond I put in dates 09/26/03 thru 09/26/03.. hit graph a few times.. it may come up with the correct graph..

    Last, I go back and take the dates off again.. hit graph a few times.. now here I get the graph that show with the dates 09/26/03 a few times and the graph that shows no dates a few times..

    Wierd if you ask me.. I dont know why its doing this..

    Maybe you see something I don't...

    thanks for the help
    I had a look at your database. I have made 2 changes. Created a form called frmChart_1 which contains the chart. I have also changed the code behing the first graph button on Form1 to open this new form with the SQL you want. Using the graph object model you could optimise the form frmChart_1 to use it for all you graphs - just change the titles and chart type accordingly
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •