Datasheet doesn't reflect underlying data in Microsoft Graph 2000 2

[Terpsfan]

I have created a number of graphs in Access reports. However I can never seem to get the datasheet to reflect the underlying data. Can someone give me a heads up on how I would correct this? Thanks in advance...

 

[dhookom]

Check out ACG's tips at

http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Terpsfan]

Thanks...that link is a very good resource.

 

[Terpsfan]

Actually none of those fine code examples on that site worked for my charts...however I figured out the answer. The datasheet doesn't bind if you are basing the chart on an unbound recordsource. If you use a dynamic query that isn't saved, you get the generic datasheet. If you use a stored query then the datasheet binds and shows actual data since it was last refreshed.

 

[stable]

Hi there,

Omega36 can you explain what you meant by a stored query?

I've got a report that has a table record source. For each record in the table I wish to display a chart. In the detail section of this report I have set up a text box showing a description field from the current record and a chart that utilises the refreshing data mentioned on the site

http://ourworld.compuserve.com/homepages/attac-cg/acgsoft.htm.

The description is correct but the chart looks the same for all 16 records. Here is the code in my On Print event:

Dim dbs As Database
Dim qdf As QueryDef
Dim strSQL As String

    Set dbs = CurrentDb

    Debug.Print "me.ServerName="; Me.ServerName

    strSQL = "SELECT ServerNames.ServerName, Drive_Space.DriveLetter, Drive_Space.Date, Drive_Space.FreeSpace, Drive_Space.DriveCapacity, Drive_Space.PercentFreeSpace " & _
            "FROM ServerNames INNER JOIN Drive_Space ON ServerNames.ServerID = Drive_Space.ServerID " & _
            "WHERE (((ServerNames.ServerName) = '" & Me.ServerName & "')) " & _
            "ORDER BY ServerNames.ServerName, Drive_Space.DriveLetter, Drive_Space.Date; "

    Set qdf = dbs.QueryDefs("qsGenericServerChart")
    qdf.SQL = strSQL
    
Debug.Print strSQL

    On Error Resume Next
    Dim objGraph As Object

    Set objGraph = Me.Chart1.Object
    
    objGraph.RowSource = "TRANSFORM Max(qsGenericServerChart.FreeSpace) AS MaxOfFreeSpace SELECT (Format([Date],'Short Date')) AS Expr1 FROM qsGenericServerChart GROUP BY (Int([Date])), (Format([Date],'Short Date')) PIVOT qsGenericServerChart.DriveLetter;"
    'This will update the data sheet
    objGraph.Application.Update


    objGraph.Refresh
    DoEvents
    Set objGraph = Nothing

The debug print shows that I am stepping through all records in the table. While I page through the preview the report I look at the data view of the generic query "qsGenericServerChart" and it reflects the data for the current report. Therefore I can only assume that the chart is not updated using the data from the current generic query.

Any help would be greatly appeciated.

Cheers
Stable

 

[dhookom]

Stable,
I don't believe you can change the RowSource property of subreports and chart controls after the report has begun formatting or printing. You are masking any error message by "On Error Resume Next".

First try remove line of code that sets the RowSource.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[stable]

Hi Duane,

In actual fact I only inserted the rowsource line to see if it would make any difference - it did not. The rowsource does not change for each record in the Table.

Its the filtered data in "qsGenericServerChart" that changes with each new value of ServerName.

I will remove the on Error resume next statement when I go back to work on Monday.

cheers
Stable

 

[dhookom]

The other thing I would try is to leave the server name in the crosstab and then use the Link Master/Child properties of the chart to filter the chart data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[stable]

Hi Duane,

I've removed the line of code that sets the rowsource - no difference.

I can't use the Link Master/child properties - the message I get is "Can't build a link between unbound forms".

I've put the chart into a subreport linked by ServerName field and kept the server name in the crosstab - still no difference.

I've also tried removing the servername from the crosstab - then I get data for all servers in the chart!

Do you have any other ideas?

Cheers
Stable

 

[dhookom]

The method that has worked for me is setting the Link Master/Child. I don't care what the error message states when using the "builder wizard". If your report is bound to a record source with the serverName and the same field is in the graph Row Source then you can just type the field names into the properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]