Graph row source

[ChrisProg]

Using Access 2000 I have a report containing a graph. This object has a pivot transform query to provide up to 10 series (lines) on the graph. A form is used to select values to be used in the graph.

I need to be able to change the rowsource contents of the graph depending upon the values selected on a parent form.

Can any one help with the vb code needed on the form (control button) which will allow me to specify the graph rowsource sql. I know what syntax I need for the sql but cannot find a method to update this into the graph rowsource.

I have tried to open the report in dev mode to update the row source this way but am unable to make this work (when I try to run the code it causes Access to close with a log file the name of which is not given).

Any help much appreciated.

 

[dhookom]

I would set the Row Source property of the graph control to a saved query. You can then change the sql of the saved query using a little DAO code:

CurrentDb.QueryDefs("qxtGraphRowSource").SQL = "TRANSFORM..."

Run this code prior to opening the report that contains the graph.

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

 

[ChrisProg]

Thank you dhookkrom. I have inserted code to update a query used as the basis for the graph data. The query is a pivot query which takes about 20 seconds to execute.

One small problem is that the code used to modify the query is part of a for....next loop as I have about 50 graphs to create from the same database data. During the for...next loop, I modify the criteria of the query for each execution within the loop (to produce a single graph). On the second and subsequent passes in the loop I get a "cannot append or insert" error message when trying to update the query as the loop code is executing synchronously with the report, and the query is in the process of being run.

How might I create a 'wait' loop in my code in which I check for the report /graph execution to have been completed before updating the query for the next graph?

 

[dhookom]

Are the graphs all in one report or many different reports?

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

 

[ChrisProg]

Hi Duane,

I have a single report which is called from a form. The form allows the user to select the parameters and options to provide graph headings and filter (where statements) parameters for inclusion in the graph query.

I have used the 'syscdm' function to check when a query or table is in use before trying to update the query code. This works most of the time but still some of the graphs are produced with the wrong query.
What is the best practice for dealing with this timing issue? Is there a method of ensuring that the amended query is stored in the database before attempting to use it (other than trying to execute it!).

Thanks for any help.

 

[dhookom]

It is not real clear whether:
-this is a single user front end
-are all fifty graphs in the same report
-what version of Access

Sometimes creating temporary tables for row sources of graphs is much quicker. For timing issues, check this page

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

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

 

[ChrisProg]

Hello Duane,

The system in use is a MS 2000 Prof within a network with Access 2000 (9.0.2720) installed on the PC only running as a single user facility.

There is one database in which a report has been created. The report is called by a form to produce a single page on which is a single graph with supporting table displayed.

The form has multiple combo boxes and check boxes which allow the user to select the data to be shown in the report graph. The code behind the form's print button evaluates the user selection and updates the record source query of the report before the report is executed. The code has a loop which enables the report to be called as many times as the user selected parameters demand.

The loop code is as follows

For j = 1 To imax(1)
For k = 1 To imax(2)
For m = 1 To imax(4)
ReportClear = False
Me.TimerInterval = 1000
TimerElapsed = False
Call CheckReportNotRunning("MartinGraph1")
Call SetUserOptions
Call UpdateGraphQuery(stOption)
' Update the graph heading table
QueryClear = False
DoCmd.RunSQL UpdateGraphHeadings

' Check table not busy and that insert query has finished
Call CheckQueryNotRunning("MartinGHead")
Call CheckQueryNotRunning("MartinGraph1")
' Check record source count > 0 then print
If Mod_RecordCount > 0 Then
stDocName = "MartinGraph1"
' Set up the printer
res = RepPrint(stDocName, True, dmPaperSize.dmpaper_a4, 1, True)
DoCmd.OpenReport stDocName, acViewNormal
End If
Next m
Next k
Next j


The record source query is a pivot type to create two or more series to be included in the report graph.

The code to check that a report has finished before calling the next report execution is as follows.

Private Sub CheckReportNotRunning(RepName As String)
'
' Loop here if report still running
'
Do While ReportClear = False
If adhIsOpen(RepName, acReport) = True Then
Me.TimerInterval = 1000
TimerElapsed = False
Do While TimerElapsed = False
DoEvents
Loop
Else
ReportClear = True
End If
DoEvents
Loop
End Sub


I have run the reports several times and the results obtained show a random variation of cases where the wrong record source query code has been used. (3 graphs are wrong in each case, but not the same ones!)

I will check the site you have specified to see if this will help me, but I would still like to identify the cause of the problem.

Thanks for any help.