Try Refresh, Requery, Other? Thanks! 1

[newtech2]

I have a form, frmChartReports, with 2 unbound controls: StartDate and EndDate. frmChartReports also has 5 command buttons that run its own report which is a chart. Each of the chart reports are based on parameter queries, which use the unbound StartDate and EndDate on frmChartReports. When dates are entered in frmChartReports and each of the buttons is selected, each graph report previews as planned. Sometimes, when the EndDate is changed, and a button is then clicked, the parameter is the same date parameter that was entered before. Sometimes, when the EndDate is changed, the chart parameter is fine. Any suggestions?

 

[RickSpr]

Might it be that the selected report was already open, from the time it was opened with the previous EndDate? The recordset is built when the report is opened, not necessarily when OpenReport is called. If a report is already opened when OpenReport is called, the report is not opened again, it is only brought to the top. The recordset would not be rebuilt, so the updated parameters would not apply.

If it were a form, Requery would rebuild the recordset. Reports don't have a Requery method, unfortunately.

One solution would be to make sure the report is closed when you call OpenReport. That will cause a screen flicker, though. Another solution might be to assign the report's RecordSource property to itself. Assigning to the RecordSource property should cause the report to rebuild its recordset, even if there's no actual change in the property's value. Rick Sprague

 

[newtech2]

I think you are on about the report not closing. After playing with it more, I noticed when the chart report comes up in print preview, and I click close, I moment later I can hear my computer “cooking” for a second. FrmChartReport was, of course, still open when I clicked close on print preview and if I change the date in StartDate or EndDate, and run the same chart report before I hear this “cooking” from the previous close, it keeps the parameter from the previous run.

How would I go about assuring the report is completely closed before allowing another report to run? The button for closing is clicked, but there seems to be a delay. I am not worried about the screen flicker as I am assuming the user will not be doing this very often.

If your other solution would be best, how do I go about assigning the report’s recordsource to itself? The record source to the graph is, of course, the query which is based on other queries (that need the parameter for frmChartReport). The recordsource for the report itself is blank because only the chart is placed on it.

Thanks so much for your input as I am new to development!

 

[RickSpr]

Well, now you've taught me something. Never having needed a chart before, I didn't realize it wouldn't be using the report's record source. I guess you must be using an ActiveX control, right?

Assuming the "cooking" is some sort of disk activity that occurs after you close the report, it's happening aynchronously, and I think it would be quite hard to detect when the report was truly finished closing. I can think of one way to test for it, but it might not be reliable. Did you realize that open forms are members of the application's Reports collection? It may be that it remains in the Reports collection until the closure is completed, so you could tell that way. Still, I'm not sure how you can stop the user from attempting to open the report again before it's fully closed. (For that matter, I'm not entirely sure that the cooking sound is related to the report closing, either.)

Assigning the report's RecordSource property to itself won't work, probably, since the chart doesn't use it. But perhaps you can get the ActiveX chart control to update itself. Does it have an Update method or equivalent, perhaps? Or can you change one of its other properties to make it rerun its data source query? Since I'm not familiar with this object, I'm afraid I can't help you more than that.

Here's the syntax for calling a method of the ActiveX control from your switchboard:
Reports!<report name>!<ActiveX control name>.<method name>
You can also refer to a property of the control the same way, substituting a property name for the method name. Rick Sprague

 

[newtech2]

I received some help from Microsoft on this. This is what they had to say. . .seemed to work like a charm!. . .

Based on my research, the graph does not match the data of the query result, but it is referencing data from the original result in the report. This behavior seems to be on Windows 2000 and Windows XP. This issue is caused by a timing issue between Microsoft Access and Microsoft Graph. The data is transferred from Microsoft Access to Microsoft Graph which is responsible for generating the appropriate graph. If Microsoft Graph does not generate and return the graph object within some time interval while the report is being formatted, it might become disassociated from its Recordset. This will cause the object to be displayed with an unrelated record.

To work around this problem, we may need to implement a DoEvents loop:

1. Create a new module.

2. Copy and Paste or type the following function into the new module.

Function TestProc()
Dim i As Integer
For i = 1 To 10
DoEvents
Next i
End Function

Because this is related to timing, the number of iterations through the loop may need to be increased.

3. Save and close the module.

4. Open the report in Design View.

5. Display the Properties window for the details section that contains the graph object and select the Events tab.

6. For the On Format event, call the TestProc procedure completed earlier.